How to loop sql table data inside a html table

Question

I have a table for all orders made by users and I would like to send them their order summary in email. However I have managed to create a table showing the customer details and now I would like to loop all the orders by a user. My email body is store in variable $emailbody inside of which I would like to include all the items ordered by a user. I don't

<?php
include"db.php";

if (isset($_POST['invoicebtn'])) {


$invoice=$_POST['invoice'];
  
  
$result1 = mysqli_query($con,"SELECT * FROM users WHERE invoice='$invoice' ");
$rowinvoice = $result1->fetch_assoc();

$name = $rowinvoice['name'];
$email = $rowinvoice['email'];
$phone = $rowinvoice['phone'];
$address = $rowinvoice['address'];
$carttotal = $rowinvoice['orderTotal'];

date_default_timezone_set('Africa/Nairobi');
$date = date('d-m-y h:i:s');



$sql = mysqli_query($con,"SELECT * FROM orders WHERE invoice='$invoice' ");
while($row = mysqli_fetch_array($sql)){


  /*Data corespond to table columns as bolow*/

  $item=$row['item'];
  $qty=$row['quantity'];
  $price=$row['price'];
  $subtotal=$row['subtotal'];

/*I would like to loop all the data from the page and use it in the table below which I will be sending out in an email body*/
   



}




$emailbody="<table style='margin:10px auto;  border-collapse: collapse; border: 1px solid black;  padding-left:10px;'>
  <tr>
    <th colspan='4' style='border: 1px solid black; padding-left:10px;  padding-top: 6px; padding-bottom: 6px;  background-color: #00000061; color: White;    font-weight:400;'>
    
    
    <h4>Invoice Order No. # $invoice </h4>
   </th>    
  </tr>
  <tr>
    <td colspan='4' style='text-align:left; border: 1px solid #ddd; padding: 5px 20px;'>
      <p  style='text-align:right;line-height: 0.2; padding:5px 15px;'> $name </p>
      <p style='text-align:right; padding:5px 15px;'> $address </p>
      <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> $phone </p>
      <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> $email</p>
      <p style='text-align:left;line-height: 0.2; padding:5px 15px;'>Invoice Date:  $date </p>
    </td>    
   </tr>
   <tr style='padding-top: 6px; padding-bottom: 6px;  background-color: #00000061; color: White; padding-left:10px;   font-weight:400;'>
        <th style='border: 0px solid;padding-left:10px;'>ITEM</th>
      
        <th style='border: 0px solid;padding-left:10px;'>QUANTITY</th>        
        <th style='border: 0px solid;padding-left:10px;'>PRICE</th>
        <th style='border: 0px solid;padding-left:10px;'>TOTAL<ksh></th>
    </tr>
    <!--loophere the table contents here as td in a tr-->

        

    <!--loop here-->
       <td colspan='4' style='background-color:#0a0512a8;color:white; text-align: center;padding:10px 25px;'><strong>Total: $  $carttotal </strong></td>
     </tr> 
      

  </table>";

 
}else{
header("Location:orders.php?Anauthorized=true");
};

?>





<!DOCTYPE html>
<html>
<head>
  <title></title>
</head>
<body>

  <!--im echooing the variable emailbody to see what will be sent-->
  <?php echo $emailbody; ?>

</body>
</html>

know how to go about it in the table

