php loop start over when num_rows==0

Question

I'm trying to write a php code to select form tables:

books
images

Some books does not have an image, so I want to skip it and select another book. I have wrote this code but it does not work with me perfectly.

Now I'm getting only 5 records! it must be 6 as I limited in the book select query.

$slider_sql = "select * from books  limit 6";
$slider_result = $conn->query($slider_sql);
while($slider_row = $slider_result->fetch_assoc()) {
   extract($slider_row);

   $img_sql = "SELECT big_img FROM images WHERE book_id = '$id'";
   $img_rs = $conn->query($img_sql);
   $img_row = $img_rs->fetch_assoc();

   if ($img_rs->num_rows == 0) 
       continue; //--> here I want to start while again to select another book.
    echo $book_name.'<br>';
    echo $img_row['big_img'].'<br>';
}

Thanks for your help and time!

This is pretty old school, I'd suggest using PHP PDO object with a simple foreach loop. Best of luck! — Aaron Belchamber
– Aaron Belchamber, Commented Jul 24, 2017 at 19:41
WARNING: When using mysqli you should be using parameterized queries and bind_param to add user data to your query. DO NOT use string interpolation or concatenation to accomplish this because you have created a severe SQL injection bug. NEVER put $_POST, $_GET or any user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman
– tadman, Commented Jul 24, 2017 at 19:50
A LIMIT 6 means that it will return as many as 6 records, but you're not guaranteed to get exactly 6. — tadman
– tadman, Commented Jul 24, 2017 at 19:51

Qirel · Accepted Answer · 2017-07-24 19:42:37Z

3

Instead of having a sub-query in a loop (which is nearly ALWAYS a bad idea!), use a JOIN instead, which simplifies it to one query instead of two. Then set a condition that big_img should not be empty. This guarantees that you will only find rows where there's an image matching the book. LIMIT will still only ensure the return of 6 rows. <> in MySQL is the same as !=.

$slider_sql = "SELECT b.book_name, i.big_img 
               FROM books b 
               JOIN images i 
                 ON i.book_id=b.id 
               WHERE i.big_img <> '' 
               LIMIT 6";
$result = $conn->query($slider_sql);
while ($row = $result->fetch_assoc()) {
    echo $row['book_name'].'<br>';
    echo $row['big_img'].'<br>';
}

MySQL JOIN

answered Jul 24, 2017 at 19:42

Qirel

26.6k7 gold badges54 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Atef Sugair Over a year ago

Fantastic, I just added GROUP BY i.book_id because some books has many images. Well done @Qirel

Collectives™ on Stack Overflow

php loop start over when num_rows==0

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related