How to execute multiple SQL query to different table?

Question

I'm attempting to run both query to different table.

As tbl_invoice are inserted, invoice_id is created from first query (first one works) but when I put another query for tbl_invoice_details I think it doesn't actually pass the previous invoice_id value, tried with if($invoice_id != null){...} but this query doesn't seem to work for tbl_invoice_details.

Any help would be appreciated, thanks.


include_once 'connectdb.php';

$id = $_GET['id'];
$select = $pdo->prepare("SELECT * FROM tbl_product WHERE barcode=$id");
$select->execute();
$row = $select->fetch(PDO::FETCH_OBJ);

    // FOR TBL_INVOICE
    $variables
    ... 

    $insert = $pdo->prepare("INSERT INTO tbl_invoice(customer_name,order_date,subtotal,tax,discount,total,paid,due,payment_type,profit) values(:cust,:orderdate,:stotal,:tax,:disc,:total,:paid,:due,:ptype,:profit)");

    $insert->bindParam(':cust',$customer_name);
    $insert->bindParam(':orderdate',$order_date);
    $insert->bindParam(':stotal',$subtotal);
    $insert->bindParam(':tax',$tax);
    $insert->bindParam(':disc',$discount);
    $insert->bindParam(':total',$total);
    $insert->bindParam(':paid',$paid);
    $insert->bindParam(':due',$due);
    $insert->bindParam(':ptype',$payment_type);
    $insert->bindParam(':profit',$profit);
    $insert->execute();

    // FOR TBL_INVOICE_DETAILS
    
    $invoice_id = $pdo->lastInsertId();
    if($invoice_id != null){
    $rem_qty = $stock - $qty;
    $update = $pdo->prepare("UPDATE tbl_product SET pstock='$rem_qty' WHERE barcode='".$id."'");
    $update->execute();
        
    $insert = $pdo->prepare("INSERT INTO tbl_invoice_details(invoice_id,product_id,product_name,qty,price,order_date,values(:invid,:pid,me,:qty,:price,:orderdate,:profit)");
    
    $insert->bindParam(':invid',$invoice_id);
    $insert->bindParam(':pid',$pid);
    $insert->bindParam(':pname',$productname);
    $insert->bindParam(':qty',$qty);
    $insert->bindParam(':price',$total);
    $insert->bindParam(':orderdate',$order_date);
    $insert->bindParam(':profit',$profit);
    $insert->execute();
    }
    
    
?>```

Half of your queries are vulnerable to SQL injection. Learn about [Prepared Statements](https://en.wikipedia.org/wiki/Prepared_statement) with [parameterized queries](https://stackoverflow.com/a/4712113/5827005). — GrumpyCrouton, Sep 23 '20 at 17:46
all for tbl_invoice_details doesn't insert, tbl_invoice works fine — mnamz, Sep 23 '20 at 17:52
Did `$invoice_id` get a proper value, if not read: https://stackoverflow.com/questions/11776818/pdo-lastinsertid-always-return-0 — Luuk, Sep 23 '20 at 17:56
The fourth query has a missing `)` after you list the columns that you want to insert. You have a comma instead. — droopsnoot, Sep 23 '20 at 18:01
Where does `$stock` come from? If it's coming from `tbl_product` via your first query, you should update the quantity directly in the query, because there's a chance the value might have changed between the first and third query. Use something like `"update tbl_product set pstock = pstock - :qty where barcode = :barcode"` — droopsnoot, Sep 23 '20 at 18:04
alright, it works. i did some mistake in the fourth query as you mentioned — mnamz, Sep 23 '20 at 18:10
the code needs to either enable PDO to throw exceptions when an error occurs, or the code needs to check the return from each SQL call to see if an error occurred. The current code is sticking its pinky finger to the corner of its mouth (Mike Myers) Dr.Evil style "I just assume it will all go to plan. What?" if the code was reporting errors, we would see MySQL telling us there was an error. there's a whole slew of possible errors other than a simple syntax problem ... https://www.php.net/manual/en/pdo.error-handling.php — spencer7593, Sep 23 '20 at 18:20

score 0 · Answer 1 · answered Sep 23 '20 at 18:24

enable PDO error reporting:

include_once 'connectdb.php';
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$id = $_GET['id'];

when a SQL error occurs, it will get thrown as an exception. if we don't enable PDO error reporting, then the code needs to check the return from each SQL call to see if an error occurred.

The question here isn't "how to get multiple SQL query to different table". The real question is "how do I see what SQL error occurred".

How to execute multiple SQL query to different table?

1 Answers1