Perform mysql query of two tables via php

Question

Perform mysql query of two tables via php

Navigation

#1 by (2 votes)

2

Good afternoon, guys. I have a problem that I no longer know how to solve.

I have the following mysql query in php:

mysql_select_db("banco", $conexao);
    $resultado = mysql_query("SELECT * FROM pessoa WHERE id = '" . $cod . "'");
    while ($linha = mysql_fetch_array($resultado))

This works.

But I need to get the data from another table referenced in the person table. The person table has a foreign keyed column of the vehicle table.

If I put the sql below in PhpMyAdmin to test, it works:

SELECT p.id, p.nome, v.modelo FROM pessoa p
INNER JOIN veiculo v ON p.veiculo = v.id
WHERE p.id = 17

Now if I get this sql and put it in php it gives error:

mysql_select_db("banco", $conexao);
    $resultado = mysql_query("SELECT p.id, p.nome, v.modelo FROM pessoa p INNER JOIN veiculo v ON p.veiculo = v.id WHERE p.id = '" . $cod . "'");
    while ($linha = mysql_fetch_array($resultado))

This has already consumed my day! What can I be wrong?

php mysql sql-select mysqli

asked by anonymous 14.05.2015 / 22:08

1 answer

Redirect or deny direct access to public folder Is it possible to change my server code without having to compile?

score 2 · Accepted Answer

One question, does every person necessarily have a vehicle?

Use in a simplified way (without JOIN) and see if it works, otherwise report the error generated:

SELECT 
  p.id,
  p.nome,
  v.modelo
FROM
  pessoa p,
  veiculo v 
WHERE p.veiculo = v.id 
  AND p.id = 17

Using LEFT JOIN (respecting the person table):

SELECT 
  p.id,
  p.nome,
  v.modelo 
FROM
  pessoa p 
  LEFT JOIN veiculo v 
    ON p.veiculo = v.id 
WHERE p.id = 17

Also try to use SQL already with the defined id (17), without going through parameter, often the error can be your $id too.

Hope it helps