Search in every site

Asked

Viewed 94 times

4

It is very common to find on websites a search that returns records from different tables.

I need to implement on my site a search that returns produtos and usuários(profile) registered on the site. The result should be to mix, however the link to the same will be different, ex: site.com/perfil/id and site.com/produto/id.

I’m gonna need two querys? Or just with a consultation with you that result?

I won’t be able to use JOIN since my charts are unrelated. I hope I’ve been clear.

php mysql query quest

  • I would say that it was better to do separate searches and show in the table of results (if it is a table) the results with the proper link.

  • Jorge, there is no table results, the same is the return (write on screen) of the query of the two tables.

  • I was talking about a table but can be anything else.

  • That way the result won’t come mixed up, right? I will first have the list of all products found and then all users found?

  • Yeah, that’s pretty much it.

  • Your tables are the same, with the same fields? produto.id, produto.nome, usuario.id, usuario.nome...? If so, you can use UNION ALL

  • @Papacharlie but this won’t screw up? Then how does he know which search links to which table?

  • @Jorgeb. I don’t think so, I would: query-1 concat("user") as 'tipo', query-2 concat("product") as 'tipo', so we have the table type reference.

  • @Papacharlie makes an answer.

  • 1

    @Jorgeb., but it depends on the structure of his table. But I will elaborate a simple answer here.

Show 5 more comments

1 answer

5


You can use UNION to combine the results of your query.

UNION is used to combine the result of multiple SELECT’s 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 mounts in the tables user and product, all records containing the term searched in the name.

Your tables may have a different structure: user.idade, user.sobrenome, product.preco... but the instruction UNION need the same amount of fields.

If your need is to display the names as a result of a search, I believe that UNION is a viable alternative.

In case I used concat( "X" ) as 'tipo', where guy represents the type of table. When you do the listing, just do the comparison to insert the link accordingly.

if( $row['tipo'] = 'U' )
{
   // link para usuário
}
else
{
   // link para produto
}
  • 1

    Perfect @Papa Charlie, met my need, I will study more about UNION.

  • @Dagobe if possible, place the structure of your tables.

  • 1

    the SELECT worked, you want me to post the script SQL? And I’m new here and I couldn’t post the amount of characters that have the structure. How do I post?

  • @Dagobe, it doesn’t have to be everything, just the table fields... But if it worked accordingly, then I guess there’s no need for me.

  • 1

    Thank you @Papa, I marked your reply as the one that met me.

Browser other questions tagged php mysql query quest

You are not signed in. Login or sign up in order to post.