generating invoice and getting values of quantity into an array

[webdeveloper123]

20 minutes ago, webdeveloper123 said:

So does the select statement insert the values into the table?

No that doesn't make any sense

 

and I don't have $order_id in my product table

Edited May 25, 2022 by webdeveloper123

[Barand]

10 minutes ago, webdeveloper123 said:

I'm a little bit stuck. I notice you don't have VALUES to go with your insert statement. Are you substituting VALUES with the SELECT statement? So does the select statement insert the values into the table?

Firstly, One of the syntaxes for an insert statement allows you to to directly insert records into a table that are selected from another.

Useful for backing up a table to a copy...

CREATE TABLE copy_of_A LIKE table_A;
INSERT INTO copy_of_A SELECT * FROM table_A;

Secondly, you are correct about the placeholders referencing $order_id, $qty and $id. The $id is used to select the product in the select statement. The price comes from the product row

[webdeveloper123]

Yes but I am trying to get the price of that row into the order_details (order_items) table, then do the sql for the invoice

[webdeveloper123]

5 minutes ago, Barand said:

Firstly, One of the syntaxes for an insert statement allows you to to directly insert records into a table that are selected from another.

I never knew that!

[Barand]

2 minutes ago, webdeveloper123 said:

and I don't have $order_id in my product table

Order_id comes from the creation of the order record. If the input is for product #2 and qty 25 and we just created an order with id = 1234

mysql> SELECT 1234, id, 25, price
    -> FROM product
    -> WHERE id = 2;
+------+----+----+-------+
| 1234 | id | 25 | price |
+------+----+----+-------+
| 1234 |  2 | 25 |  9.00 |
+------+----+----+-------+

The result row is inserted into the order_item table.

 

[webdeveloper123]

ahh so with that select statement, that is what is inserting price into order_details?

[Barand]

Did I hear the sound of a penny dropping?

[webdeveloper123]

17 hours ago, Barand said:

Did I hear the sound of a penny dropping?

I think so. I think I've had this the wrong way round from the start. So the price attribute in order_items, that's not the result of qty * price , that is just price, as it appears in the product table?

[Barand]

Yes, it's to preserve the price at which the product was sold so that historical reports can use that price and not the price it is currently being sold at (if at all). Keeps the accounting straight.

[webdeveloper123]

lol. Thanks barand. I can't believe I had it the wrong way round all this time!

[mac_gyver]

don't you recall in the ERD thread where this was suggested -

 

[webdeveloper123]

yes that was why I included it, but at the time I "understood" it, I understood it wrong

[webdeveloper123]

On 5/24/2022 at 6:45 PM, Barand said:

$inv_total = 0;

 

On 5/24/2022 at 6:45 PM, Barand said:

do {
        $inv_output .= "<tr><td>{$row['product']}</td>
                            <td>{$row['qty']}</td>
                            <td>{$row['amount']}</td>
                        </tr>";
        $inv_total += $row['amount'];            
    } while ($row = $res->fetch());

I'm having trouble getting the total cost of the invoice. I know the above code does it but the condition of the do..while loop is in PDO. I tried to match it using Mysqli but this is as far as i've got:

          $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 = mysqli_query($link, $invoice);

                    $table = [];

while ( $row = mysqli_fetch_assoc( $invoiceOutput ) ) {
   $table[] = $row;  //add each row into the table array
}
      $amount = $table[0]["amount"]; 

 $inv_total = 0;

do {
      
        $inv_total += $table[0]["amount"];            
    } while ($row = mysqli_fetch_assoc( $invoiceOutput ));

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

I only get the invoice total for the first product

Thanks

Edited May 28, 2022 by webdeveloper123

[webdeveloper123]

16 minutes ago, webdeveloper123 said:

I only get the invoice total for the first product

 

As in I only get the amount charged for the first product

[Barand]

Here's a mysqli version

<?php
include '../db_inc.php';
$link = myConnect('test');
  
$last_id = 1046;
  
$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();                                                                           // READ 1st ROW OF RESULTS
                                                                                                                // RETRIEVE AND OUTPUT THE INVOICE HEADER STUFF
