Insert array into MySQL database with PHP

Solution 1:

You can not insert an array directly to MySQL as MySQL doesn't understand PHP data types. MySQL only understands SQL. So to insert an array into a MySQL database you have to convert it to a SQL statement. This can be done manually or by a library. The output should be an INSERT statement.

Update for PHP7

Since PHP 5.5 mysql_real_escape_string has been deprecated and as of PHP7 it has been removed. See: php.net's documentation on the new procedure.

Original answer:

Here is a standard MySQL insert statement.

INSERT INTO TABLE1(COLUMN1, COLUMN2, ....) VALUES (VALUE1, VALUE2..)

If you have a table with name fbdata with the columns which are presented in the keys of your array you can insert with this small snippet. Here is how your array is converted to this statement.

$columns = implode(", ",array_keys($insData));
$escaped_values = array_map('mysql_real_escape_string', array_values($insData));
$values  = implode(", ", $escaped_values);
$sql = "INSERT INTO `fbdata`($columns) VALUES ($values)";

Solution 2:

There are a number of different ways... I will give you an example of one using prepared statements:

$prep = array();
foreach($insData as $k => $v ) {
    $prep[':'.$k] = $v;
}
$sth = $db->prepare("INSERT INTO table ( " . implode(', ',array_keys($insData)) . ") VALUES (" . implode(', ',array_keys($prep)) . ")");
$res = $sth->execute($prep);

I'm cheating here and assuming the keys in your first array are the column names in the SQL table. I'm also assuming you have PDO available. More can be found at http://php.net/manual/en/book.pdo.php

Solution 3:

Here is my full solution to this based on the accepted answer.

Usage example:

include("./assets/php/db.php");
$data = array('field1' => 'data1', 'field2'=> 'data2');
insertArr("databaseName.tableName", $data);

db.php

<?PHP
/**
 * Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php
 *
 * @example
 *    $db = new database(); // Initiate a new database connection
 *    mysql_close($db->get_link());
 */
class database{
    protected $databaseLink;
    function __construct(){
        include "dbSettings.php";
        $this->database = $dbInfo['host'];
        $this->mysql_user = $dbInfo['user'];
        $this->mysql_pass = $dbInfo['pass'];
        $this->openConnection();
        return $this->get_link();
    }
    function openConnection(){
    $this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass);
    }

    function get_link(){
    return $this->databaseLink;
    }
}

/**
 * Insert an associative array into a MySQL database
 *
 * @example
 *    $data = array('field1' => 'data1', 'field2'=> 'data2');
 *    insertArr("databaseName.tableName", $data);
 */
function insertArr($tableName, $insData){
    $db = new database();
    $columns = implode(", ",array_keys($insData));
    $escaped_values = array_map('mysql_real_escape_string', array_values($insData));
    foreach ($escaped_values as $idx=>$data) $escaped_values[$idx] = "'".$data."'";
    $values  = implode(", ", $escaped_values);
    $query = "INSERT INTO $tableName ($columns) VALUES ($values)";
    mysql_query($query) or die(mysql_error());
    mysql_close($db->get_link());
}
?>

dbSettings.php

<?PHP
$dbInfo = array(
    'host'      => "localhost",
    'user'      => "root",
    'pass'      => "password"
);
?>

Solution 4:

Personally I'd json_encode the array (taking into account any escaping etc needed) and bung the entire lot into an appropriately sized text/blob field.

It makes it very easy to store "unstructured" data but a real PITA to search/index on with any grace.

A simple json_decode will "explode" the data back into an array for you.

Solution 5:

Serialize the array and you'll have a text on your database column, that will solve the problem.

I do that, for instance to save objects, that way I can retrieve them easily.