MySQL JOIN whether null or not

I'm pretty out of practice with MySQL and PHP, but I have a project I'm working on for a friend that involves selecting data from two tables, and combining them into one result - seems simple.

Table 1 has 13 fields, but the important ones are id (auto-increment, primary key) and serial (unique). The rest are just ones like customer, description, etc. etc.

Pictures has 3 fields, picID (auto-increment, primary key), imagePath and serial

I need to retrieve all data from Table 1, and if there is a matching photo (identified by the same serial - only ever 1 photo possible per serial) in Pictures, then retrieve that data too. I then output the data from Table1, and use imagePath from Pictures to build an image in HTML if one has been uploaded.

The query I've been using is:

$sql = "SELECT * FROM Table1 LEFT JOIN Pictures ON Table1.serial = Pictures.serial ORDER BY Table1.serial";


Which seems perfect, EXCEPT if any row from Table1 does not have a photo match in Pictures, the serial is no longer returned with the rest of the data, although the remainder of the row is all correct.

I have looked into the different types of JOIN, and whether it's just UNION that I need, but I am a bit stumped. How should I query to get each row of Table1 plus Pictures.imagePath added on to the matching Table1 row, if it exists?

Thank you for your time!!!! :)

EDIT with dumped array output

I don't see any problem with your query. I would suggest to post some test data.
– imankurpatel000
Jun 30 at 3:33

Thanks for looking! On further inspection of the dumped array data, I can see that although the serial is returning empty, the matching item in the array [4] is showing the correct serial number (18001)??
– WebsiteCreationNZ
Jun 30 at 3:49

1 Answer
1

It's doing that because both Table1 and Pictures have a column called serial and it drops the table names when it is generating the array keys. Probably its doing something like this internally:

$result = array()
$result[0] = Table1.serial;
$result['serial'] = Table1.serial;
$result[1] = Table1.client;
$result['client'] = Table1.client;
....
$result[14] = Pictures.serial;
$result['serial'] = Pictures.serial;


So you end up with only Picture.serial as the value for the key 'serial' in the resulting array.

One way to fix this would be to specify your columns explicitly and don't include Pictures.serial, like this:

SELECT
Table1.id,
Table1.client,
Table1.location,
Table1.description,
Table1.serial,
Pictures.notes
FROM
Table1
LEFT JOIN
Pictures ON Table1.serial = Pictures.serial
ORDER BY
Table1.serial


Thank you!! Really appreciate the explained reason, and the shared solution as well. Makes for a long query with 13 fields to reference, but it's worth it all because it WORKS and now I know why. Thanks so much for your time! :)
– WebsiteCreationNZ
Jun 30 at 5:31

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.