MySQL get row ids of affected rows and update them again after update

Question

Basically I'm updating my orders table and modifying the status if the date is older than x amount. What I want to do is get the affected rows and insert a row into my events table to record the event and my events table recognize its orders by a order_id field which should be the same as the order_id in the orders table.

So down to some code.

UPDATE `orders` SET `status`='7' WHERE `date` < (UNIX_TIMESTAMP(NOW()) - 172800) AND `status` = '2';

The rows that this query updates has to go through some for each loop to insert a row for each affected row into my events table. Some how I have to get the order_id to use in this query:

INSERT INTO `orders_events` (`order_id`, `event_id`, `user`, `date`) VALUES (12345678, 6, "Admin", UNIX_TIMESTAMP(now()))

I was figuring to first select the orders and insert them into a temporary table and modify them there and then select them again to get the row id's and insert the event data from the rows in the temporary table. However i ran into a problem here since i have no idea how to get the rows and insert into the events with a kind of for each loop in mysql.

What would be the proper and most efficient way of doing this in a stored procedure / mysql scheduled event?

Also I'd like to add, Locking the table is a no go since its being updated by multiple users every minute.

Answer 1

Solved it!

INSERT INTO `orders_events` (`order_id`, `event_id`, `user`, `date`) 
SELECT id, 6, 'Admin', UNIX_TIMESTAMP(NOW()) FROM `orders` WHERE `date` < (UNIX_TIMESTAMP(NOW()) - 172800) AND `status` = '2';

UPDATE `orders` SET `status`='7' WHERE `date` < (UNIX_TIMESTAMP(NOW()) - 172800) AND `status` = '2';

Answer 2

You could use this to get the information you are looking for

$affected = mysql_info();
//this will let you see what is returned
echo '<pre>'; print_r($affected); die();

http://php.net/manual/en/function.mysql-info.php

However, you will notice that it is being deprecated. If you aren't already using PDO or Mysqli you should look into doing so. Here is the Mysqli equivalent of the above http://php.net/manual/en/mysqli.info.php