Add 1 to a field (MySQL)

Question

How do I turn the following 2 queries into 1 query

$sql = "SELECT level FROM skills WHERE id = $id LIMIT 1;";
$result  = $db->sql_query($sql);
$level  = (int) $db->sql_fetchfield('level');
$db->sql_freeresult($result);

++$level;

$sql = "UPDATE skills SET level = $level WHERE id = $id;";
$result = $db->sql_query($sql);
$db->sql_freeresult($result);

I'm using it in a phbBB mod but the gist is that I grab the level, add one to it then update, it seems that it'd be much easier and faster if I could do it as one query.

Edit: $id has already been forced to be an integer, thus no escaping is needed this time.

Answer 1

I get downmodded for this?

$sql = "UPDATE skills SET level = level+1 WHERE id = $id";
$result = $db->sql_query($sql);
$db->sql_freeresult($result);

Answer 2

$sql = "UPDATE skills SET level = level + 1 WHERE id = $id";

I just hope you are properly sanitising $id elsewhere in your code!

Answer 3

UPDATE skills SET level = level + 1 WHERE id = $id

Answer 4

This way:

UPDATE skills
SET level = level + 1
WHERE id = $id

Answer 5

How about:
UPDATE skills SET level = level + 1 WHERE id = $id;

Answer 6

Thanks all for the quick answers, I've upmodded all as I can only give one "best answer".

Answer 7

Josh: I didn't do it but I suspect you were downmodded for having a totally incorrect answer (you were subtracting 1, and still in two queries), which you did for quite a while before you edited to a proper solution.

Answer 8

@Mat: That'd be because I wrote --$level at first, then edited it to ++$level. Josh must have seen the pre-edit version first. Sorry Josh, I hope "best answer" can make up for it :)

Answer 9

Josh: I downmodded you because, as Mat said, you had the completely wrong answer. What's showing up now isn't what I saw.

Answer 10

Mat: That's what pasted in from the question. It hasn't been edited, so I attribute that to a bug in Markdown. But, oddly enough, I did notice.

Also: yes, mysql_escape_string()!

Answer 11

With PDO and prepared query:

$query = $db->prepare("UPDATE skills SET level = level + 1 WHERE id = :id")
$query->bindValue(":id", $id);
$result = $query->execute();