While loop with fetch(PDO::FETCH_ASSOC) returns only the first row

Question

0.00/5 (No votes)

See more:

Hi guys! I have this code for booking system, where the while loop is supposed to return all rows with dates for the coming week. I have also some conditions for booking within this loop. The while loop used to work before I added those conditions for $action (join, unavailable and already booked), but now it doesn't work as it should even if I remove the conditions.

Any input will be greatly appreciated!

session_start();
require_once "config.php";

$publishedClasses = "";
$query = $pdo->prepare("SELECT * FROM classes WHERE class_date > CURDATE()");
$query->execute();
$getClass = $query->fetchColumn();
if($getClass>0){
  $stmt = $pdo->prepare("SELECT * FROM classes WHERE class_date > CURDATE() AND class_date < CURDATE() + interval 7 DAY");
  $stmt->execute();
  while($query = $stmt->fetch(PDO::FETCH_ASSOC)){
    $title = $query['class_title'];
    $sub_title = $query['class_sub_title'];
    $date_tb = date('l, F jS Y', strtotime($query['class_date']));
    $date = $query['class_date'];
    $time = date('G:i', strtotime($query['class_time']));
    $seats = $query['class_seats'];
    $places = $query['class_places'];
    $classId = $query['class_id'];
    $email = $_SESSION['email'];

    $query = $pdo->prepare("SELECT count(*) FROM booking WHERE res_date = ? AND res_email = ?");
    $query->execute([$date, $email]);
    $numDate= $query->fetchColumn();
    //echo $numDate;
    $stmt = $pdo->prepare("SELECT count(*) FROM booking WHERE class_id=?");
    $stmt->execute([$classId]);
    $res = $stmt->fetchColumn();
    //echo $res;
    if($res < $places){
    $action = '<p class="available"><a type="submit" class="available" href = "booking/book_class.php?id='.$class['class_id'].'">JOIN</a></br></p>';
    //echo $action;
    } elseif($numDate>=1){
        $action = '<p class="unavailable">YOU HAVE ALREADY BOOKED A CLASS FOR THIS DATE</p>';
        //echo $action;
    } else {
        $action = '<p class="unavailable"><a class="unavailable" href = "https://www.youtube.com/channel/UCc81Z0PhHjTUy8w800sH2Vw">UNAVAILABLE<br/>Click here to train at home</a></br></p>';
        //echo $action;
      }
    }

        // Define classes to be published
        $publishedClasses .= '
          <div align="center" class="wrapper">
          <div class="row booking">
            <div class="col">
              <p class="training1">'.$classId.'</p>
              <p class="training1">'.$title.'</p>
              <p class="training2">'.$sub_title.'</p>
            </div>
            <div class="col middle">
              <p class="date">Date: '.$date_tb.'</p>
              <p class="date">Time: '.$time.'</p>
            </div>
            <div class="col action">
              <p class="unavailable"><a type="submit" class="unavailable" href = "booking/book_class.php?id='.$classId.'">'.$action.'</a></br></p>
            </div>
          </div>';
          echo $publishedClasses;

} else { $publishedClasses .= '    <div align="center" class="wrapper">
                                    <div class="row booking">
                                      <div class="col">
                                        <p class="noclasses">There\'s no classes available.</p>
                                      </div>
                                    </div>';
                                    echo $publishedClasses;
  }

What I have tried:

I have searched the web for solutions for a few days, but found nothing that could fix it. I tried to go back to the original code that worked, without the check for $action, but it won't work anymore.

Posted 8-Mar-21 22:02pm

InnaTS

Updated 8-Mar-21 23:03pm

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Chris Copeland · Accepted Answer · 2021-03-08T23:03:00

Solution 1

Your while loop depends on the $stmt variable to contain the results of the query listing all of the classes, yet within the loop you're overwriting the results stored there with another block:

PHP

while($query = $stmt->fetch(PDO::FETCH_ASSOC)){
  ..
  $stmt = $pdo->prepare("SELECT count(*) FROM booking WHERE class_id=?");
  $stmt->execute([$classId]);
  ..
}

You need to consider naming your variables differently, perhaps in a way that identifies what those variables store. Be aware that anything that happens within the while loop could affect variables outside of that loop.

Posted 8-Mar-21 23:03pm

Chris Copeland

Comments

InnaTS 9-Mar-21 5:31am

Thanks a lot for quick response! It absolutely makes sense! I have now changed the name of the stmt and query to actionstmt and actionquery, and it only echos the last row now. I am not a very experienced programmer.

"Be aware that anything that happens within the while loop could affect variables outside of that loop." - could this be the reason? I just don't quite understand how this would affect the code.

Chris Copeland 9-Mar-21 8:17am

For the issue with only one row being printed, it's probably because the line starting $publishedClasses .= .. isn't contained inside the while loop so it's not going to run on every row. You'd need to move it within the loop to make sure it runs for each record, at the moment it'll only run when the loop has ended.

InnaTS 9-Mar-21 8:20am

Thanks a million for your help! It works like a charm! :)