Add 1 To A Field
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 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.
$id has already been forced to be an integer, thus no escaping is needed this time.
I get downmodded for this?
$sql = "UPDATE skills SET level = level+1 WHERE id = $id"; $result = $db->sql_query($sql); $db->sql_freeresult($result);
In Teifion's specific case, the phpBB DDL lists that particular field as NOT NULL, so there's no danger of incrementing NULL.
In the general case, you should not use NULL to represent zero. Incrementing NULL should 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
$sql = "UPDATE skills SET level = COALESCE(level,0)+1 WHERE id = $id";
...but let's face it: you're wrong. If everyone starts at level 0, then your DDL should include
level INT DEFAULT '0' NOT NULL
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.
- → "failed to open stream" error when executing "migrate:make"
- → October CMS Plugin Routes.php not registering
- → OctoberCMS Migrate Table
- → OctoberCMS Rain User plugin not working or redirecting
- → October CMS Custom Mail Layout
- → October CMS - How to correctly route
- → October CMS create a multi select Form field
- → October CMS - Conditionally Load a Different Page
- → How to disable assets combining on development in OctoberCMS
- → October CMS - Radio Button Ajax Click Twice in a Row Causes Content to disappear
- → OctoberCms component: How to display all ID(items) instead of sorting only one ID?
- → In OctoberCMS how do you find the hint path?
- → How to register middlewares in OctoberCMS plugin?