Problem with LEFT JOIN in PHP

Question

Hello, my friends.

I have 3 tables in my mysqli bank to record access to a particular location:

1. visitor, whose data will always be filled in.

2. corporate, whose data will be filled in only if the visitor represents a company.

3. vehicle, whose details will be filled out if the visitor comes by car.

When making a search to return all 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 the logs where I have no corporate visitor data or vehicle data, it returns my visitor id as NULL.

I think it’s some PHP problem because when doing this query in phpmyadmin, it returns all the data normally.

Does anyone have any suggestions???

Thank you.

Answer 1

Try creating aliases for the id column in the visiting table, or change the name. If in the tables involved there are several columns with the same name (id_visitor) it may be 'choosing' the last ones (corporate.id_visitane, vehicle.id_visitor) that in fact are empty and end up overwriting the value of the first one (visitor.id_visitor).

Remember that you do not need to bring three times the value of the id_visitor

The ideal would be something like this: (Just one 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