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

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

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

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

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

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?

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

I never knew that!

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

No that doesn't make any sense and I don't have $order_id in my product table

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

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

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

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! yep, must have missed that one. Thanks for the help guys!

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

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

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.

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

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

ahh I get it, that's why it wasn't working! Thanks for the code Barand, it really helped!

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

tried that before as well. 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!

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

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

I agree. Thanks guys