Vinothbabu’s Desk!

UPDATE and INSERT differences in syntax is an inconvenience

Posted on 08. May, 2010 by Sachin in PHP, Programming

An associative array is the conventional workaround of the coding world.. In an associative array, we can associate any key or index we want with each value.

Code block

<?php
 
include('escape_arr.php');
//create an array
$sets=array(
'userid'=>$userid,
'mimetype'=>$mimetype,
'bytesize'=>$bytesize,
'filename'=>$filename,
'extension'=>$ext,
'lastmodified'=>time(),
'height'=>$height,
'width'=>$width,
'binarydata'=>$data
);
list($sets,$cols,$values)=escape_arr($sets);
$insert_sql="INSERT INTO `avatars` ".implode(',',$cols)." VALUES(".implode(',',$values).")";
$update_sql="UPDATE `avatars` SET ".implode(',',$sets)." WHERE userid=$userid LIMIT 1";
 
if(!$avatar_exists)
{
$result=mysql_query($insert_sql) or die("Insert query failed!");
}else{
$result=mysql_query($update_sql) or die("Update query failed!");
}
echo 'Avatar updated!';
?>
escape_arr.php
<?php
function escape_arr($arr)
{
$out=array();
$sets=array();
foreach ($arr as $key => $value)
{
$k="`$key`";
$v="'".mysql_real_escape_string($value)."'";
$out[$k] = $v;
$sets[]="$k = $v";
}
return array($sets, array_keys($out), array_values($out));
}
?>

escape_arr.php

Code block

<?php
function escape_arr($arr)
{
$out=array();
$sets=array();
foreach ($arr as $key => $value)
{
$k="`$key`";
$v="'".mysql_real_escape_string($value)."'";
$out[$k] = $v;
$sets[]="$k = $v";
}
return array($sets, array_keys($out), array_values($out));
}
?>

Although the above code is elegant, an associative array forces me to think of my queries as objects. After putting my head to work, I found that a blank INSERT could put my mind at ease.

Code block

<?php
$sql="INSERT INTO `tbl` (`id`) VALUES(NULL);";
$sql.="UPDATE `tbl` SET `f1`='v1',`f2`='v2' WHERE id = LAST_INSERT_ID();";
mysql_query($sql) or die(mysql_error());
?>

Conclusion: both solutions are equally valid. You may also hard-code your queries but such coding is bad practice. There are many drawbacks for using this approach.

1. First of all, such strings can span several lines of code and make your PHP file hard to read and understand.
2. Secondly each one of your strings becomes a debugging red-flag.
3. And thirdly if you ever decided to add a new field to your table – say “birthdate” – then you will have to run through each of your hard-coded strings and add-in a field name and value for each of the strings respectively. Each string will then have an added chance for a syntax errors and make debugging harder. And since such coding practice lacks versatility, a simple task such as adding a “birthdate” field later in the future can become a nightmare.

One could also use REPLACE. However I strongly advise against using this approach since it reserves the potential to wipe your database clean due to its DELETE + INSERT logic. A better solution would be INSERT … ON DUPLICATE KEY UPDATE. However this query doesn’t address my syntax difference emphasis.

Tags: mys, mysql, PHP, queries