Perform mysql query of two tables via php

Asked

Viewed 1,972 times

2

Good afternoon guys. I have a problem that I don’t know how to solve anymore.

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))

It works.

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

If I put 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 take this sql and put in php 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))

It’s already consumed my day! What can I be missing?

  • 1

    What’s the mistake that comes?

  • 1

    Shouldn’t be INNER JOIN veiculo AS v? there is one missing AS and the same in FROM pessoa p which should be FROM pessoa AS p

  • 1

    The alias, depending on the Mysql version, can be referenced without the term AS. But particularly I don’t see it as good practice.

  • 1

    Thanks for the tips!

1 answer

2


A question, every person must have a vehicle?

Use the simplified way (without JOIN) and see if it works, otherwise let us know 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 id defined (17), without passing parameter, often the error may be yours $id also.

I hope it helps

  • thanks. Solved my problem once and for all!

  • for another table, this would be: SELECT p.id, p.name, v.model m.brand FROM person p, vehicle v , mark m WHERE p.vehicle = v.id AND p.brand = m.mark AND p.id = 17

  • Exact @Marciapereirareis :D

Browser other questions tagged

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