How to make foreign table associate fields using select?

Question

In my project I have a function in which searches all the elements of the database and still makes "categoria_id" become "categoria_nome" ordering the field in another table, but when I try to use this select again I get this error:

Warning: mysqli_fetch_assoc() expects Parameter 1 to be mysqli_result, Boolean Given in C: xampp htdocs Warehouse Assets php databases.php on line 16

Below are the functions:

listProducts:

function listaProdutos($conexao) {
    $produtos = array();
    $resultado = mysqli_query($conexao, "select p.*, c.nome as categoria_nome 
    from produtos as p join categorias as c on p.categoria_id = c.id");

    while($produto = mysqli_fetch_assoc($resultado)) {
        array_push($produtos, $produto);
    }

    return $produtos;
}

searchProducts

function buscaProduto($conexao, $id) {
    $query = "select p.*, c.nome as categoria_nome from produtos as p where id ={$id} join categorias as c on p.categoria_id = c.id";
    $resultado = mysqli_query($conexao, $query);

    return mysqli_fetch_assoc($resultado);
 }

How can I make the assimilation between the fields occur ?

Structure of the database:

 *Tabela Produtos:* 

 id

 nome

 descricao

 quantidade

 categoria_id

 localizacao

 ponto_minimo

 ponto_maximo


 *Tabela Categorias:*

  id

  nome

Answer 1

It seems that your query is wrong, the where has to be after your Join and you have two columns with the name "ID", so you have to pass the alias.

Change to:

select 
   p.*, 
   c.nome as categoria_nome 
from produtos as p
join categorias as c on p.categoria_id = c.id
where p.id = {$id}

If you want to see if an error is occurring during the execution of the query, change the line:

$resultado = mysqli_query($conexao, $query);

for:

$resultado = mysqli_query($conexao, $query) or die(mysqli_error($conexao));