generating invoice and getting values of quantity into an array

[webdeveloper123]

Hi,

I have a invoice generating php script. The basic layout is a list of all customers from the database with an "Invoice" link beside each record. Like this:

ID  first_name last_name     address                     town               county          post_code

1    Alex             Ferguson     25 Hale Barnes       Basildon      Essex           MH3 4JE            Invoice

Now when I click on invoice I get this screen:

Product Name             quantity                 price

4 Pin Microphone                                  £48.99

Now the above is repeated for every product in the database, and where it says quantity I have an Input type of text:

 <td><input type="text" id="qty" name="qty[]"></td>

Problem is when I want to generate invoice for lets say products with the ID, 1,4 and 7 I will type in the number of quantities I want for those products. What I want is when I hit submit I should have my invoice. Problem Is I can only get the quantity into the array for the last Product on the list, not the other 9 above it (I only have 10 products). This is my current code (only showing relevant parts)

if(!empty($_POST['Submit'])) {

$qty = [];


if (isset($_POST['qty'])) {
   $qty[] = mysqli_real_escape_string($link, $_POST['qty']);
} 

and here is my print_r($qty). This is even If I have selected multiple quantities for multiple products, I only get the quantity for the last product (which is 7)

Array ( [0] => 7 )

I have also tried many other things like:

$qty = [];



array_push($qty,$_POST['qty']);

I get the same result. And I also tried:

$qty[] = $_POST['qty'];




foreach($qty as $qt){

$qty[] = $qt;

}

And my print_r($qty) for above is:

Array ( [0] => 34 [1] => 34 )

So it entered it twice, probably because both lines tell it to do the same thing. I tried a few other things, but none of them worked out. btw, I also want to generate the invoice in a function. I know I haven't shown that in the above code but I'm still trying to get the basics down first

Many thanks

[dodgeitorelse3]

your submit already sends an array of quantities but then you check to see if $_POST['Submit']) is not empty you then create $qty = [];

I think you would want to use $qty = $_POST['qty']

Edited May 22, 2022 by dodgeitorelse3
re-read OP post

[webdeveloper123]

I only have 1 text input but its in a loop. Here is the code:

<?php 

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

				 <td><input type="text" id="qty" name="qty[]"></td>
				
				<td>£<?php echo($d_row["price"]); ?></td>
</tr>
		<?php 
		}
	}
?>

 

[Barand]

i would name my qty fields using the product id as the key

<td><input type="text" id="qty" name="qty[<?=$d_row['id']?>]"></td>
				
				

then your $_POST['qty'] array for products 1, 4 and 7 will look like

Array (
    1 => 5,
    4 => 15,
    7 => 10
)

 - you're products and ids in one bundle.

[webdeveloper123]

34 minutes ago, dodgeitorelse3 said:

I think you would want to use $qty = $_POST['qty']

tried that before as well.

28 minutes ago, Barand said:

<td><input type="text" id="qty" name="qty[<?=$d_row['id']?>]"></td>

Hey thanks Barand! I had a sneeky feeling this morning it was going to be something like that, because you did something similar in that check box code you gave me, and I couldn't think of any other way of getting the correct product_id to the corresponding quantity amount!

[webdeveloper123]

I'm having some problems with using the values in the array.

I used Barands code but mine looked like this:

<td><input type="text" id="qty" name="qty[<?=$d_row['product_id']?>]"></td>

And the other part is:

$quantity = [];


$quantity[] = $_POST['qty'];

 

Now i'm sure the key is called product_id and the values are called qty but i'm using:

foreach ($quantity as $key => $value) {
   echo ("key = $key and value = $value");
   }

But I get error:

Notice: Array to string conversion in

Then I use:

echo $quantity['product_id'];

and I get error:

Undefined index: product_id in

Because I want to start making the invoice, and for that I have to query the db using product_id and multiply the price by the quantities. 

Thanks

Edited May 22, 2022 by webdeveloper123

[Barand]

We can't see the query which get the list of products, so we don't know what the index should be.

It will be whatever your SELECT clause says  it is.

[mac_gyver]

31 minutes ago, webdeveloper123 said:

id="qty"

the DOM ids must be unique. since you are producing that markup in a loop, you either need to dynamically build the id attribute using unique values or if you are not using the id attribute at all, just remove it.

32 minutes ago, webdeveloper123 said:

$_POST['qty']

$_POST['qty'] is an array, with the keys being the product ids and the values being the quantities. you would probably want to filter out the zero quantities, see array_filter() to do so. you would then have an array of the non-zero entries. rather than to waste time copying this to another variable, which you are doing incorrectly (you are setting the first element in $quantity to be the entire $_POST['qyt'] array), just loop over the original variable holding the data.

