lime icon

Phosphorus and Lime

A Developer's Broadsheet

This blog has been deprecated. Please visit my new blog at klenwell.com/press.
PHP: mysql_insert_array
This function inserts an associative array (e.g. $ARRAY = array("first_name" => $_first_name, "last_name" => $_last_name);) in table where array keys match the database column names.

/* Fx mysql_insert_array
*****************************/
function mysql_insert_array($ARRAY, $table, $db_name='')
{
// *** DATA

# internal
$_insert_line = '';
$_values_line = '';

# flags
$_FLAG['is_col'] = FALSE;

# return
$id = FALSE;


// *** MANIPULATE

// default db
if ( $db_name == 'default' )
{
if ( defined('DB_NAME') )
{
$db_name = DB_NAME;
}
else
{
trigger_error('database not defined', E_USER_WARNING);
return FALSE;
}
}

// open DB
$link = open_db($db_name)
OR trigger_error("unable to open db [$db_name]", E_USER_WARNING);

// get table column names

# query
$SQL['show_cols'] = "SHOW COLUMNS FROM $table";
$_sqlr = mysql_query($SQL['show_cols']);

# query failed
if (!$_sqlr)
{
close_db($link);
trigger_error("Unable to check columns for table [$table]", E_USER_WARNING);
return FALSE;
}

# match column
if (mysql_num_rows($_sqlr) > 0)
{
$_i = 0;
while ($_COL_HEADERS = mysql_fetch_assoc($_sqlr))
{
$COLUMN[$_i] = $_COL_HEADERS['Field'];
$_i++;
}
}

// construct query string

# reset ARRAY
reset($ARRAY);

# cycle through array
foreach ( $ARRAY as $key => $value ) {

# reset flag
$_FLAG['is_col'] = FALSE;

# check ARRAY keys against table column names [$COLUMN]
for( $i = 0; $i < count($COLUMN); $i++ )
{
if ( $key == $COLUMN[$i] )
{
$_FLAG['is_col'] = TRUE;
break;
}
}

# if key is column, insert value
if ($_FLAG['is_col'])
{

// *** SANITIZE DATA HERE (see http://www.php.net/manual/en/function.mysql-real-escape-string.php)
$value = mysql_sanitize_input($value, $link);

$_insert_line .= "`$key`, ";
$_values_line .= "'$value', ";
}
}
## end FOREACH loop

# assemble query

# trim insert superfluous comma from query strings
$_insert_line = substr($_insert_line, 0, strrpos($_insert_line, ','));
$_values_line = substr($_values_line, 0, strrpos($_values_line, ','));

# SQL query
$SQL['insert_arrays'] = <<<SQL
INSERT INTO `$table` ( $_insert_line )
VALUES ( $_values_line )
SQL;

// run query
$_sqlr = mysql_query($SQL['insert_arrays'])
OR trigger_error('MySQL error number '.mysql_errno().': '.mysql_error());

// verify INSERT
if ( mysql_affected_rows($link) > 0 )
{
$id = mysql_insert_id();
}
else
{
$id = FALSE;
}


// *** RETURN

// close db
close_db($link);

// return result
return $id;

} /* end Fx */
/****************************/


The function mysql_sanitize_input($value, $link) is little more than a wrapper for the mysql function mysql_real_escape_string ( string unescaped_string [, resource link_identifier] ).