Jump to content

generating invoice and getting values of quantity into an array


Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Posted (edited)
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 by webdeveloper123
Link to comment
Share on other sites

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

image.png.f952d35bb3c340e81d721f7ab531a61c.png

Link to comment
Share on other sites

Posted (edited)

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 by webdeveloper123
Link to comment
Share on other sites

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

Link to comment
Share on other sites

Posted (edited)

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 by webdeveloper123
Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.