Philadelphia Reflections

mysql_update_assoc

Function to make updating rows in a database table easier (and safe: quote_smart logic is implented inline).

<?php
function mysql_update_assoc ($my_table, $my_array, $where_conditions) {

//
// Update values in a MySQL database table
// Includes quote_smart code to foil SQL Injection
//
// A call to this function of:
//
//  $val1 = "foobar";
//  $val2 = 495;
//  mysql_update_assoc("tablename", array(col1=>$val1, col2=>$val2), array(table_key=>52, age=>"old"));
//
// Sends the following query:
//  UPDATE tablename SET col1 = 'foobar', col2 = 495 WHERE table_key = 52 AND age = 'old'
// 
//                  -- and --
//
//  $table_name = "tablename";
//  mysql_update_assoc($table_name, array(col1=>$val1, col2=>$val2), array(table_key=>52));
//
// Sends this:
//  UPDATE tablename SET col1 = 'foobar', col2 = 495 WHERE table_key = 52
//
// Note: the WHERE clause is always "=" and always AND
//

global $db_link;

$sql = "UPDATE $my_table SET ";

// quote_smart the data values and create a comma-separated string of column_name = value
foreach ($my_array as $key => $value)
  {
  if (get_magic_quotes_gpc()) { $value = stripslashes($value); }
  if (!is_numeric($value))    { $value = "'" . mysql_real_escape_string($value, $db_link) . "'"; }
  $sql .= "$key = $value, ";
  }
$sql = substr($sql, 0, -2);  // remove trailing ", "

// quote_smart the conditional values and create a comma-separated string of column_name = value AND
$conditional_pairs = NULL;
foreach ($where_conditions as $key => $value)
  {
  if (get_magic_quotes_gpc()) { $value = stripslashes($value); }
  if (!is_numeric($value))    { $value = "'" . mysql_real_escape_string($value, $db_link) . "'"; }
  $conditional_pairs .= "$key = $value AND ";
  }
$conditional_pairs = substr($conditional_pairs, 0, -5);  // remove trailing " AND "

$sql .= " WHERE $conditional_pairs";

$result = @mysql_query ($sql) 
          OR die ("<br />\n<span style=\"color:red\">Query: $sql UNsuccessful :</span> " . mysql_error() . "\n<br />");

return ($result) ? true : false;
}
?>

mysql_insert_assoc is a similar function that adds new records.

Thanks to https://www.primitivetype.com/resources/htmlentities.php for encoding

Originally published: Wednesday, April 22, 2009; most-recently modified: Monday, June 04, 2012