$output = "<table style = 'width: 400px;'><tr><td><b>Customer<b></td><td>{$row['customername']}</td></tr>       

                   <tr>
                        <td>&nbsp;</td>
                        <td>
                            {$row['address']}<br>
                            {$row['town']}<br>
                            {$row['county']}<br>
                            {$row['post_code']}<br>
                       </td>
                       </tr>
                   <tr><td><b>Order No<b></td><td>" . sprintf('%06d', $last_id) . "</td></tr>
                   <tr><td><b>Order Date<b></td><td>{$row['order_date']}</td></tr>
            </table><br><br>
            <table style='width: 500px; border-collapse: collapse;'>
                <tr style='background-color:#000; color: #FFF'>
                    <td><b>Product</b></td>
                    <td><b>Quantity</b></td>
                    <td style='text-align: right;'><b>Amount</b></td>
                </tr>
         ";
do {                                                                                                           // LOOP THROUGH RESULTS OUTPUTTING DETAIL LINES
       $output .= "<tr><td>{$row['productname']}</td>
                        <td>{$row['qty']}</td>
                        <td style='text-align: right;'>{$row['amount']}</td>
                        </tr>";
        $inv_total += $row['amount'];
                    
    } while ($row = $invoiceOutput->fetch_assoc());
                                                                                                                // OUTPUT THE TOTAL
$output .= "<tr><td colspan='3'>&nbsp;</td></tr>                                                                
            <tr><td colspan='2'><b>TOTAL</b></td><td style='text-align: right;'>$inv_total</td></tr>
            </table>
            ";
            
echo $output;
?>

Giving

[webdeveloper123]

Hey Barand,

I'm using the following but I get an error:

Notice: Trying to access array offset on value of type null in 

On line:

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

Here is more of my code:

<?php
          $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);


                    $table = [];

while ( $row = mysqli_fetch_assoc( $invoiceOutput ) ) {
   $table[] = $row;  
}
      ?>

<table>
   <tr>
      <th><strong>Product name</strong></th>
   
      <th><strong>Quantity</strong></th>

      <th><strong>Amount</strong></th></tr>

      <?php 

      $inv_total = 0;

      $row= $invoiceOutput->fetch_assoc();

   

      $name = $table[0]["customername"]; 
      $amount = $table[0]["amount"]; 
      $orderDate = $table[0]["order_date"]; 
      $address = $table[0]["address"];
      $town = $table[0]["town"];
      $county = $table[0]["county"];
      $postCode = $table[0]["post_code"];    

 

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

                    
    } while ($row = $invoiceOutput->fetch_assoc());
  
  echo ("$inv_total</br>");

And the above echo on $inv_total prints: 0

Edited May 29, 2022 by webdeveloper123

[Barand]

Why are you using both a while() loop and a do..while() loop?

When the while loop finishes there a re no rows left to read.

I showed you how to do it. Apparently that was a total waste of my time.

[webdeveloper123]

Hey, thanks for the code. Got it working. Sorry I didn't know that the do...while and the while do the same thing

[webdeveloper123]

Hello,

I know this is a basic question and I know you have shown how to print values from the query in your $output variable but I'm used to using the:

                    $table = [];

while ( $row = mysqli_fetch_assoc( $invoiceOutput ) ) {
   $table[] = $row;  
}

   $name = $table[0]["customername"]; 

and I wanted to know what the equivalent was using your do...while loop. I am trying this:

      <?php

   $cusName = $row['customername'];
      echo ("This is name $cusName");
   print_r($row);
?>

But I get:

 Trying to access array offset on value of type null in 

On line:

  $cusName = $row['customername'];

And the print_r shows nothing at all. I also tried variations of:

 

{$row['customername']}

Trying to store the above in a variable, removing the {}, putting a ; at the end etc

Because what happened was when I used your do...while loop it broke my table array and had a knock on effect on all my storing the values like this:

      $name = $table[0]["customername"]; 

And I was echoing those values

Thanks

[webdeveloper123]

actually I think I did it, all I had to do was put it in the loop

[webdeveloper123]

Wait now my other loop is broken as well. Before when I was using the table array I just used a foreach to loop over table and print the results. But I have since amended it to this:

 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 
      }
   }
?>

It's not printing any values. Can you help please?

Edited May 29, 2022 by webdeveloper123

[Barand]

2 hours ago, webdeveloper123 said:

I didn't know that the do...while and the while do the same thing

Not quite the same

• https://www.php.net/manual/en/control-structures.while.php
• https://www.php.net/manual/en/control-structures.do.while.php

[webdeveloper123]

yeah I know they do different things, but I meant I thought I could use them both in the same code I had

[Barand]

At least you now know why you can't.

[webdeveloper123]

yes. Can you help me out with that foreach. Surely if the resultset is saved in $row, then I should be able to use $row in my foreach?