0
$query = "UPDATE transaction SET c_status = :status WHERE c_name = :name AND c_id = :id";

$stmt = $this->handle->prepare($query);

$stmt->bindParam(':c_status',$status,PDO::PARAM_STR);
$stmt->bindParam(':c_name',$name,PDO::PARAM_STR);
$stmt->bindParam(':c_id',$id,PDO::PARAM_STR);

return $stmt->execute();

Using the above syntax, I am able to update a record in the transaction table. However, what I only get with the return is a boolean. I want to know if there is a way I can get the transaction_id (the AUTO_INCREMENT field in the transaction table, c_id and c_name where just a column of that) and the rest of its columns?

fishcracker
  • 2,401
  • 5
  • 23
  • 28
  • @JoachimIsaksson - What if I won't opt to stored procedure approach? Will that limit me not to be able to do this? – fishcracker May 13 '13 at 04:24
  • With MySQL, you'll have to do an extra select, so either you need to do it manually or using a stored procedure to do it all in one call. – Joachim Isaksson May 13 '13 at 04:26

1 Answers1

3

This question is not related to PDO but to mysql in general.

UPDATE queries are not intended to return anything. To get a row from database you have to use SELECT query.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 1
    To be fair though, many RDBMS support something like this, PostgreSQL supports `UPDATE ... RETURNING` and SQL Server supports `UPDATE ... OUTPUT`. MySQL does not. – Joachim Isaksson May 13 '13 at 04:33
  • +1 I would add that you're better off declaring **c_name** as unique and possibly adding a **c_count** column if you need to. Then you can select against the strings just inserted and reliably retrieve the **c_id** s. Although possibly that might let you dispense with the ids altogether. – James K. Lowden May 13 '13 at 04:34