using foreach to loop over resultset from fetch_assoc();

[webdeveloper123]

Hi Guys,

I know this is pretty basic stuff and I have done it loads of times successfully (using different code structure) but I've been at it since yesterday and just can't figure it out. All my code was fine but then I had to switch from a while loop that fetches the sql query and saves each row to a array, then I loop over that array in a foreach and print out my results. Now the sql query is saved in $row, so I am trying to use $row in my foreach but it's not printing the values. Surely if my resultset is stored in $row I should be able to use foreach on it. I think my echo statements might be wrong, but I've tried multiple ways. Here is my code:

 $invoice="SELECT order_details.order_id, DATE_FORMAT(orders.order_date, '%M %e %Y') as order_date, 
concat(customer.first_name, ' ', customer.last_name) as customername, customer.address, 
customer.town, customer.county, customer.post_code, product.name as productname, order_details.qty, order_details.qty * product.price as amount 
FROM order_details JOIN orders ON orders.order_id = order_details.order_id 
JOIN customer on customer.customer_id = orders.customer_id JOIN product ON order_details.product_id = product.product_id WHERE order_details.order_id = '$last_id'"; 

                 

                  $invoiceOutput = $link->query($invoice);

                     $inv_total = 0;

      $row= $invoiceOutput->fetch_assoc();



      print_r($row);

       do {             
           
         $cusName = $row['customername'];    
         $amount = $row['amount'];  
         $orderDate = $row['order_date'];  
         $address = $row['address']; 
         $town = $row['town'];    
         $county = $row['county'];
         $postCode = $row['post_code'];
               

                                                                                             
     
      
                    $inv_total += $row['amount'];

                    
    } while ($row = $invoiceOutput->fetch_assoc());


   if ($row) {
      foreach ($row as $d_row) {
         ?>
         <tr>
            
            
            <td><?php echo($d_row["productname"]); ?></td>
            
            <td><?php echo($d_row["qty"]); ?></td>

            <td><?php echo($d_row["amount"]); ?></td>

             



                  </tr>
         <?php 
      }
   }

 

my print_r($row) shows this, which can't be right because it only shows 1 Product, when that person has actually purchased 3 products:

Array ( [order_id] => 117 [order_date] => May 30 2022 [customername] => Harry Maguire [address] => 109 Temple Gardens 
[town] => Brixton [county] => Lambeth [post_code] => B8J 3QD [productname] => 4 Pin Microphone [qty] => 4 [amount] => 195.96 )

And for the echos I also tried:

<?php echo $d_row["productname"]; ?>

<?php echo $d_row["qty"]; ?>

<?php echo $d_row["amount"]; ?>

Could someone please help?

Edited May 30, 2022 by webdeveloper123

[ginerjm]

Perhaps this is a bit easier to read (and write):

$invoice = "SELECT d.order_id, DATE_FORMAT(o.order_date, '%M %e %Y') as order_date, 
			concat(c.first_name, ' ', c.last_name) as customername, c.address, c.town, c.county, 
			c.post_code, p.name as productname, d.qty, d.qty * p.price as amount 
		FROM order_details d

		JOIN orders o
		ON o.order_id = d.order_id 

		JOIN customer c 
		ON c.customer_id = o.customer_id 

		JOIN product p 
		ON d.product_id = p.product_id 

		WHERE d.order_id = '$last_id'"; 

$invoiceOutput = $link->query($invoice);
$inv_total = 0;
//   Fetch 1 result row
$row = $invoiceOutput->fetch_assoc();
//  show the row just retrieved   WHY?
print_r($row);
//  start a loop
//  show all rows now
while ($row = $invoiceOutput->fetch_assoc());
{
	echo "
		<tr>
		<td>{$row['productname']}</td>
		<td>{$row['???']}</td>
		<td>{$row['???']}</td>
		<td>{$row['???']}</td>
		<td>{$row['???']}</td>
		<td>{$row['???']}</td>
		</tr>";
}

I used aliases in your query to show you how much easier it is to write it when you do so.

I switched the while loop to the way I like it.  Don't know if that works for you but it really should.  Please look at it carefully and understand how it works. 

I also changed your output to be easier to type as well.  Switching into and out of php mode is just silly.

I also added a comment on your print_r line because I don't know why you are doing it.

[Barand]

26 minutes ago, webdeveloper123 said:

$row= $invoiceOutput->fetch_assoc();



      print_r($row);

You fetch a row then print_r() it to see what it contains. Why are you surprised that it only shows the one row that you fetched?

[webdeveloper123]

I didn't know that it only fetched one row. I just used the code you gave me. I've only been doing php for 5 months and your do...while loop was the first proper time I used it (outside of trivial examples like print numbers 1 to 10)

[Barand]

13 minutes ago, ginerjm said:

I switched the while loop to the way I like it.  Don't know if that works for you but it really should

@ginerjm It won't work! You're loop will now print only the last two ordered items - you've thrown the first one way. That is why there was a do..while() loop.
 

Read first row

output customer details

do {

     output item details

} while read next row

 

[ginerjm]

Barand - That's why I change his code to now work properly.  I left the print_r there with my questioning comment.  He should remove it.

[Barand]

3 minutes ago, webdeveloper123 said:

I didn't know that it only fetched one row.

RTFM once in a while.

https://www.php.net/manual/en/mysqli-result.fetch-assoc.php

[webdeveloper123]

