Viewed   52 times

I've been trying to find an answer to this question, but haven't found any definitive "yes" or "no" in all my research.

I'm running a simple MySQL query like this:

 UPDATE item SET `score`=`score`+1 WHERE `id`=1

Is there a way for that query to return the updated value, instead of the number of rows affected? Just as a reference, I'm doing this in PHP, so the actual code looks like:

 $sql = "UPDATE item SET `score`=`score`+1 WHERE `id`=1";
 $new_value = mysql_query($sql); 
 //Unfortunately this does not return the new value

I know I could do a second query and just SELECT the value, but I'm trying to cut down on queries as much as possible. Is there a way?



You can do it with a stored procedure that updates, and then selects the new value into an output parameter. The following returns one column new_score with the new value.

DELIMITER $$   -- Change DELIMITER in order to use ; withn the procedure
CREATE PROCEDURE increment_score
   IN id_in INT
    UPDATE item SET score = score + 1 WHERE id = id_in;
    SELECT score AS new_score FROM item WHERE id = id_in;
$$            -- Finish CREATE PROCEDURE statement
DELIMITER ;   -- Reset DELIMITER to standard ;


$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];
Thursday, August 11, 2022

I would say just build it yourself. You can set it up like this:

$query = "INSERT INTO x (a,b,c) VALUES ";
foreach ($arr as $item) {
  $query .= "('".$item[0]."','".$item[1]."','".$item[2]."'),";
$query = rtrim($query,",");//remove the extra comma
//execute query

Don't forget to escape quotes if it's necessary.

Also, be careful that there's not too much data being sent at once. You may have to execute it in chunks instead of all at once.

Saturday, November 5, 2022

You've hit the nail right on the head: what you want to do is SELECT userid FROM users WHERE username='$username'. Try it - it'll work :)

SELECT * is almost always a bad idea; it's much better to specify the names of the columns you want MySQL to return. Of course, you'll stil need to use mysql_fetch_assoc or something similar to actually get the data into a PHP variable, but at least this saves MySQL the overhead of sending all these columns you won't be using anyway.

As an aside, you may want to use the newer mysqli library or PDO instead. These are also much more efficient than the old mysql library, which is being deprecated.

Monday, November 28, 2022

The function you're looking for is find_in_set:

 select * from ... where find_in_set($word, pets)

for multi-word queries you'll need to test each word and AND (or OR) the tests:

  where find_in_set($word1, pets) AND find_in_set($word2, pets) etc 
Wednesday, August 17, 2022

The point is not if potential bad situations are likely. The point is if they are possible. As long as there's a non-trivial probability of the issue occurring, if it's known it should be avoided.

It's not like we're talking about changing a one line function call into a 5000 line monster to deal with a remotely possible edge case. We're talking about actually shortening the call to a more readable, and more correct usage.

I kind of agree with @Mark Baker that there is some performance consideration, but since id is a primary key, the MAX query will be very quick. Sure, the LAST_INSERT_ID() will be faster (since it's just reading from a session variable), but only by a trivial amount.

And you don't need a lot of users for this to occur. All you need is a lot of concurrent requests (not even that many). If the time between the start of the insert and the start of the select is 50 milliseconds (assuming a transaction safe DB engine), then you only need 20 requests per second to start hitting an issue with this consistently. The point is that the window for error is non-trivial. If you say 20 requests per second (which in reality is not a lot), and assuming that the average person visits one page per minute, you're only talking 1200 users. And that's for it to happen regularly. It could happen once with only 2 users.

And right from the MySQL documentation on the subject:

You can generate sequences without calling LAST_INSERT_ID(), but the utility of 
using the function this way is that the ID value is maintained in the server as 
the last automatically generated value. It is multi-user safe because multiple 
clients can issue the UPDATE statement and get their own sequence value with the
SELECT statement (or mysql_insert_id()), without affecting or being affected by 
other clients that generate their own sequence values.
Thursday, October 27, 2022
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :