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, Sep 03 '12 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 http://stackoverflow.com/a/1751282/919944 and it seems it's correct because it's working in phpmyadmin. — Drust, Sep 03 '12 at 21:00
Perhaps you need to look at multiple row sets: http://php.net/manual/en/pdostatement.nextrowset.php — Petah, Sep 03 '12 at 21:29

score 3 · Answer 1 · answered Apr 09 '14 at 06:17

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();

score 0 · Answer 2 · answered Sep 03 '12 at 23:08

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 03 '12 at 23:08

ArrayDude

74
2

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. – Drust Sep 04 '12 at 10:38

fetching mysql user-defined variable with pdo

2 Answers2