Inner Join php to search in two tables

Asked

Viewed 308 times

-2

I need to search with PHP for the data typed in two Mysql tables, produtos and usuario.

I’m doing like this:

$array = array(); 

$query = mysqli_query($conn,"SELECT * FROM produtos inner JOIN usuario ON produtos.nome = usuario.nome
    WHERE produtos.nome LIKE '%{$query}%'");

     while($row=mysqli_fetch_assoc($query)) {
        $array[] = $row['nome'];
     }
     echo json_encode($array);

But nothing returns in the search, how could it solve this?

Bank structure:

produtos
id | nome

usuario
id_usuario | nome

I tried with the UNION as I was instructed here but also did not give:

$query = mysqli_query($conn,"SELECT nome FROM produtos WHERE nome LIKE '%{$query}%' limit 5 UNION Select usuario FROM nome WHERE nome LIKE '%{$query}%' limit 5");

 while($row=mysqli_fetch_assoc($query)) {
    $array[] = $row['nome'];
 }
 echo json_encode($array);
php inner-join

  • What is the foreign key that makes the relation between the tables?

  • foreign key in Mysql type Innodb

  • Do you need to "search in two tables" or do you need to "search according to the relationship between them"? The two tables don’t seem to be related, but you still did a JOIN. JOIN serves to address the relationship between tables, not to unify and search.

  • Where does the value of $query in LIKE '%{$query}%' to define the variable $query? This is wrong, it’s not!?

  • would have to be with UNION? how would it look?

  • I tried with UNION but it didn’t work either

  • 1

    But what is the result you expect? All products whose name is exactly the same as the user’s name? If so then use INNER JOIN, if not then explain what you want.

  • You have to have a field in the users/customers table that relates them to the product/merchandise table, for example a field usuário.compra whose value is a produtos.id

Show 3 more comments

1 answer

0


products.name = user name.name

Your John is picking up records whose user name is equal to the product name.

In the join, use the fields that are related, which I don’t know what they are because I don’t know how is the structure of these tables.

  • I put the structure in the bank in question

  • I have to get the records typed in the form I used the WHERE products.LIKE name '%{$query}%' would not be that?

  • You have a value and need to check if it exists in the product table and also see if it exists in the user table?

  • I used UNION but I couldn’t

  • If that’s the case, you need a union not of a join. In the Union you posted is Select usuario FROM nome....the field and table is reversed

Browser other questions tagged php inner-join

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