tags:

views:

1394

answers:

11

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.

+5  A: 

I get downmodded for this?

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

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

Mat
A: 

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

Karl Seguin
A: 

This way:

UPDATE skills
SET level = level + 1
WHERE id = $id
Lasse V. Karlsen
A: 

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

Sean Carpenter
A: 

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

Teifion
A: 

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.

Mat
A: 

@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 :)

Teifion
A: 

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

Karl Seguin
A: 

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()!

Josh
A: 

With PDO and prepared query:

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