[Barand]

Should look something like this

<?php 

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    
    $quantities = array_filter($_POST['qty']);      # remove 0 qtys
    
    foreach ($quantities as $id => $qty) {
        echo "Product #$id : $qty ordered<br>" ;
    }
    
}

$results = $pdo->query("SELECT id, description FROM product");

?>
<!DOCTYPE html>
<html lang='en'>
<head>
<title>Vehicle Allocation</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
</head>
<body>

<form method='POST'>
    <table style='width:500px'>
        <tr style='background-color: #ccc'><td>ID</td><td>Product</td><td>Quantity</td></tr>
        
        <?php
            foreach($results as $r) {
                echo "<tr><td>{$r['id']}</td>
                          <td>{$r['description']}</td>
                          <td>
                            <input type='number' value='0' name='qty[{$r['id']}]'>
                          </td>
                      </tr> ";
            }
        ?>
    </table>
    <br>
    <button>Submit</button>
</form>
</body>
</html>

 

[webdeveloper123]

17 hours ago, mac_gyver said:

you are setting the first element in $quantity to be the entire $_POST['qyt'] array),

ahh I get it, that's why it wasn't working!

 

17 hours ago, Barand said:

Should look something like this

Thanks for the code Barand, it really helped!

[webdeveloper123]

Hey guys,

I'm stuck on what to do next. As in my first post, I have the invoice screen which when I put the quantities and hit submit it takes me to the generate invoice screen and I want that to be the final screen, with all data in the db and the invoice rendered on the screen. Thing is, I have to calculate the prices. Now If I knew which product(s) they were before hand, then I could do a simple Select query, get the prices and multiply them to get the price. Thing is I won't know in advance which products will be purchased. So how do I make my query then? It could be product_id 3,5 and 9 , but how would I express that? Is the SQL echoed using an if statement? Or should I do SELECT id, name, price FROM product into an array and then somehow compare the $quantities array from Barands post with the array from the select statement? 

Thanks 

 

 

[Barand]

Once you have the product ids and quantities from your POST data, write them to a db table (cart or order_items - call it what you will)

Then you just query that table joined to product table to produce the invoice.

This is the bare bones of the process

+----------------+       +----------------+
| product        |       | order_item     |
+----------------+       +----------------+
| id             |       | id             |
| description    |- - - <| product_id     |
| price          |       | qty            |
+----------------+       +----------------+ 
                         


SELECT  p.description
     ,  o.qty
     ,  o.qty * p.price as amount
FROM order_item o
     JOIN
     product p ON o.product_id = p.id
WHERE o.id = ?

 

[webdeveloper123]

Thanks Barand, never thought of it that way, I'll give it a go!

[mac_gyver]

5 hours ago, webdeveloper123 said:

I'm stuck on what to do next

aren't you at the point of inserting data in to the order table - member_id and date columns, get the last insert id from that query, get the current prices for the product ids from the product table, then insert the data in to the order_item table - order_id, product_id, qty, and price columns that you defined in the ERD?

[webdeveloper123]

16 hours ago, mac_gyver said:

aren't you at the point of inserting data in to the order table - member_id and date columns, get the last insert id from that query

Yes! I have done all that (quote selection part) and now I'm on to the order_details table. I am trying to use my $_POST['qyt'] and write the values to the database. order_item_id is fine as thats Auto-increment, order_id is fine as thats the last insert id. I am getting product_id and qty into the database but it's not the expected output. As for price in the order_details table, I am just passing a fixed variable (e.g $price = 15;) for development purposes at the moment whilst trying to figure out how to get my POST array into the db, correctly. When I've done that, I will do the price.

[webdeveloper123]

3 hours ago, webdeveloper123 said:

I am getting product_id and qty into the database but it's not the expected output.

As in I am getting something into product_id and qty , but not what I was expecting

[Barand]

This illustrates the mechanics of the process.

It processes the posted quantities, writes them to an order and order item file then generates the invoice html. The invoice is written to orders/order_xxx.html where xxx is the order number

Example invoice output

