fetching mysql user-defined variable with pdo

Question

I'm using pdo and I have a query like this

$stmt=$db->query("SET @update_id := 0;  
                    UPDATE table SET column = something, id = (SELECT @update_id := id)  
                    WHERE condition  
                    LIMIT 1;   
                    SELECT @update_id;");

It is supposed to update a row and return the id.
I'm sure the query itself is working because I ran it in phpmyadmin and it returned a column named @updated_id with the value of the updated row like this:

|   @updated_id    |  
|------------------|   
|  *correct id*    |

I want to fetch the value of @updated_id and store it in a php variable.
I tried $stmt->fetchColumn(); and $stmt->fetchColumn(); but I get SQLSTATE[HY000]: General error.
I've been searching for something to work but I can't find anything.
so anybody knows how to store the value of @updated_id in a php variable?
thanks

Execute your query in phpMyAdmin. What does it return? Additionally, should't it read like this: SET @update_id = 0; — SteAp
– SteAp, Commented Sep 3, 2012 at 20:51
@SteAp as I said it returns a column named @updated_id with the correct value beneath it. also I used the query from here stackoverflow.com/a/1751282/919944 and it seems it's correct because it's working in phpmyadmin. — Shayan
– Shayan, Commented Sep 3, 2012 at 21:00
Perhaps you need to look at multiple row sets: php.net/manual/en/pdostatement.nextrowset.php — Petah
– Petah, Commented Sep 3, 2012 at 21:29

jel · Accepted Answer · 2014-04-09 06:17:28Z

3

As Petah suggested in the comments, you need to iterate through each rowset until you get to the SELECT statement you want to fetch.

Given you example above, this should work:

$stmt=$db->query("SET @update_id := 0;  
                UPDATE table SET column = something, id = (SELECT @update_id := id)  
                WHERE condition  
                LIMIT 1;   
                SELECT @update_id;"); 
$stmt->nextRowset();
$stmt->nextRowset();
$update_id =  $stmt->fetchColumn();

answered Apr 9, 2014 at 6:17

jel

1,17210 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ArrayDude · Accepted Answer · 2012-09-03 23:08:52Z

0

<?php
$id = 123;
$query = "UPDATE table SET column = something, id = ? WHERE condition LIMIT 1;"
$st = $db->prepare($query);
$st->execute(array(
 $id
));

$result = $st->fetch(PDO::FETCH_ASSOC);

var_dump($result);

Do you tried something like this?, fetching the modified statement

answered Sep 3, 2012 at 23:08

ArrayDude

742 bronze badges

1 Comment

Shayan Over a year ago

sry for late response. the problem is I don't have the id and I have to either select the id in a different query and update it later or use the query written in the question.

Collectives™ on Stack Overflow

fetching mysql user-defined variable with pdo

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related