mysqli_insert_id($link) will return exactly the id of last updated row. I am using this funtion in my projects for the same purpose. You can use it both in synchronous or asynchronous queries. Just insert $lastupdated_row_id = mysqli_insert_id($link) into your code and it will work for you.
Go for After Insert/update trigger. It will always give you the last inserted/updated record ,from the dynamic table called Inserted... Query: Select * from Inserted.
If you are AUTO_INCREMENT with column, then you can use last_insert_id() method. This method gets the ID of the last inserted record in MySQL.
The current ID is in $_GET['id'] . You should sanitize it before inserting it into your query: $id = intval($_GET['id']); Then, use $id in your query.
I've found an answer to this problem :)
SET @update_id := 0;
UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id)
WHERE some_other_column = 'blah' LIMIT 1;
SELECT @update_id;
EDIT by aefxx
This technique can be further expanded to retrieve the ID of every row affected by an update statement:
SET @uids := null;
UPDATE footable
SET foo = 'bar'
WHERE fooid > 5
AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;
This will return a string with all the IDs concatenated by a comma.
Hm, I am surprised that among the answers I do not see the easiest solution.
Suppose, item_id
is an integer identity column in items
table and you update rows with the following statement:
UPDATE items
SET qwe = 'qwe'
WHERE asd = 'asd';
Then, to know the latest affected row right after the statement, you should slightly update the statement into the following:
UPDATE items
SET qwe = 'qwe',
item_id=LAST_INSERT_ID(item_id)
WHERE asd = 'asd';
SELECT LAST_INSERT_ID();
If you need to update only really changed row, you would need to add a conditional update of the item_id through the LAST_INSERT_ID checking if the data is going to change in the row.
This is officially simple but remarkably counter-intuitive. If you're doing:
update users set status = 'processing' where status = 'pending'
limit 1
Change it to this:
update users set status = 'processing' where status = 'pending'
and last_insert_id(user_id)
limit 1
The addition of last_insert_id(user_id)
in the where
clause is telling MySQL to set its internal variable to the ID of the found row. When you pass a value to last_insert_id(expr)
like this, it ends up returning that value, which in the case of IDs like here is always a positive integer and therefore always evaluates to true, never interfering with the where clause. This only works if some row was actually found, so remember to check affected rows. You can then get the ID in multiple ways.
MySQL last_insert_id()
You can generate sequences without calling LAST_INSERT_ID(), but the utility of using the function this way is that the ID value is maintained in the server as the last automatically generated value. It is multi-user safe because multiple clients can issue the UPDATE statement and get their own sequence value with the SELECT statement (or mysql_insert_id()), without affecting or being affected by other clients that generate their own sequence values.
MySQL mysql_insert_id()
Returns the value generated for an AUTO_INCREMENT column by the previous INSERT or UPDATE statement. Use this function after you have performed an INSERT statement into a table that contains an AUTO_INCREMENT field, or have used INSERT or UPDATE to set a column value with LAST_INSERT_ID(expr).
The reason for the differences between LAST_INSERT_ID() and mysql_insert_id() is that LAST_INSERT_ID() is made easy to use in scripts while mysql_insert_id() tries to provide more exact information about what happens to the AUTO_INCREMENT column.
PHP mysqli_insert_id()
Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() function will also modify the value returned by the mysqli_insert_id() function.
Putting it all together:
$affected_rows = DB::getAffectedRows("
update users set status = 'processing'
where status = 'pending' and last_insert_id(user_id)
limit 1"
);
if ($affected_rows) {
$user_id = DB::getInsertId();
}
(FYI that DB class is here.)
This is the same method as Salman A's answer, but here's the code you actually need to do it.
First, edit your table so that it will automatically keep track of whenever a row is modified. Remove the last line if you only want to know when a row was initially inserted.
ALTER TABLE mytable
ADD lastmodified TIMESTAMP
DEFAULT CURRENT_TIMESTAMP
ON UPDATE CURRENT_TIMESTAMP;
Then, to find out the last updated row, you can use this code.
SELECT id FROM mytable ORDER BY lastmodified DESC LIMIT 1;
This code is all lifted from MySQL vs PostgreSQL: Adding a 'Last Modified Time' Column to a Table and MySQL Manual: Sorting Rows. I just assembled it.
Query :
$sqlQuery = "UPDATE
update_table
SET
set_name = 'value'
WHERE
where_name = 'name'
LIMIT 1;";
PHP function:
function updateAndGetId($sqlQuery)
{
mysql_query(str_replace("SET", "SET id = LAST_INSERT_ID(id),", $sqlQuery));
return mysql_insert_id();
}
It's work for me ;)
SET @uids := "";
UPDATE myf___ingtable
SET id = id
WHERE id < 5
AND ( SELECT @uids := CONCAT_WS(',', CAST(id AS CHAR CHARACTER SET utf8), @uids) );
SELECT @uids;
I had to CAST the id (dunno why)... or I cannot get the @uids content (it was a blob) Btw many thanks for Pomyk answer!
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With