Mysql – PHP MYSQL Update Two Tables Using Cross Joins

MySQLPHP

I am running into a problem updating my two tables at the same time.
My first table res which needs to be updated every time the other table
rest gets input from a HTML Form.

Table res=> http://sqlfiddle.com/#!9/82bfc2/2 . column id =>Primary Key
This table holds all the information related to Train Journey. This has columns(ie. col AC_I=no. of tickets at the begging, book= tickets booked and bal=balance of tickets from the total tickets)
which describe against which Train how many tickets are available, how many were booked and what's the current balance when the user is logged in to book their tickets.

My Second table rest http://sqlfiddle.com/#!9/557e4. column PNR =>Primary Key
This table takes input from users.

My question is how to update table res and update columns which is created to store the tickets booked for each class and for a particular train name or train number.

For example: IF customer books a seat in TRAIN NAME ='A', CLASS='AB' then my other table with the same row having a column "BOOK" will auto-increment.

What I tried is My SQL Trigger and Join.
Both are not working at this time.

Trigger=> 

DELIMITER $$ 
DROP TRIGGER IF EXISTS t1i $$ 
CREATE TRIGGER t1 
  AFTER INSERT ON rest 
  FOR EACH ROW 
  BEGIN 
    UPDATE res 
      SET book = 1 
      WHERE Tr_Num = NEW.Tr_Num; 
  END $$ 
DELIMITER ;

JOIN=> 

UPDATE res 
JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num 
SET res.book = 1 
where case when rest.class ='AC I' then res.book=1 
           when rest.class ='AC II' then res.book=1 
           when rest.class ='AC III' then res.book=1 
      end

Result shows 0 Rows affected

Kindly assist.

Best Answer

You can do an UPDATE of multiple tables in a single UPDATE statement, if that is really what you want to do here. It would be something like this for a specific 'rest' row (PNR = 5):

UPDATE res
INNER JOIN rest ON res.id = rest.PNR and res.TRAIN_NUMBER=rest.Tr_Num 
SET 
  res.book = res.book + 1,
  rest.age = 21 
WHERE (rest.class ='AC I' OR rest.class ='AC II' OR rest.class ='AC III') AND rest.PNR = 5;

However, and maybe I misunderstand, but I suspect what you really want to do is actually an INSERT + an UPDATE in a single transaction. You want to INSERT a new row into 'rest', and then update the relevant numbers in 'res'.

To do this, turn off autocommit in PHP, then INSERT INTO rest ... and UPDATE res ..., and then execute a COMMIT. Make sure to catch any DB-related exceptions, and handle them by executing ROLLBACK instead of COMMIT.

This way, the operation is atomic. Other DB sessions looking at the tables will always see the results of either none or all of the updates in the transaction (assuming you're not using the non-default READ UNCOMMITTED transaction isolation level).

There is not really a good way to do an INSERT and an UPDATE in a single query, except you could wrap the two statements in a stored procedure, and then call that stored procedure to execute both statements in one go. Refer to the documentation for the CREATE PROCEDURE syntax for details and/or see e.g. a stored procedure tutorial at w3resource.com. Note that it's still a good idea to wrap the two statements in a transaction inside the stored procedure.

Related Question