Asked  7 Months ago    Answers:  5   Viewed   35 times

I've found a few answers for this using mySQL alone, but I was hoping someone could show me a way to get the ID of the last inserted or updated row of a mysql DB when using PHP to handle the inserts/updates.

Currently I have something like this, where column3 is a unique key, and there's also an id column that's an autoincremented primary key:

$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3";
mysql_query($query);

$my_id = mysql_insert_id();

$my_id is correct on INSERT, but incorrect when it's updating a row (ON DUPLICATE KEY UPDATE).

I have seen several posts with people advising that you use something like

INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id) 

to get a valid ID value when the ON DUPLICATE KEY is invoked-- but will this return that valid ID to the PHP mysql_insert_id() function?

 Answers

17

Here's the answer, as suggested by Alexandre:

when you use the id=LAST_INSERT_ID(id) it sets the value of mysql_insert_id = the updated ID-- so your final code should look like:

<?
    $query = mysql_query("
        INSERT INTO table (column1, column2, column3) 
        VALUES (value1, value2, value3) 
        ON DUPLICATE KEY UPDATE
            column1 = value1, 
            column2 = value2, 
            column3 = value3, 
            id=LAST_INSERT_ID(id)
    ");
    $my_id = mysql_insert_id();

This will return the right value for $my_id regardless of update or insert.

Wednesday, March 31, 2021
 
Dail
answered 7 Months ago
45

Per the documentation:

The ID generated for an AUTO_INCREMENT column by the previous query on success, 0 if the previous query does not generate an AUTO_INCREMENT value, or FALSE if no MySQL connection was established.

The documentation states that it can only return 0 if the query last executed does not generate an AUTO_INCREMENT value, which should mean that your PRIMARY KEY column in notes is not properly setup with auto_increment. I would recommend double-checking that the PRIMARY KEY column in notes is in-fact setup with auto_increment (never hurts to check again!).

Viewing your sample code, you do call mysql_insert_id() immediately after insertion, so there shouldn't be any conflicting queries in between to skew the result.

The only thing I see that may cause an issue is that you're passing the MySQL resource to mysql_query(), but not to mysql_insert_id():

if (!mysql_query($note_sql,$con))
...
$note_id = mysql_insert_id();

There may be a conflict in the resources due to this. Try updating your call to:

$note_id = mysql_insert_id($con);
Wednesday, March 31, 2021
 
msg
answered 7 Months ago
msg
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
10

I've used ON DUPLICATE KEY UPDATE a lot. For some situations it's non-standard SQL extension that's really worth using.

First, you need to make sure you have a unique key constraint in place. The ON DUPLICATE KEY UPDATE function only kicks in if there would've been a unique key violation.

Here's a commonly used format:

 $query = "INSERT INTO $table (column1, column2, column3)
 VALUES ('value-1', 'value-2', 'value-3')
 ON DUPLICATE KEY UPDATE
 column1 = values(column1),
 column2 = values(column2),
 column3 = values(column3);"

column1 = values(column1) means "Update column1 with the value that would have been inserted if the query hadn't hit the duplicate key violation." In other words, it just means update column1 to what it would've been had the insert worked.

Looking at this code, it doesn't seem correct that you're updating all three of the columns you're trying to insert. Which of the columns has a unique constraint on it?

EDIT: Modify based on 'SET' format of mysql insert statement per the question from the OP.

Basically to use ON DUPLICATE KEY UPDATE, you just write the insert statement as you normally would, but add the ON DUPLICATE KEY UPDATE clause tacked onto the end. I believe it should work like this:

INSERT INTO $table 
    set column1 = 'value-1',
        column2 = 'value-2',
        column3 = 'value-3'
ON DUPLICATE KEY UPDATE
    column1 = values(column1),
    column2 = values(column2),
    column3 = values(column3);

Again, one of the columns you're inserting has to have a unique index (or a combination of the columns). That can be because one of them is the primary key or because there is a unique index on the table.

Wednesday, August 4, 2021
 
Sean Werkema
answered 3 Months ago
52

From Mysql Docs

In the case of "INSERT ... ON DUPLICATE KEY UPDATE" queries, the return value will be 1 if an insert was performed, or 2 for an update of an existing row.

Use mysql_affected_rows() after your query, if INSERT was performed it will give you 1 and if UPDATE was performed it will give you 2.

Monday, August 23, 2021
 
Elton Stoneman
answered 2 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :