Inserting An Array into a MySQL Database Table

Written by Cory S.N. LaViska on October 25th, 2007

A streamlined method of inserting an array of values into a MySQL database with detailed results on success or failure.

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.

Comments

Me Array is overfull, therefor i cant run it without errors, is there away arround without loosing some of my inputs?

#1 Einar on Nov 7th, 2007

Einar, if by "errors" you mean timing out, then I fear you are limited by what your web server can handle in one page load. Since the function makes one query per execution, I don't believe this is a good solution for your particular application.

You may want to consider modifying the function to use the MySQLi extension, which would allow you to take advantage of the mysqli_multi_query() function (http://us2.php.net/manual/en/function.mysqli-multi-query.php)

For a large number of queries, it may even be practical to write them to a file first, then import them into the database using LOAD DATA (http://dev.mysql.com/doc/refman/5.0/en/load-data.html).

#2 Cory S.N. LaViska on Nov 7th, 2007

No, the Array is to full..
http://pastie.caboo.se/115422
As you se, the last entry is 'tag' wich was supposed to be 'tags'. If I add a entry before tags, then tags dissapear and the same thing happend.

So the Array cant hold more information..

(i use xampp on my Mac)

#Sorry my bad English!

#3 Einar on Nov 8th, 2007

Fixed...

#4 Einar Gangso on Nov 12th, 2007

This is a great little script. Thanks. But have you (or anyone) thought about using it along with something like the md5 function?

#5 Jamie on Jun 4th, 2008

nevermind! i figured it out. simply wrap the post variable in the md5 function in the php before runnign the insert. works like a champ.

#6 jamie duncan on Jun 4th, 2008

This is a very helpful code. thnx

#7 foysal osmany on Oct 3rd, 2008

I do thank you very much for the helping hand. It kept my from reinventing the wheel once again!

Ta ra

A Beautiful Site

Search the Notebook

Bookmark this Article

Popular Entries

Related Entries

Archives

Products & Services

Advertisements