@ginerjm it looks like I have to switch the do...while loop with your while loop. Is that right? Because If I do, how am I supposed to get the invoice total which was the reason to switch the loops in the first place?

[ginerjm]

I have no idea what your overall goal is.  I just helped you out to read the query results properly.  What I gave you will output your entire result set.  Do what you will.

[Barand]

2 minutes ago, webdeveloper123 said:

how am I supposed to get the invoice total

 

set total = 0

Read first row

output customer details

do {

     output item details
     add item value to total

} while read next row

output total

 

[webdeveloper123]

So I should have a do...while and a while loop in there at the same time?

[ginerjm]

You really need to do what Barand suggests and READ A MANUAL!

You can use the do---while  OR the while{}.  Either one works.  He prefers the Do, I use the While{} method.  Make your choice and LEARN WHAT THEY DO.  And try and remember it.

[Barand]

Where, in my pseudocode, is there a while loop and a do..while loop?

[ginerjm]

I think he's reading both of our examples and thinking he needs them both....

[webdeveloper123]

I was actually on that page this morning reading through it, I'll give it a good read

[webdeveloper123]

Just now, ginerjm said:

I think he's reading both of our examples and thinking he needs them both....

YES giner!

[ginerjm]

As for your total calculations - add up the amounts as you process each row in the loop.  When the loop is done output one more table row showing what you have summed up.  It's really very simple.

[Barand]

2 minutes ago, ginerjm said:

He prefers the Do, I use the While{} method.

The do.. while is required in the case.

the data is basically

+----------+--------+
| customer | item 1 |
| customer | item 2 |
| customer | item 3 |
+----------+--------+

We read the first row and output the customer data as the order header. In our loop we now need to output the 3 items.

We still have the first row in our buffer. If we use a while loop we lose item 1 and only output items 2 and 3.

The do while lets us output item 1 then loop throuugh 2 and 3 to output those also

[ginerjm]

I see that now.  Did not realize what he/you was trying to do.

Of course I doing this, I would look for a change in order number and output the header data and keep track of the new order number and then continue on with the detail data from the current row and then move on to the next one until I caught a new order number.

[Barand]

In this instance ther is only one order - it's items from a shopping cart

[webdeveloper123]

ok thanks guys, I'll have a go at it

[webdeveloper123]

Hey guys, I am pretty close but there is a few things I don't understand why it's not working. 

The manual says: 

Fetches one row of data from the result set and returns it as an associative array. Each subsequent call to this function will return the next row within the result set.

So I make a subsequent call to the function according to Barand pseudo code (which is higher up the thread) .

This is my code:

$invoiceOutput = mysqli_query($link, $invoice);

                  $inv_total = 0;

                        $row = $invoiceOutput->fetch_assoc();

                          $cusName = $row['customername'];    
         $amount = $row['amount'];  
         $orderDate = $row['order_date'];  
         $address = $row['address']; 
         $town = $row['town'];    
         $county = $row['county'];
         $postCode = $row['post_code'];

 do {             
   
       
               $pName = $row['productname'];
               $qty = $row['qty'];
               $amount = $row['amount'];
                                                                                             
     

                    $inv_total += $row['amount'];

                    
    } while  ($row = $invoiceOutput->fetch_assoc());


 <td><?php echo $pName; ?></td>
            
            <td><?php echo $qty; ?></td>

             <td><?php echo $amount; ?></td>

If Each subsequent call to the function returns the next row, why am I only getting one row printed out within my td elements. Or is it that there is only one record there in the first place? Because in the pseudo code he says to:

Read first row

So i'm a bit confused. Can you help please?

Btw, I also tried these 2 as well:

  $row = mysqli_fetch_assoc($invoiceOutput)

                         
$row = $invoiceOutput->fetch_array(MYSQLI_ASSOC);

 

Edited May 30, 2022 by webdeveloper123

[ginerjm]

YOu really don't read the manual very well.  Either that or you just don't have a mind for this business.

You are using a loop.  But - what is inside that loop that does any kind of output?  I don't see it.  What do you think you should do about that?

And - just what are name, qry and amount for?  Are they the order header parts that need to be output before the order detail lines?  If so, then where is the output of those values before you go into the detail part?

And:

where is the start of your html table code?

Where are you showing the header information that you collected from the first row?

Do you have column headers for your html table?

Edited May 30, 2022 by ginerjm

[webdeveloper123]

I got the output of what was inside the loop. I just didn't show it. It's just not in the loop, I am printing the variables outside the loop. I am doing the php first then I am going to do the HTML and CSS later.

echo ("The customer id  is $FormId</br>");
      echo ("$cusName</br>");
      echo ("$orderDate</br>");
      echo ("$address</br>");
      echo ("$town</br>");
      echo ("$county</br>");
      echo ("$postCode</br>");
       echo ("$amount</br>");

      echo ("$inv_total</br>");

productname, qty and amount are just that, it prints the product name, qty of how much they bought and the amount for that row (eg. Blu Ray Player, 7, £150 for example (£150 is the total for 7 Blu Ray Players)

[ginerjm]

This means nothing to me.

If I understand you are getting the first row and you should be echoing out those values for the customer or whatever your header info is.

Then you should begin the do-while loop by outputting the detail data from that same row and afterwards hit the while to get the 2nd record and loop back and output the next set of details.  When the do-while completes you should then output the totals row of your html table.  Then close the table (</table).

BTW - an echo does not use parentheses.