Inserting An Array into a MySQL Database Table :

mysql_insert_array()

Inserts $data into $table using the associative array keys as field names and the values as values (requires an existing open database connection).

Parameters

Argument	Type	Explanation
$table	String	The name of the database table to insert into
$data	Array	The associative array containing fieldnames as keys and values
$exclude	String/Array	Optional string or array of field names to exclude from the insertion. Useful for excluding certain elements when using this on $_POST

Return Values

The function returns an associative array with the following elements:

Key	Description
mysql_error	FALSE if the query was successful, detailed MySQL error  otherwise
mysql_insert_id	The most recent ID generated from the query (only for  tables  with an AUTO_INCREMENT)
mysql_affected_rows	The number of rows affected by the query
mysql_info	MySQL information about the query

Code

<?php
function mysql_insert_array($table, $data, $exclude = array()) {


    $fields = $values = array();


    if( !is_array($exclude) ) $exclude = array($exclude);


    foreach( array_keys($data) as $key ) {
        if( !in_array($key, $exclude) ) {
            $fields[] = "`$key`";
            $values[] = "'" . mysql_real_escape_string($data[$key]) . "'";
        }
    }


    $fields = implode(",", $fields);
    $values = implode(",", $values);


    if( mysql_query("INSERT INTO `$table` ($fields) VALUES ($values)") ) {
        return array( "mysql_error" => false,
                      "mysql_insert_id" => mysql_insert_id(),
                      "mysql_affected_rows" => mysql_affected_rows(),
                      "mysql_info" => mysql_info()
                    );
    } else {
        return array( "mysql_error" => mysql_error() );
    }


}
?>

Example

<?php


// Open database here


// Let's pretend these values were passed by a form
$_POST['name'] = "Bob Marley";
$_POST['country'] = "Jamaica";
$_POST['music'] = "Reggae";
$_POST['submit'] = "Submit";


// Insert all the values of $_POST into the database table `artists`, except
// for $_POST['submit'].  Remember, field names are determined by array keys!
$result = mysql_insert_array("artists", $_POST, "submit");


// Results
if( $result['mysql_error'] ) {
    echo "Query Failed: " . $result['mysql_error'];
} else {
    echo "Query Succeeded! <br />";
    echo "<pre>";
    print_r($result);
    echo "</pre>";
}


// Close database


?>

Since every field value is sanitized through mysql_real_escape_string(), the potential for SQL injection is reduced significantly.

In a public environment, or anywhere that users can modify the array keys, you should validate and sanitize the keys in the $data array to prevent SQL errors and injections. For example, if someone forges a POST to your script with additional fields, MySQL will most likely throw an error.

To combat this, simply make sure that the keys in the array are what you expect them to be, and disallow anything foreign.