How to do foreign table associate fields using select? [duplicate]

Question

How to do foreign table associate fields using select? [duplicate]

Navigation

#1 by (4 votes)

0

In my project I have a function in which I search all elements of the database and still causes "categoria_id" to become "categoria_nome" requesting the field in another table, however when I try to use this select I again receive this error:

Warning: mysqli_fetch_assoc () expects parameter 1 to be mysqli_result, boolean given in C: \ xampp \ htdocs \ warehouse \ assets \ php \ banks \ database.php on line 16

Here 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 assimilation between fields?

Database structure:

 *Tabela Produtos:* 

 id

 nome

 descricao

 quantidade

 categoria_id

 localizacao

 ponto_minimo

 ponto_maximo


 *Tabela Categorias:*

  id

  nome

php mysql sql

asked by anonymous 18.08.2017 / 03:10

1 answer

Separate the values from List Percentage Mask

score 4 · Accepted Answer

It looks like your query is wrong, 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 query execution, change the line:

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

To:

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