PHP Getting content from SQL database with PHP works fine until I add a for loop

[Johna]

I'm trying to make a dynamic gallery page for a website.

This code works fine:

<?php
$servername = "server";
$username = "username";
$password = "password";
$dbname = "dbname";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$result = $conn->query("SELECT title, image, link FROM gallery WHERE id=1");
if ($result->num_rows > 0) {
  while ($row = $result->fetch_assoc()) {
    echo "<div onclick='window.location = `/gallery/image?name=" . $row['link'] . "`' class='image' style='background-image: url(data:image/jpg;charset=utf8;base64," . $row['image'] . ")'><div class='title'>" . $row['title'] . "</div></div>";
  }
}
?>

After searching, I found that I can find the number of rows in a table using the count() function, so I have this PHP code to save the number of rows in a variable called $images:

$images = $conn->query("SELECT COUNT(id) FROM gallery");

When I add a for loop so that I can put every image in that database table on the page, it stops working:

<?php
$servername = "server";
$username = "username";
$password = "password";
$dbname = "dbname";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
  die("Connection failed: " . $conn->connect_error);
}

$images = $conn->query("SELECT COUNT(id) FROM gallery");
for ($i=0; $i < $images; $i++) {
  $result = $conn->query("SELECT title, image, link FROM gallery WHERE id=$i");
  if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
      echo "<div onclick='window.location = `/gallery/image?name=" . $row['link'] . "`' class='image' style='background-image: url(data:image/jpg;charset=utf8;base64," . $row['image'] . ")'><div class='title'>" . $row['title'] . "</div></div>";
    }
  }
}
?>

 

[Deleted member 2829]

Only questions for now, I'm afraid...

Does $images have the expected value?
Does your for loop do that exact number of iterations ?
Do you get any values other than 1 as the result of each $conn->query inside the loop ?
Does $result->fetch_assoc return an array of the expected size ?

I don't know if and how you could debug PHP code (with a debugger I mean), but I would put some echo statements in there to follow the progress. If you don't want to clutter your page with these, there is also a trick to make PHP log to the browser's console : https://stackify.com/how-to-log-to-console-in-php/

HTH 🙂

 

[Johna]

So I echoed $images and got an internal server error (error 500)

 

[Johna]

After a bit more searching I'm using this instead, and it gets the number of rows (2):

$images = mysqli_num_rows($conn->query("SELECT * FROM gallery"));

I echoed $i in the for loop, and got 12 (1 and 2 because i have 2 rows in the DB), so the loop is working as expected.

Everything seems to be working well now. Thanks for the help.


I still don't understand why $images = $conn->query("SELECT COUNT(id) FROM gallery"); didn't work. Am I doing it wrong?

 

[Deleted member 2829]

Throughout my many SQL years I have always only used select count(*) from table to get the count of rows. In fact I did not even know you could pass a column as parameter ! My first thought was suggesting that to you, but when thinking about it I could not see why select count(field) from table would not work - let alone throw a server error. And apparently not on the query itself, but on echoing the result, which is a bit alarming. It could be due to delayed execution I suppose.

The only thing I can think of now is that perhaps some of the rows in gallery do not have the id field - if such a situation is even possible in MySql ? I would have thought not but you never know. It would have been interesting to see the database server logs, there must have been an error somewhere. But ok, all is well that ends well.

 

[Johna]

The only thing I can think of now is that perhaps some of the rows in gallery do not have the id field

I also tried * so I don't think that should be why.

 

[Deleted member 2829]

Aha ! My biased eye directly latched on the * without seeing the other change 😳
So now it looks like initially you were calling echo to display the entire result set - or rather a mysqli_result object which is really just an iterator rather than a data object. Apparently PHP doesn't like that and I can't really blame it.