CODEWITHSUNDEEP
phpmysqlsqlarrays
Giovanni
Giovanni

Reputation: 838

insert array of values into a database using SQL query?

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?

Upvotes: 1

Views: 46764

Answers (5)

Robert Sinclair
Robert Sinclair

Reputation: 5416

# Insert this array
$arr = array("sounds" => "one", "sound" => "two", "big" => "blue");

function addQuotes($str){
    return "'$str'";
}

# Surround values by quotes
foreach ($arr as $key => &$value) {
    $value = addQuotes($value);
}

# Build the column
$columns = implode(",", array_keys($arr));

# Build the values
$values = implode(",", array_values($arr));

# Build the insert query
$query = "INSERT INTO table (".$columns.") VALUES (".$values.")";

echo $query;

// returns

INSERT INTO table (sounds,sound,big) VALUES ('one','two','blue')

Upvotes: 0

Slavko
Slavko

Reputation: 148

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

Upvotes: 5

bug
bug

Reputation: 342

Try serialize() before the INSERT and unserialize() to get the array after a SELECT.

You need only one field to insert all the data.

https://www.php.net/manual/fr/function.serialize.php
https://www.php.net/manual/fr/function.unserialize.php

Upvotes: 0

dsimer
dsimer

Reputation: 115

Using PHP:

Getting your values into an array as $key => $value depends on the situation, but manually it would happen like so:

$array = array(`a` => '$1',`b` => '$2', ...and so on); //I am assuming that $ is not a variable indicator since it is inside single quotes.

There are a variety of array functions that can help you if you have existing arrays that you would rather manipulate to create the final array.

Once you have it, however:

$query = 'INSTERT INTO `first_page_data` (';
foreach ($array as $key => $value) {
    $query .= '`' . $key . '`';
}
$query .= ') VALUES (';
foreach ($array as $value) {
    $query .= '`' . $value . '`';
}
$query .= ')';

The code runs a foreach on the array twice, once to get the key and append it to the appropriate part of the string, and the other to add the corresponding values.

Upvotes: 0

Mahmoud.Eskandari
Mahmoud.Eskandari

Reputation: 1478

//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!

Upvotes: 3

Related Questions