Why is my PHP nested while loop not working?

Question

I was trying to fetch some data from mysql using PDO. For example, there is one table for brand names like Mercedes, Audi, Bently, Toyota. And there is another table for car names of each brand. The id s from the 'brand' tables are the foreign keys in the 'cars' table. Now I want to fetch all car names inside each brand name. Here is my code :

  // Outer loop for Brands
  $query = "SELECT * FROM brand";
  $result = $db->query($query);
  while($row=$result->fetch(PDO::FETCH_OBJ)){
    $brand_name = $row->brand_name;
    $brand_id = $row->id;

    echo $brand_name;
    echo "<br>";

    // Inner loop for Cars
    $query = "SELECT * FROM cars WHERE brand_id = $brand_id";
    $result = $db->query($query);
       while($row=$result->fetch(PDO::FETCH_OBJ)){
       $car_name = $row->car_name;

       echo $car_name;
       echo "<br>";

       } // Ending of inner loop

   } // Ending of outer loop

But I got a problem here. The first brand name is fetched and then the inner loop runs and fetched the car names inside that brand. When the inner loop finished fetching all car names it should go to the outer loop again and find the next brand name. But it is not fetching the rest of the brand names and the car names as well. For example, if it finishes fetching all car names inside Toyota it doesn't go for the next brand name which is Audi.

But if I remove the inner while loop it fetched all the brand names without any errors. Please help me out with your best possible solutions. Thanks in advance.

You are using the same $result & $row variables and that's why it is not working as the var values are overwritten — jeprubio
– jeprubio, Commented Oct 14, 2019 at 5:10

ScaisEdge · Accepted Answer · 2019-10-14 05:09:24Z

0

Try use different var name otherwise you overwrite le first vars eg $sql, $result and $row and in second $sql2, $result2, $row2

  $query = "SELECT * FROM brand";
    $result = $db->query($query);
    while($row=$result->fetch(PDO::FETCH_OBJ)){
      $brand_name = $row->brand_name;
      $brand_id = $row->id;

      echo $brand_name;
      echo "<br>";

      // Inner loop for Cars
      $query2 = "SELECT * FROM cars WHERE brand_id = $brand_id";
      $result2 = $db->query($query2);
         while($row2=$result2->fetch(PDO::FETCH_OBJ)){
         $car_name = $row2->car_name;

         echo $car_name;
         echo "<br>";

         } // Ending of inner loop

     } // Ending of outer loop

answered Oct 14, 2019 at 5:09

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

Comments

Michał · Accepted Answer · 2019-10-14 05:11:32Z

You need to change variable names of your inner loop. Right now you are overriding values of your outer loop in your inner loop (result and row).

// Loop for Brands
$query = "SELECT * FROM brand";
$result = $db->query($query);
while($row=$result->fetch(PDO::FETCH_OBJ)){
$brand_name = $row->brand_name;
$brand_id = $row->id;

echo $brand_name;
echo "<br>";

// Loop for Cars
$query = "SELECT * FROM cars WHERE brand_id = $brand_id";
$result = $db->query($query);
   while($row=$result->fetch(PDO::FETCH_OBJ)){
   $car_name = $row->car_name;

   echo $car_name;
   echo "<br>";

   } // Ending of inner loop

} // Ending of outer loop

Use bindParam that PDO offers in PHP for setting variables https://www.php.net/manual/en/pdostatement.bindparam.php

Achal Gupta · Accepted Answer · 2019-10-14 05:15:38Z

Just change the variable names $query,$row and $result for the inner query and everything will work fine. Your result of the first query held in $result is getting overwritten by the result of inner query

  // Outer loop for Brands
  $query = "SELECT * FROM brand";
  $result = $db->query($query);
  while($row=$result->fetch(PDO::FETCH_OBJ)){
    $brand_name = $row->brand_name;
    $brand_id = $row->id;

    echo $brand_name;
    echo "<br>";

    // Inner loop for Cars
    $query1 = "SELECT * FROM cars WHERE brand_id = $brand_id";
    $result1 = $db->query($query1);
       while($row1=$result1->fetch(PDO::FETCH_OBJ)){
       $car_name = $row1->car_name;

       echo $car_name;
       echo "<br>";

       } // Ending of inner loop

   }

Top-Master · Accepted Answer · 2019-10-14 05:18:43Z

Your variable names override each other and you should rename them
and also consider using closeCursor() to release resources.

Applying above would look like below:

// Loop for Brands
$sql = "SELECT * FROM brand";
$brandQuery = $db->query($sql);
while ($brandRow = $brandQuery->fetch(PDO::FETCH_OBJ))
{
  $brand_name = $brandRow->brand_name;
  $brand_id = $brandRow->id;

  echo $brand_name;
  echo "<br>";

  // Loop for Cars
  $sql = "SELECT * FROM cars WHERE brand_id = $brand_id";
  $carQuery = $db->query($sql);
  while ($carRow = $carQuery->fetch(PDO::FETCH_OBJ))
  {
    $car_name = $carRow->car_name;

    echo $car_name;
    echo "<br>";

  } // Ending of inner loop
  $carQuery->closeCursor();

} // Ending of outer loop
$brandQuery->closeCursor();

Collectives™ on Stack Overflow

Why is my PHP nested while loop not working?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related