You can not loop "inside" of a string value assignment, so you will need to do this in separate steps. Assign everything up to ` — CBroe, May 13 '22 at 11:40
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) — Dharman, May 13 '22 at 12:15

score 0 · Answer 1 · edited May 13 '22 at 12:16

Well most people will probably recommend you use some kind of template engine like twig (https://twig.symfony.com/) or mustache (https://mustache.github.io/) or another one.

But if you do want to do this without a templating engine you can do the following.

...
<!--loophere the table contents here as td in a tr-->
<?php foreach($orders as $key=>$order): ?>
<tr>
  <td>
    content 1
  </td>
  <td>
    content 2
  </td>
</tr>
<?php endforeach; ?>
...

As you can see in this code we are using the alternative syntax for control structures this looks like if(some condition): and then you can close it with a endif; where the : and the endif; basically replace curly brackets you would normally use.

Note that the above code would just be printed as html rather than in a variable and then echo'ed.

So your total html would look like this:

    <!DOCTYPE html>
    <html>
    <head>
      <title></title>
    </head>
    <body>
    
     <table style='margin:10px auto;  border-collapse: collapse; border: 1px solid black;  padding-left:10px;'>
       <tr>
         <th colspan='4' style='border: 1px solid black; padding-left:10px;  padding-top: 6px; padding-bottom: 6px;  background-color: #00000061; color: White;    font-weight:400;'>
           <h4>Invoice Order No. # <?php echo $invoice ?> </h4>
         </th>    
       </tr>
       <tr>
         <td colspan='4' style='text-align:left; border: 1px solid #ddd; padding: 5px 20px;'>
         <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> <?php echo $name ?></p>
         <p style='text-align:right; padding:5px 15px;'> <?php echo $address ?> </p>
         <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> <?php echo $phone ?> </p>
         <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> <?php echo $email ?></p>
         <p style='text-align:left;line-height: 0.2; padding:5px 15px;'>Invoice Date:  <?php echo $date ?></p>
      </td>
     </tr>
   <tr style='padding-top: 6px; padding-bottom: 6px;  background-color: #00000061; color: White; padding-left:10px;   font-weight:400;'>
        <th style='border: 0px solid;padding-left:10px;'>ITEM</th>
        <th style='border: 0px solid;padding-left:10px;'>QUANTITY</th>
        <th style='border: 0px solid;padding-left:10px;'>PRICE</th>
        <th style='border: 0px solid;padding-left:10px;'>TOTAL</th>
    </tr>
    <!--loophere the table contents here as td in a tr-->
    <?php foreach($orders as $key=>$order): ?>
    <tr>
      <td>
        content 1
      </td>
      <td>
        content 2
      </td>
    </tr>
    <?php endforeach; ?>
    <!--loop here-->
       <td colspan='4' style='background-color:#0a0512a8;color:white; text-align: center;padding:10px 25px;'><strong>Total: <?php echo $carttotal ?> </strong></td>
     </tr>
  </table> 
</body>
</html>

This alternative notation is available for multiple cases:

PHP offers an alternative syntax for some of its control structures; namely, if, while, for, foreach, and switch. In each case, the basic form of the alternate syntax is to change the opening brace to a colon (:) and the closing brace to endif;, endwhile;, endfor;, endforeach;, or endswitch;, respectively.

source: https://www.php.net/manual/en/control-structures.alternative-syntax.php

Also see the answer provided by ilanco here PHP simple foreach loop with HTML

This produced the desired results on local server but it wont echo the rows in live server I dont understand why that would be so — Paul Hagi, May 14 '22 at 03:16

score 0 · Answer 2 · answered May 13 '22 at 12:22

How about this here?

<?php
    include "db.php";
    
    if (isset($_POST['invoicebtn'])) {
    
    
        $invoice = $_POST['invoice'];
    
    
        $result1 = mysqli_query($con, "SELECT * FROM users WHERE invoice='$invoice' ");
        $rowinvoice = $result1->fetch_assoc();
    
        $name = $rowinvoice['name'];
        $email = $rowinvoice['email'];
        $phone = $rowinvoice['phone'];
        $address = $rowinvoice['address'];
        $carttotal = $rowinvoice['orderTotal'];
    
        date_default_timezone_set('Africa/Nairobi');
        $date = date('d-m-y h:i:s');
    
    
    
        $sql = mysqli_query($con, "SELECT * FROM orders WHERE invoice='$invoice' ");
        $emailbody = "<table style='margin:10px auto;  border-collapse: collapse; border: 1px solid black;  padding-left:10px;'>
        <tr>
        <th colspan='4' style='border: 1px solid black; padding-left:10px;  padding-top: 6px; padding-bottom: 6px;  background-color: #00000061; color: White;    font-weight:400;'>
        
        
        <h4>Invoice Order No. # $invoice </h4>
       </th>    
      </tr>
      <tr>
        <td colspan='4' style='text-align:left; border: 1px solid #ddd; padding: 5px 20px;'>
          <p  style='text-align:right;line-height: 0.2; padding:5px 15px;'> $name </p>
          <p style='text-align:right; padding:5px 15px;'> $address </p>
          <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> $phone </p>
          <p style='text-align:right;line-height: 0.2; padding:5px 15px;'> $email</p>
          <p style='text-align:left;line-height: 0.2; padding:5px 15px;'>Invoice Date:  $date </p>
        </td>    
       </tr>
       <tr style='padding-top: 6px; padding-bottom: 6px;  background-color: #00000061; color: White; padding-left:10px;   font-weight:400;'>
            <th style='border: 0px solid;padding-left:10px;'>ITEM</th>
          
            <th style='border: 0px solid;padding-left:10px;'>QUANTITY</th>        
            <th style='border: 0px solid;padding-left:10px;'>PRICE</th>
            <th style='border: 0px solid;padding-left:10px;'>TOTAL<ksh></th>
        </tr>";
        while ($row = mysqli_fetch_assoc($sql)) {
    
    
            /*Data corespond to table columns as bolow*/
            $emailbody .= "
            <tr>
                <td>" . $row['item'] . "</td>
                <td>" . $row['quantity'] . "</td>
                <td>" . $row['price'] . "</td>
                <td>" . $row['quantity'] * $row['price'] . "</td>
            </tr>
            ";
    
            /*I would like to loop all the data from the page and use it in the table below which I will be sending out in an email body*/
        }
    
    
    
    
    
    
    
        $emailbody .= "
      
      <!--loophere the table contents here as td in a tr-->
    
          
    
      <!--loop here-->
         <td colspan='4' style='background-color:#0a0512a8;color:white; text-align: center;padding:10px 25px;'><strong>Total: $  $carttotal </strong></td>
       </tr>
       </table>";
    } else {
        header("Location:orders.php?Anauthorized=true");
    };
    
    ?>
    
    
    <!DOCTYPE html>
    <html>
    
    <head>
        <title></title>
    </head>
    
    <body>
    
        <!--im echooing the variable emailbody to see what will be sent-->
        <?php echo $emailbody; ?>
    
    </body>
    
    </html>

How to loop sql table data inside a html table

2 Answers2