MySQL update row to highest value plus 1

Question

This is my PHP snippet:

$con = mysqli_connect('localhost','root','','db');
if (!$con) {
    die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"db");
$sql = "UPDATE nations SET queue='MAX(queue) FROM nations' WHERE nation_name='$nation'";
$query = mysqli_query($con,$sql);
$result = mysqli_fetch_assoc($query);

The query should by my understanding get the current highest value from column queue and then add 1 to that result, and insert that new value into the table. This is not working the way I want and it simply sets the queue to 0...

Why would you think 1 would be added? Anyway, take a look here: https://dev.mysql.com/doc/refman/5.7/en/subqueries.html — jeroen, Jan 23 '18 at 11:29
Easiest way might be to use an AUTO_INCREMENT column, as far as I understand. — Robin K, Jan 23 '18 at 11:51
SQL injection is possible in your code read ( https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php ) — Raymond Nijland, Jan 23 '18 at 12:27

juergen d · Answer 1 · 2018-01-23T12:59:50.480

3

Use

UPDATE nations 
SET queue = (SELECT MAX(queue)+1 FROM nations)
WHERE nation_name = '$nation'

edited Jan 23 '18 at 12:59

answered Jan 23 '18 at 11:29

juergen d

201,996
37
293
362

Note that the OP is doing an update although the wording below is a bit confusing... – jeroen Jan 23 '18 at 11:31
*insert that new value into the table* - OP does not seem to know that he should use INSERT instead of UPDATE – juergen d Jan 23 '18 at 11:32
Or is confused about the terminology, inserting a new value in an existing row :-) – jeroen Jan 23 '18 at 11:32
Hmm, doesn't seem to be working. If I replace "(SELECT MAX(queue)+1" with 10 for example, it works. But it doesn't seem to be able to get the highest vale.. – Alexander Jovric Jan 27 '18 at 09:50
*doesn't seem to be working* means exactly? Do you get an error? – juergen d Jan 27 '18 at 09:55

MySQL update row to highest value plus 1

1 Answers1