Home > OS >  How to loop sql table data inside a html table
How to loop sql table data inside a html table

Time:05-14

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

CodePudding user response:

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.

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

CodePudding user response:

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>
  • Related