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.

Author avatar

About the author

New Hampshirite building web apps in Florida. Creator of Surreal CMS, Postleaf, and DirtyMarkup.

Need to get in touch? Catch me on Twitter.