PHP MySQL Table Update Function

Typing out the full PHP/MySQL for each UPDATE query within a very dynamic PHP application quickly becomes a tedious task. Since starting out with PHP ten years ago I’ve written my own functions to make such tedious tasks easier, I keep them all in a functions.php file which I include in all my projects. It takes a while to create and remember the syntax for such functions, but once you do you will save a lot of time. The same concept as using a framework, but without the bulk.

Below is a function I wrote for running UPDATE queries which:

• automatically escapes data
• allows MySQL functions to pass through with the use of *function* e.g. *NOW()*
• allows you to update columns with empty string/null values
• allows a single or an unlimited number of columns to be updated
• saves you from typing out long UPDATE queries
• assumes that you have a column called id, presumably a primary key

Parameters are split with ::: and the first parameter is the table name, then the id of the row to update, then the column, and then the value. Add on as many columns and values as desired.

[code language="php"]ut("jobs:::1:::status:::walking the dog:::server:::fileserver2");[/code]

replaces

[code language="php"]$query="UPDATE jobs SET status=" . mysql_real_escape_string('walking the dog') .", server=" . mysql_real_escape_string('fileserver2') . " WHERE id=" . mysql_real_escape_string('1');
$result=mysql_query($query);[/code]

Below is the function and here is a more readable screenshot of the code.

[code language="php"]
function ut($data)//update table function
{
/*  Update mysql table, save time. MySQL real escape data. Pass unlimited number of columns/values. Allow MySQL function passthrough with *function*, e.g. *NOW()*
to update a column with a blank value simply skip the data. e.g. jobs:::123:::status:::  would set status to empty string for row 123
table:::row id:::column:::value:::column:::value:::column:::value

examples, don't forget that values have been through mysql_real_escaped_string(), so the equivalent query typed/sanitized manually would be much longer than shown below.
jobs:::1:::status:::walking the dog:::server:::fileserver2
UPDATE jobs SET status='walking the dog', server='fileserver2' WHERE id='1'

jobs:::1:::status::::::server:::fileserver3422
UPDATE jobs SET status='', server='fileserver342' WHERE id='1'

jobs:::1:::status:::starting the job:::addedtime:::*NOW()*
UPDATE jobs SET status='starting the job', addedtime=NOW() WHERE id='1'

Questions/improvements welcome at http://www.incero.com/?p=1301
*/
$data=explode(":::",$data);
$table=addslashes(strip_tags($data[0]));//we know the column ID will always be an int, wont need to escape this data
$id=intval($data[1]);//we know the column ID will always be an int, wont need to escape this data
connect();//our own mysql connect function, omit this if you always keep mysql open (for the whole page load)
$x=2;
while($x<sizeof($data))//Loop through the data array for all the update queries.
{
$column=strip_tags($data[$x]);
$value=$data[$x+1];
if($x==2){
$queryString= mysql_real_escape_string($column);
}
else//this isn't the first data/column set so we need a comma
{
$queryString.= ", " . mysql_real_escape_string($column);
}
if(substr($value,0,1)=="*" && substr($value,strlen($value)-1,1)=="*")
{
$queryString.= "=" . substr($value,1,strlen($value)-2);//if the value is surrouned by * then omit single quotes, allowing mysql functions to pass through, e.g. *NOW()*
}
else
{
$queryString.= "='" .mysql_real_escape_string($value) . "'";
}
$x=$x+2;
}
$sQuery = "UPDATE $table SET $queryString WHERE id='$id'";
//echo "<br>the query was $sQuery<br";
$sQueryresult = mysql_query($sQuery);
@mysql_close();//omit this if you always keep mysql open (for the whole page load)
return $sQueryresult;
}
[/code]