insert array of values into a database using SQL query?

Question

I have a PHP array of the column names in my SQL table. I also have an array of the values I want to assign to these columns. How do I put this in an SQL query. At present im writing out each column title like so:

$query = "INSERT INTO `first_page_data`(`a`, `b`, `c`, `d`, `e`, `f`, `g`, `h`) 
VALUES ('$1','$2','$3','$4','$5','$6','$7','$8')";

But there must be a way of just using the arrays?

As an extra, is there a way of defining key/value pairs to keep the two pairs of data together, and then using these to insert into the database? how is this formatted in the SQL query?

Solution 1

Here's another similar solution.

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 usage:

<?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

?>

Source: Inserting An Array into a MySQL Database Table

Solution 2

//Insert ( var , Array )
    function insert($table, $inserts) {
        $values = array_map('mysql_real_escape_string', array_values($inserts));
        $keys = array_keys($inserts);   
        return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\''.implode('\',\'', $values).'\')');
    }
/*  Samples
 insert('first_page_data', array(
    'a' => 'Just Persian Gulf',
    'b' => 'DB9',
    'c' => '2009'
));
*/

it's good And Rapid!

Comments

[pid]

With the code provided and the reasons you give there is not much to do. The `INSERT' cannot be written in any other way and you need to specify the columns and parameters. What are you trying to do?

[Marcus Adams]

This is similar to how Zend Framework does it, only you probably want to use ? for each value and bind your values in, using PDO.

[Giovanni]

Would the output of this look similar to what ive done, except its created in a much more an automated way?

[Slavko]

Yes, the final query will look exactly the same as yours. It just eliminates the hassle of manually specifying table columns if your submitted data follows their naming scheme. It also returns some useful info if in case you need it. If you want a shorter solution, @M.Eskandari's code should work just as well.

[Giovanni]

I like extended code over shorter code, i can follow it easier! Thanks!

[CT.]

foreach ($data as $key => $value) {} please, no point in adding additional looping with array_keys(), especially as array_expression in foreach