<p>How do I turn the following 2 queries into 1 query</p>

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

++$level;

$sql    = "UPDATE skills SET level = $level WHERE id = $id;";
$result = $db-&gt;sql_query($sql);
$db-&gt;sql_freeresult($result);
</code></pre>

<p>I'm using it in a phpBB 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.</p>

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

[{'answer_id': 5848, 'author': 'Josh', 'author_id': 257, 'author_profile': 'https://Stackoverflow.com/users/257', 'pm_score': 6, 'selected': True, 'text': '<p>I get downmodded for this?</p>\n\n<pre><code>$sql = "UPDATE skills SET level = level+1 WHERE id = $id";\n$result = $db-&gt;sql_query($sql);\n$db-&gt;sql_freeresult($result);\n</code></pre>\n\n<p>In Teifion\'s specific case, the phpBB DDL lists that particular field as NOT NULL, so there\'s no danger of incrementing NULL.</p>\n\n<p>In the general case, you should not use NULL to represent zero.  Incrementing NULL <em>should</em> give an answer of NULL. If you\'re the kind of misguided developer who thinks NULL=0, step away from keyboard and find another pastime, you\'re just making life hard for the rest of us. Of course, this is the computer industry and who are we to say you\'re wrong? If you\'re not wrong, use</p>\n\n<pre><code>$sql = "UPDATE skills SET level = COALESCE(level,0)+1 WHERE id = $id";\n</code></pre>\n\n<p>...but let\'s face it: you\'re wrong. If everyone starts at level 0, then your DDL should include</p>\n\n<pre><code>level INT DEFAULT \'0\' NOT NULL\n</code></pre>\n\n<p>in case the programmers forget to set it when they create a record.  If not everyone starts on level 0, then skip the DEFAULT and force the programmer to supply a value on creation. If some people are beyond levels, for whom having a level is a meaningless thing, then adding one to their level equally has no meaning.  In that case, drop the NOT NULL from the DDL.</p>\n'}, {'answer_id': 5849, 'author': 'Mat', 'author_id': 48, 'author_profile': 'https://Stackoverflow.com/users/48', 'pm_score': 2, 'selected': False, 'text': '<pre><code>$sql = &quot;UPDATE skills SET level = level + 1 WHERE id = $id&quot;;\n</code></pre>\n<p>I just hope you are properly sanitising <code>$id</code> elsewhere in your code!</p>\n'}, {'answer_id': 5850, 'author': 'Karl Seguin', 'author_id': 34, 'author_profile': 'https://Stackoverflow.com/users/34', 'pm_score': 2, 'selected': False, 'text': '<p>try this</p>\n\n<pre><code>UPDATE skills SET level = level + 1 WHERE id = $id\n</code></pre>\n'}, {'answer_id': 5851, 'author': 'Lasse V. Karlsen', 'author_id': 267, 'author_profile': 'https://Stackoverflow.com/users/267', 'pm_score': 4, 'selected': False, 'text': '<p>This way:</p>\n\n<pre><code>UPDATE skills\nSET level = level + 1\nWHERE id = $id\n</code></pre>\n'}, {'answer_id': 5852, 'author': 'Sean Carpenter', 'author_id': 729, 'author_profile': 'https://Stackoverflow.com/users/729', 'pm_score': 1, 'selected': False, 'text': '<p>How about:  </p>\n\n<pre><code>UPDATE skills SET level = level + 1 WHERE id = $id;\n</code></pre>\n'}, {'answer_id': 5861, 'author': 'Josh', 'author_id': 257, 'author_profile': 'https://Stackoverflow.com/users/257', 'pm_score': -1, 'selected': False, 'text': "<p>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 have noticed.</p>\n\n<p>Also: yes, <code>mysql_escape_string()</code>!</p>\n"}, {'answer_id': 25535, 'author': 'Imran', 'author_id': 1897, 'author_profile': 'https://Stackoverflow.com/users/1897', 'pm_score': 3, 'selected': False, 'text': '<p>With PDO and prepared query:</p>\n\n<pre><code>$query = $db-&gt;prepare("UPDATE skills SET level = level + 1 WHERE id = :id")\n$query-&gt;bindValue(":id", $id);\n$result = $query-&gt;execute();\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/5846', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1384652/']