CODEWITHSUNDEEP
mysqlsql
Hanna Teobald
Hanna Teobald

Reputation: 44

MySQL Query INSERT / SELECT / UPDATE last Row

i've 3 MySQL querys in a HTML POST form. I want to update the last insert ROW i've inserted with my PHP script.

The problem what I've is, it just updates everytime the first row instead the last row i've inserted.

This is my query currently

INSERT INTO 
    tbl_orders (name,ordered,number,idcard,account) 
    VALUES 
    (:name,:carID,:number,:idcard,:account);
            
INSERT INTO 
    tbl_money (gesamtWert) 
    SELECT SUM(amount*price) total 
    FROM tbl_prices, tbl_car_pos 
    WHERE cid = :carID AND priceID = productID;

UPDATE 
    tbl_money 
    SET tbl_money.orderID = (SELECT tbl_orders.orderID FROM tbl_orders ORDER BY tbl_orders.orderID DESC LIMIT 1) 
    ORDER BY 
        orderID 
    DESC LIMIT 1;

First query selects the values of the form action and insert it into the first table.

Second one is SUM the total price of the car and insert it into the second table.

Third one should now select the last insert row of the first query and update the last insert row from the second query. but as I said it updates the first row.

Lets say i've inserted the last row with ID: 10 and insert now a new row with ID: 11, the third query will update the ID: 1 instead of ID:11. So if anyone can tell me where my problem is would be amazing.

Upvotes: 0

Views: 605

Answers (2)

Valeriu Ciuca
Valeriu Ciuca

Reputation: 2094

If you use PHP, there is a function that returns the last inserted ID.

https://www.php.net/manual/ro/function.mysql-insert-id.php

Upvotes: 0

Akina
Akina

Reputation: 42728

Integrate 3rd query into 2nd.

Without optimization, using direct insert:

INSERT INTO 
    tbl_orders (name,ordered,number,idcard,account) 
    VALUES 
    (:name,:carID,:number,:idcard,:account);

INSERT INTO 
    tbl_money (gesamtWert, orderID) 
    SELECT SUM(amount*price), 
           (SELECT tbl_orders.orderID FROM tbl_orders ORDER BY tbl_orders.orderID DESC LIMIT 1)
    FROM tbl_prices, tbl_car_pos 
    WHERE cid = :carID AND priceID = productID;

Upvotes: 1

Related Questions