sql php find field value from updated row

Question

Say I have this loop:

foreach ($array as $a) {
    if ($a == $b) { 
        mysql_query("UPDATE table SET this = 'that' WHERE id='$a'");
        }
    }

And a table...

id    this    blah
------------------------
1     that    54
2     that    73
3     there   27

Inside that loop, I also want to find the value stored in the tables blah field from the current record that is being updated.

Whats the most effective way to do this?

Did you ever find a solution for this question? – JSuar Dec 28 '12 at 16:06 — JSuar, Dec 28 '12 at 16:06

score 1 · Answer 1 · answered Jan 13 '12 at 22:52

1

You can have your query consist of multiple statements, and the last statement is what is used for the "results".

So, you can just add a "select" statement to the end of the update query and treat it like a normal select statement:

UPDATE table SET this = 'that' WHERE id='$a'; SELECT blah from [your table] WHERE id = '$a'

The advantage with this method is that it doesn't require an additional DB call.

Of course, you will want to be escaping the values put into the SQL statements to prevent SQL injection, but that's another matter.

answered Jan 13 '12 at 22:52

cdeszaq

30,869
25
117
173

is this ok though that im updating and querying the db for each loop instance? – user1022585 Jan 13 '12 at 23:00
It depends on how many times you go through the loop, really. If it's a lot, you will want to do 1 SELECT after all the updates are done, and you may want to batch the updates. On the other hand, if it's a small number of loop iterations, then it doesn't really matter. – cdeszaq Jan 14 '12 at 08:51

score 0 · Answer 2 · edited May 23 '17 at 11:55

Update

This was my ~~first~~ second SO answer which I felt needed revising. Searching around, I found a much better answer to your question.

From the accepted answer for question: SQL: Update a row and returning a column value with 1 query

You want the OUTPUT clause

UPDATE Items SET Clicks = Clicks + 1 
OUTPUT INSERTED.Name 
WHERE Id = @Id

Similar question: Is there a way to SELECT and UPDATE rows at the same time?

Old Answer

Add a SELECT statement to the end of your UPDATE query.

mysql_query("UPDATE table SET this = 'that' WHERE id='$a'; SELECT blah WHERE id='$a';");

This prevents you from ensuring the update took place since mysql_query only returns the last statement's result.

You could also write a custom function that performs both statements but, for instance, won't preform the SELECT if the UPDATE failed, etc.

** Skeleton Function - Not Tested **

function MyUpdate($query, $id){ 
    $retVal = "-1" // some default value
    $uResult = mysql_query("UPDATE table SET this = 'that' WHERE id='$a'");

    if( $uResult )
    $result= mysql_query('SELECT blah WHERE id=$a');
        if (!$result) {
            die('Invalid query: ' . mysql_error());
        }
        $retVal = $result;
    }
    return $retVal;
}

sql php find field value from updated row

2 Answers2