5

I updated a row in database using PDO. 

 $stmt = $db->prepare("UPDATE product SET price=? WHERE seller=?");
             $stmt->execute(array(456,"Apple"));

This didn't worked for me

$updated_id = $db->lastInsertId();

How can i get the id of that updated row.

Brainy Prb
  • 303
  • 1
  • 3
  • 11
  • There is only 1 product with seller = Apple – Brainy Prb Oct 12 '14 at 05:07
  • oh okay, i don't think you can get those ids in one query, you need to select those, last insert id pertains to the last insertion – Kevin Oct 12 '14 at 05:09
  • just select id first, then update (or vice versa) – Mark Miller Oct 12 '14 at 05:16
  • Yes i did the same ,it worked. I have 6 rows in the table.Now i want to query and find the products whose status = 1 and then among them i want to assign "Apple" to first result.How can i do that? – Brainy Prb Oct 12 '14 at 05:19

3 Answers3

3

Best you can do is use PDO::rowCount() to get the number of rows affected. Remember, your update statement may not update only one, so, there is no way to just get a single id.

brian
  • 2,745
  • 2
  • 17
  • 33
  • In my case only one row will be updated,show is there any solution? – Brainy Prb Oct 12 '14 at 05:08
  • If you know one row is to be updated, you can re-query using the data you supplied to it. select id from product where price = ? and seller = ?; – brian Oct 12 '14 at 05:09
  • Yes it worked,i did the same. I have 6 rows in the table.Now i want to query and find the products whose status = 1 and then among them i want to assign "Apple" to first result.How can i do that? – Brainy Prb Oct 12 '14 at 05:15
0

Please have a look at this answer How to get ID of the last updated row in MySQL?

or do a select on same where clause (If you think it will have only one record)

$stmt = $db->query("SELECT id FROM product WHERE seller=:seller");
$stmt->bindParam(':seller', $sellerName, PDO::PARAM_STR)
$row =$stmt->fetchObject();
echo $row->id;
Community
  • 1
  • 1
Maz I
  • 3,664
  • 2
  • 23
  • 38
-1

Use this

$sql="Update table_1 set col_1=?,col_id = LAST_INSERT_ID(col_id) where col_2=? limit 1;SELECT LAST_INSERT_ID() last_update_id;";
$stmt =$dbh->prepare($sql);
$stmt->execute(array('new_val','cond_val'));
$stmt->nextRowset();
$rez=$stmt->fetch(PDO::FETCH_ASSOC);
print_r($rez);
nuke nuke
  • 1