CODEWITHSUNDEEP
mysql
Pascale Abou Abdo
Pascale Abou Abdo

Reputation: 387

mysql updating last inserted id

I want to update a table by getting the last inserted id but it is giving no results. here is the query :

 $quer = mysql_query("UPDATE date 
                      SET d_startdate = '$start', d_enddate = '$end' 
                      WHERE d_id = LAST_INSERT_ID() AND d_sid = $id");

d_id is the primary key and d_sid is a foreign key of another table

Upvotes: 2

Views: 13671

Answers (4)

Cynical
Cynical

Reputation: 9558

LAST_INSERT_ID gives you the id of the most recent insert.

Suppose that you added a row which has d_id=10 (d_id set by auto increment) and d_sid=20 and then another one with d_id=11 (again, auto increment) and d_sid=30.

You then want to look for the most recent insert with d_sid=20, but doing this with LAST_INSERT_ID is not possible, since LAST_INSERT_ID has value 11, and no row matches with that d_id and d_sid=20. You have to keep track by yourself of the most recent d_id for each category, most likely when you insert the new rows.

  • Do the INSERT
  • SELECT the LAST_INSERT_ID (name it L_ID)
  • Store the tuple <d_sid, L_ID> somewhere, so you know that for d_sid your most recent INSERT has value L_ID
  • UPDATE your table where the d_sid is the one you want and d_id is taken from the tuple

As a side note, mysql_* is deprecated, so you should switch to something else like mysqli or PDO.

Upvotes: 0

Neeraj
Neeraj

Reputation: 9145

I have used INSERT as well as UPDATE operation on my same table and its working fine. You can change this query as per your need.

<?php
$con = mysql_connect("localhost","root","") or die("Could not connect");
mysql_selectdb("test", $con);
$query = 'INSERT INTO item (`name`) VALUES ("DELTaaaA")';
$res = mysql_query($query, $con) or die(mysql_error());
echo "<pre>";
print_r($res);
$query = 'UPDATE item set name="DELTaaaA1" WHERE id = LAST_INSERT_ID()';
$res = mysql_query($query, $con) or die(mysql_error());
print_r($res);
?>

It should return 1 1

Upvotes: 4

Peeyush Kushwaha
Peeyush Kushwaha

Reputation: 3623

I don't know the normal syntax, but PDO syntax is quiet simple, you can get last inserted id by the function PDO::lastInsertId() use it as $myPDOobject->lastInsertId() . More information here : http://php.net/manual/en/pdo.lastinsertid.php

Upvotes: 0

user7282
user7282

Reputation: 5196

use code like this before that SELECT LAST_INSERT_ID() and assign this to a variable, then use that variable in your code

Upvotes: 0

Related Questions