Problem with LEFT JOIN in PHP

Question

Problem with LEFT JOIN in PHP

Navigation

#1 by (0 votes)

0

Hello, friends.

I have 3 tables in my mysqli database to register access to a given location:

visitor, whose data will always be filled.

corporate, whose data will be populated only if the visitor represents a company.

Vehicle, whose data will be filled if the visitor comes by car.

When doing a search to return all the results, I use the following query:

SELECT * 
FROM 'visitante' 
LEFT JOIN 'corporativo' ON 'corporativo'.'id_visitante' = 'visitante'.'id_visitante' 
LEFT JOIN 'veiculo' ON 'veiculo'.'id_visitante' = 'visitante'.'id_visitante' 
WHERE 'visitante'.'entrada' LIKE '%$data%' OR 'visitante'.'status' LIKE '%Aberto%' 
GROUP BY 'visitante'.'id_visitante' 
ORDER BY 'visitante'.'entrada';

What happens is that in records where I do not have corporate visitor data or vehicle data, it returns my viewer id as NULL.

I think it's a PHP problem because when you do this query in phpmyadmin, it returns all the data normally.

Does anyone have any suggestions?

Thank you.

php mysqli

asked by anonymous 19.09.2018 / 21:21

1 answer

I can not get the URL of the image from a JSON file even though it is implemented in the same way as other data that is correctly retrieved GDB: How to add shared libraries?

score 0 · Accepted Answer

Try to create aliases for the id column in the visitor table, or rename it. If in the tables involved there are several columns with the same name (visiting_id), they may be 'choosing' the last ones (corporativo.id_visitane, vehicle_id_visitante) that are actually empty and end up overwriting the value of the first one.

Remember that you do not need to triple the value of the viewer_id

The ideal would look something like this: (Just an example)

SELECT 
    visitante.id as id_visitante
    , visitante.nome
    , corpoartivo.empresa
    , veiculo.placa
FROM
    visitante 
    LEFT JOIN corporativo ON corporativo.id_visitante = visitante.id
    LEFT JOIN veiculo ON veiculo.id_visitante = visitante.id