How does a leftjoin work while using a initialized prepared statement for MySQLI?

Question

The user comes to this page from a different page where they click on a link and it is appended with a ?photo_id= and then the id number. I want certain information to be available to the viewer when they arrive at this page.

I want them to be able to see the photo, the photo name, and the photographers name. The first two are not a problem, because the photo_id, photo_filename, and photo_title are all in the same table. As soon as I try to get information about this photo that is on a different tabled called photographers, that's when I experience problems.

Here's my code:

$sql = 'SELECT photos.photo_id, photos.photo_title, photos.photo_filename, photos.photographer_id, photographers.photographer_id, photographers.photographer_name
       FROM photos
       LEFT JOIN photographers ON photos.photographer_id = photographers.photographer_id
       WHERE photo_id = ?';

//initialize prepared statement
$stmt = $conn->stmt_init(); 
if ($stmt->prepare($sql)) { 
  $stmt->bind_param('i', $_GET['photo_id']); 
  $stmt->bind_result($photo_id, $photo_title, $photo_filename, $photographer_id); 
  $OK = $stmt->execute(); 
  $stmt->fetch();
}

The first three variables($photo_id, $photo_title, and $photo_filename) work fine and I can echo them out on my page, but the other variables that I added from the LEFTJOIN such as photographers.photographer_id and photographers.photographer_name which are from a different table, will not work. As it is know, the page totally breaks when I do add the extra column names on the SELECT line.

I have a feeling it has something to with the amount of variables in the bind_result() function. I think they need to match the number of columns mentioned on the SELECT line, but I don't really want to create a variable for each one.

Can anybody help me out?

Answer 1

Like you mentioned, you're selecting six columns but only binding the first four. If you don't need all six of those columns, don't select them. It isn't necessary to select the column you're joining on, if that's what you're thinking.

Answer 2

What @BipedalShark said, plus...: since you're doing a LEFT JOIN, it must mean you're prepared for photos whose photographer is unknown or missing from the photographer table (*) -- but in that case the columns you're SELECTing from that table will be NULL. Maybe you want to COALESCE them in your SQL? Or maybe you actually mean INNER JOIN...? Just checking!

(*) that's what LEFT JOIN means - it's an outer join, ensuring you get all rows from the left table that match the WHERE, even when there's no matching row in the right table