It is very common to find a search on sites that returns records from different tables.
I need to implement a search on my site that returns produtos and usuários (profile) registered on the site. The result should be mix, but the link to them will be different, eg site.com/perfil/id and site.com/produto/id .
Will I need two querys ? Or just with a query with this result?
I can not use JOIN since my tables are unrelated.
I hope I have been clear.
You can use
UNIONto combine the results of your queryUNION is used to combine the result of multiple SELECTs into a single result set.
( select nome, concat( "U" ) as 'tipo' from user where nome = ? ) union all
( select nome, concat( "P" ) as 'tipo' from product where nome = ? )
The above query assembles will fetch in the tables user and product , all records that contain the term searched in the name.
Your tables may have a different structure: user.idade , user.sobrenome , product.preco ... but the UNION statement needs the same amount of fields.
If your need is to display the names as a result of a search, I believe UNION is a viable alternative.
In the case I used concat( "X" ) as 'tipo' , where type represents the table type. When you do the listing, just make the comparison to insert the link accordingly.
if( $row['tipo'] = 'U' )
{
// link para usuário
}
else
{
// link para produto
}