How to Retrieve the ID of the Last Updated Row in MySQL using PHP?

Retrieving Last Updated Row's ID in MySQL using PHP

Discovering the ID of the most recently updated row in a MySQL database is a common need in programming.

How to Accomplish This Task in PHP:

To obtain the ID of the last row modified in MySQL via PHP, utilize the provided PHP script:

<?php
// Establish database connection
$conn = new mysqli("host", "username", "password", "database_name");

// Prepare the MySQL UPDATE statement with an auto-incrementing variable
$sql = "SET @update_id := 0;
        UPDATE some_table SET column_name = 'value', id = (SELECT @update_id := id)
        WHERE some_other_column = 'value' LIMIT 1;";

if ($conn->query($sql) === TRUE) {
    // Get the last updated row's ID
    $result = $conn->query("SELECT @update_id;");
    $id = $result->fetch_assoc()["@update_id"];
    echo "Last updated row's ID: $id";
} else {
    echo "Error updating row: " . $conn->error;
}

$conn->close();
?>

Additional Considerations:

• Caution: This method should be used with care as it requires a specific column to be auto-incremented.
• Extending the Technique: You can modify this to retrieve all affected row IDs by using a similar approach with the CONCAT_WS function.

For instance, the following code will return a comma-separated string of all updated row IDs:

SET @uids := null;
UPDATE footable
   SET foo = 'bar'
 WHERE fooid > 5
   AND ( SELECT @uids := CONCAT_WS(',', fooid, @uids) );
SELECT @uids;

The above is the detailed content of How to Retrieve the ID of the Last Updated Row in MySQL using PHP?. For more information, please follow other related articles on the PHP Chinese website!