CODEWITHSUNDEEP
phpmysqlpdo
Drust
Drust

Reputation: 363

fetching mysql user-defined variable in php

I have a query like this 

SET @update_id := 0;  
UPDATE table SET column = something, id = (SELECT @update_id := id)  
WHERE condition  
LIMIT 1;   
SELECT @update_id;

It's supposed to update a column and return the updated row's id. and as you can see I don't have the id of the row before executing the query.
I'm using pdo, Is there any way to fetch the value of @updated_id in php?
I get SQLSTATE[HY000]: General error when I'm trying to use $stmt->fetchColumn();.
also I'm sure the query is correct cause I executed it in phpMyadmin and it returned a correct value so it's working. the result in phpMyadmin was like below: 

|   @updated_id    |  
|------------------|   
|        32        |

and the desired column in the row with the id of 32 (which had the conditions) was updated.
any thoughts?

Upvotes: 1

Views: 827

Answers (1)

Devart
Devart

Reputation: 122002

I think you can read this value as you read simple table. You also can add field alias and read this field by name - SELECT @update_id AS updated_id.

Another variant is to use SELECT...FOR UPDATE statement.

Upvotes: 0

Related Questions