Asked  7 Months ago    Answers:  5   Viewed   29 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?

 Answers

17

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
)
BEGIN
    UPDATE item SET score = score + 1 WHERE id = id_in;
    SELECT score AS new_score FROM item WHERE id = id_in;
END
$$            -- Finish CREATE PROCEDURE statement
DELIMITER ;   -- Reset DELIMITER to standard ;

In PHP:

$result = mysql_query("CALL increment_score($id)");
$row = mysql_fetch_array($result);
echo $row['new_score'];
Wednesday, March 31, 2021
 
BetaRide
answered 7 Months ago
71

you can't print the result from mysqli_query, it is mysqli_resource and for dumping the error you need to change mysql_error() to mysqli_error()

$username = "bob";
$db = mysqli_connect("localhost", "username", "password", "user_data");
$sql1 = "select id from user_information where username='$username'";
$result = mysqli_query($db, $sql1) or die(mysqli_error());
while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) { 
    echo $row['id'].'<br>'; 
} 
Saturday, May 29, 2021
 
pamelus
answered 5 Months ago
39

yo need create the user "pma" in mysql or change this lines(user and password for mysql):

/* User for advanced features */
$cfg['Servers'][$i]['controluser'] = 'pma'; 
$cfg['Servers'][$i]['controlpass'] = '';

Linux: /etc/phpmyadmin/config.inc.php

Tuesday, July 13, 2021
 
ShadowZzz
answered 4 Months ago
61

Something like this maybe?

include $_SERVER['DOCUMENT_ROOT'] . '/goalview/includes/db.inc.php';

$sql = array();
$sql[] = "DELETE FROM `persons` WHERE `persons`.`id` = '$person' AND `owner = '$user' AND `userOrContact` = 'contact';"
$sql[] = "DELETE FROM `tabs` WHERE `person` = '$person' AND `ownerIdentity` = '$user' AND `selfOrOther` = 'other';"
$sql[] = "DELETE FROM `tabAccess` WHERE `person`= '$person' AND `givenToIdentity` = '$user';"
$sql[] = "DELETE FROM `personAccess` WHERE `viewedPerson` = '$person' AND `viewerIdentity` = '$user';"

$aff_rows = 0;

foreach($sql as $current_sql)
{
 $deleteContacts = mysqli_query($link, $current_sql); 
 $aff_rows = $aff_rows + mysqli_affected_rows($link);
}
Friday, August 6, 2021
 
krs8785
answered 3 Months ago
52

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, August 12, 2021
 
CKing
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :