Jump to content

webdeveloper123

Members
 View Profile  See their activity

  • Posts

    437

  • Joined

  • Last visited

  • Days Won

    1

 Content Type 

Posts posted by webdeveloper123

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted · Edited by 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

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted · Edited by 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

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted · Edited by 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

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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!

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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.

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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 

     

     

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted · Edited by 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

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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!

    generating invoice and getting values of quantity into an array

    in PHP Coding Help

    Posted

    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

×
×
  • 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.