Philadelphia Reflections

The musings of a physician who has served the community for over six decades

Related Topics

Website Development
The website technology supporting Philadelphia Reflections is PHP, MySQL and DHTML. The web hosting service is Internet Planners.

George IV and Computers(1)
I got him into computers around 1960. He soon far surpassed me.

Programming;GRF4
Program notes by GRF4

New topic 694 2020-09-12 09:12:55 TITLE George IV Author :
DESCRIPTION: this is where you put a small summary blurb which appears in the section surrounded by a black box.

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 http://www.primitivetype.com/resources/htmlentities.php for encoding

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

 

Please Let Us Know What You Think

 
 

(HTML tags provide better formatting)