Lots of 'If statement', or a redundant mysql query?

Question

Hey all,

$url = mysql_real_escape_string($_POST['url']);
$shoutcast_url = mysql_real_escape_string($_POST['shoutcast_url']);
$site_name = mysql_real_escape_string($_POST['site_name']);
$site_subtitle = mysql_real_escape_string($_POST['site_subtitle']);
$email_suffix = mysql_real_escape_string($_POST['email_suffix']);
$logo_name = mysql_real_escape_string($_POST['logo_name']);
$twitter_username = mysql_real_escape_string($_POST['twitter_username']);

with all those options in a form, they are pre-filled in (by the database), however users can choose to change them, which updates the original database. Would it be better for me to update all the columns despite the chance that some of the rows have not been updated, or just do an if ($original_db_entry = $possible_new_entry) on each (which would be a query in itself)?

Thanks

Answer 1

I'd say it doesn't really matter either way - the size of the query you send to the server is hardly relevant here, and there is no "last updated" information for columns that would be updated unjustly, so...

By the way, what I like to do when working with such loads of data is create a temporary array.

$fields = array("url", "shoutcast_url", "site_name", "site_subtitle" , ....); 

foreach ($fields as $field)
 $$field = mysql_real_escape_string($_POST[$field]);

the only thing to be aware of here is that you have to be careful not to put variable names into $fields that would overwrite existing variables.

Update: Col. Shrapnel makes the correct and valid point that using variable variables is not a good practice. While I think it is perfectly acceptable to use variable variables within the scope of a function, it is indeed better not use them at all. The better way to sanitize all incoming fields and have them in a usable form would be:

$sanitized_data = array();
$fields = array("url", "shoutcast_url", "site_name", "site_subtitle" , ....); 

foreach ($fields as $field)
 $sanizited_data[$field] = mysql_real_escape_string($_POST[$field]);

this will leave you with an array you can work with:

$sanitized_data["url"] = ....
$sanitized_data["shoutcast_url"] = ....

Answer 2

Just run a single query that updates all columns:

UPDATE table SET col1='a', col2='b', col3='c' WHERE id = '5'

Answer 3

Yes, it's ok to update every field.
A simple function to produce SET statement:

function dbSet($fields) {
  $set='';
  foreach ($fields as $field) {
    if (isset($_POST[$field])) {
      $set.="`$field`='".mysql_real_escape_string($_POST[$field])."', ";
    }
  }
  return substr($set, 0, -2); 
}

and usage:

$fields = explode(" ","name surname lastname address zip fax phone");
$query  = "UPDATE $table SET ".dbSet($fields)." WHERE id=$id";

Answer 4

I would recommend that you execute the UPDATE with all column values. It'd be less costly than trying to confirm that the value is different than what's currently in the database. And that confirmation would be irrelevant anyway, because the values in the database could change instantly after you check them if someone else updates them.

If you issue an UPDATE against MySQL and the values are identical to values already in the database, the UPDATE will be a no-op. That is, MySQL reports zero rows affected. MySQL knows not to do unnecessary work during an UPDATE.

If only one column changes, MySQL does need to do work. It only changes the columns that are different, but it still creates a new row version (assuming you're using InnoDB).

And of course there's some small amount of work necessary to actually send the UPDATE statement to the MySQL server so it can compare against the existing row. But typically this takes only hundredths of a millisecond on a modern server.