The tables used are

    customer                                        order
    +-------------+-------------+                   +-------------+----------+
    | Field       | Type        |                   | Field       | Type     |
    +-------------+-------------+                   +-------------+----------+
    | customer_id | int(11)     |                   | id          | int(11)  |
    | fname       | varchar(45) |                   | customer_id | int(11)  |
    | lname       | varchar(45) |                   | order_date  | datetime |
    +-------------+-------------+                   +-------------+----------+


                                                    
    product                                         order_item
    +-------------+---------------+                 +------------+---------------+
    | Field       | Type          |                 | Field      | Type          |
    +-------------+---------------+                 +------------+---------------+
    | id          | int(11)       |                 | id         | int(11)       |
    | description | varchar(50)   |                 | order_id   | int(11)       |
    | price       | decimal(10,2) |                 | product_id | int(11)       |
    +-------------+---------------+                 | qty        | int(11)       |
                                                    | price      | decimal(10,2) |
                                                    +------------+---------------+          `

Code

<?php 
include '../db_inc.php';     ## use your own
$pdo = pdoConnect();         ## connecction code

session_start();

$_SESSION['customer'] = 125;    ## I am assuming this would be set when the customer signed in

$order_id = null;

if ($_SERVER['REQUEST_METHOD'] == 'POST') {
    
    $quantities = array_filter($_POST['qty']);      # remove 0 qtys
    
    try {
        $pdo->beginTransaction();
        
        // write order record
        $stmt = $pdo->prepare("INSERT INTO `order` (customer_id) VALUES (?)");
        $stmt->execute([ $_SESSION['customer'] ]);
        // get the id of the order
        $order_id = $pdo->lastInsertId();
        
        // now write the order item records, also storing the current prices frmm product table
        $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price)
                               SELECT ?, id, ?, price
                               FROM product
                               WHERE id = ?
                              ");
        foreach ($quantities as $id => $qty) {
            $stmt->execute([ $order_id, $qty, $id ]);
        }
        $pdo->commit();
    }
    catch (PDOException $e) {
        $pdo->rollBack();
        $order_id = null;
        throw $e;
    }
    
    // prepare the invoice output
    
    $res = $pdo->prepare("SELECT oi.order_id
                               , DATE_FORMAT(o.order_date, '%M %e %Y') as order_date
                               , concat(c.fname, ' ', c.lname) as customer
                               , p.description as product
                               , oi.qty
                               , oi.qty * p.price as amount
                          FROM  order_item oi 
                                JOIN `order` o ON o.id = oi.order_id
                                JOIN customer c ON c.customer_id = o.customer_id
                                JOIN product p ON oi.product_id = p.id
                          WHERE oi.order_id = ?   
                         ");
    $res->execute([ $order_id ]);
    
    $row = $res->fetch();
    $inv_total = 0;
    $inv_output =  "<!DOCTYPE html>
                    <html lang='en'>
                    <head>
                    <title>Sample order/invoice</title>
                    <meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
                    </head>
                    <body>

                    <table style = 'width: 400px;'><tr><td><b>Customer<b></td><td>{$row['customer']}</td></tr>
                           <tr><td><b>Order No<b></td><td>" . sprintf('%06d', $order_id) . "</td></tr>
                           <tr><td><b>Order Date<b></td><td>{$row['order_date']}</td></tr>
                    </table><br><br>
                    <table style='width: 600px; border-collapse: collapse;'>
                        <tr style='background-color:#000; color: #FFF'>
                            <td><b>Product</b></td>
                            <td><b>Quantity</b></td>
                            <td><b>Amount</b></td>
                        <tr>
                    ";
    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());
    
    $inv_output .= "<tr><td colspan='3'>&nbsp;</td></tr>
                    <tr><td colspan='2'><b>TOTAL</b></td><td>$inv_total</td><?tr>
                    </table>
                    </body>
                    <?html>";
                    
    // WRITE INVOICE OUTPUT TO HTML FILE
    file_put_contents("orders/order_{$order_id}.html", $inv_output);
    header("Refresh: 0");
    exit;
}

else {
    $results = $pdo->query("SELECT id
                         , description 
                         , price
                    FROM product");

    $form_output = "
    <form method='POST'>
        <table style='width:500px'>
            <tr style='background-color: #ccc'><td>ID</td><td>Product</td><td>Quantity</td><td>Price</td></tr>
    ";

    foreach($results as $r) {
        $form_output .= "<tr><td>{$r['id']}</td>
                              <td>{$r['description']}</td>
                              <td>
                                <input type='number' value='0' name='qty[{$r['id']}]'>
                              </td>
                              <td style='text-align: right;'>{$r['price']}</td>
                         <tr> ";
    }

    $form_output .= "
        </table>
        <br>
        <button>Submit</button>
        </form>
        ";
}
?>
<!DOCTYPE html>
<html lang='en'>
<head>
<title>Sample order/invoice</title>
<meta http-equiv='Content-Type' content='text/html; charset=utf-8'>
</head>
<body>

<?php

    if ($order_id) {                   // was a new order created?
         echo $inv_output;
    }
    
    else {                             // if not, dsplay form
         echo $form_output;
    }
?>
</body>
</html>

 

[webdeveloper123]

I wrote the POST data to the db, it was simpler then I thought it was going to be. But now I have to create a dynamic sql select statement based on user input. I'll explain why. My order_details table looks like this:

order_item_id (PK)       order_id (FK)       product_id       qty       price
1                                      1                             2                   4
2                                      1                            6                   8
3                                      2                           3                    1
4                                      3                           4                    7
5                                      3                           1                    10
6                                      3                           9                    6

Now the price  must be the total price for that row. So in order_item_id 1, that person ordered 4 units of product_id 2. Let's say product_id 2 is £10. Then the price would be £40. I have to do that for each row. I am not storing the price of the total invoice because that would be a derived attribute. I Know I can use WHERE and AND in select but it's a bit different if you don't know the id before hand. 

Thanks

[Barand]

The code I posted shows you how to pull the current price from the product table into the order_detail table . This bit...

    $quantities = array_filter($_POST['qty']);      # remove 0 qtys
    
    try {
        $pdo->beginTransaction();
        
        // write order record
        $stmt = $pdo->prepare("INSERT INTO `order` (customer_id) VALUES (?)");
        $stmt->execute([ $_SESSION['customer'] ]);
        // get the id of the order
        $order_id = $pdo->lastInsertId();
        
        // now write the order item records, also storing the current prices frmm product table
        $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price)
                               SELECT ?, id, ?, price
                               FROM product
                               WHERE id = ?
                              ");
        foreach ($quantities as $id => $qty) {
            $stmt->execute([ $order_id, $qty, $id ]);
        }
        $pdo->commit();
    }
    catch (PDOException $e) {
        $pdo->rollBack();
        $order_id = null;
        throw $e;
    }

 

[mac_gyver]

1 hour ago, webdeveloper123 said:

now I have to create a dynamic sql select statement based on user input

1 hour ago, webdeveloper123 said:

I have to do that for each row

1 hour ago, webdeveloper123 said:

it's a bit different if you don't know the id before hand

no, it seems like you think that the only way to produce the quantity * price for each row of data is if you hard-code the calculation for each row? you are missing the point, and probably not examining the posted information, of a computer program using variable/dynamic values at run-time. the term in the  posted invoice sql query - oi.qty * p.price as amount, calculates the quantity * price for each row in the result set. the where clause - WHERE oi.order_id = ? determines which rows are in the result set, matching the current $order_id value.

[Barand]

NOTE:

In the script I posted earlier I used

oi.qty * p.price as amount

in the query to produce the invoice. As the invoice is being produced immediately in my script, the values product.price and order_item.price will be the same.

However, if there is a delay before producing the invoices, or if you subsequently use the data to produce an end-of-quarter or end-of-year sales report, use the price from the order_item table - this is the price at which is was sold. The product price could have moved on since the sale.

[webdeveloper123]

17 hours ago, Barand said:

This illustrates the mechanics of the process.

 

Hey thanks for the code Barand. We must have posted at pretty much the same time so I only saw your post after I posted mine!

 

15 hours ago, mac_gyver said:

the term in the  posted invoice sql query - oi.qty * p.price as amount, calculates the quantity * price for each row in the result set

yep, must have missed that one.

Thanks for the help guys!

[webdeveloper123]

23 hours ago, Barand said:

// now write the order item records, also storing the current prices frmm product table
        $stmt = $pdo->prepare("INSERT INTO `order_item` (order_id, product_id, qty, price)
                               SELECT ?, id, ?, price
                               FROM product
                               WHERE id = ?
                              ");

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? Because i've never seen that before.

Then you have:

foreach ($quantities as $id => $qty) {
            $stmt->execute([ $order_id, $qty, $id ]);
        }

which makes no mention of price attribute. Sorry I'm trying to re-write the PDO in mysqli

Edited May 25, 2022 by webdeveloper123

[webdeveloper123]

This is what I have at the moment (that's working)

    $last_id = mysqli_insert_id($link);



foreach($quantities as $key => $val) {

       $price = 15;

   
          $sql="INSERT INTO order_details (order_id, product_id, qty, price )VALUES('$last_id', '$key', '$val', '$price')";
         $order_d = mysqli_query($link, $sql);
      }

I'm really stuck

Edited May 25, 2022 by webdeveloper123

[webdeveloper123]

23 hours ago, Barand said:

SELECT ?, id, ?, price
                               FROM product
                               WHERE id = ?
                              ");
        foreach ($quantities as $id => $qty) {
            $stmt->execute([ $order_id, $qty, $id ]);

Oh wait. The first ? after the SELECT references $order_id, the 2nd $qty and the third ? represents product_id. But there is no mention of a $price variable to the order_details (which you have referred to as order_items