Insert array into MySQL database with PHP

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

Let's not forget the most important thing to learn out of a question like this: SQL Injection.

Use PDO and prepared statements.

Click here for a tutorial on PDO.

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.

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"
);
?>

You have 2 ways of doing it:

• You can create a table (or multiple tables linked together) with a field for each key of your array, and insert into each field the corresponding value of your array. This is the most common way
• You can just have a table with one field and put in here your array serialized. I do not recommend you do do that, but it is useful if you don't want a complex database schema.

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.

Insert array data into mysql php

I Have array data.. I want post that data in database

1: this is my array data:

stdClass Object
(
    [questions] => Array
        (
            [0] => stdClass Object
                (
                    [question_id] => 54
                    [question] => Which%20of%20the%20following%20is%20a%20rational%20number%20(s)%3F%3Cbr%20%2F%3E%0D%0A%3Cbr%20%2F%3E
                    [option_1] => %3Cimg%20align%3D%22middle%22%20%20%20src%3D%22formula%2F54%2F010779c34ce28fee25778247e127b82d.png%22%20alt%3D%22%22%20%2F%3E%3Cspan%20class%3D%22Apple-tab-span%22%20style%3D%22white-space%3A%20pre%3B%20%22%3E%09%3C%2Fspan%3E
                    [option_2] => %26nbsp%3B%3Cimg%20align%3D%22middle%22%20%20%20src%3D%22formula%2F54%2F3af35a16c371ffaaf9ea6891fb732478.png%22%20alt%3D%22%22%20%2F%3E
                    [option_3] => %26nbsp%3B%3Cimg%20align%3D%22middle%22%20%20%20src%3D%22formula%2F54%2F4a57d5766a79f0ddf659d63c7443982b.png%22%20alt%3D%22%22%20%2F%3E
                    [option_4] => %26nbsp%3BAll%20the%20above%26nbsp%3B
                    [iscorrect] => yes
                    [answerGiven] => D
                    [marksobtain] => 2
                    [timetaken] => 3
                    [difficulty_levelval] => 2
                )


   [1] => stdClass Object
                (
                    [question_id] => 58
                    [question] => %3Cdiv%3EIf%20A%20%26nbsp%3B%3A%20Every%20whole%20number%20is%20a%20natural%20number%20and%3C%2Fdiv%3E%0D%0A%3Cdiv%3E%26nbsp%3B%20%26nbsp%3BR%20%3A%200%20is%20not%20a%20natural%20number%2C%3C%2Fdiv%3E%0D%0A%3Cdiv%3EThen%20which%20of%20the%20following%20statement%20is%20true%3F%3C%2Fdiv%3E
                    [option_1] => %26nbsp%3BA%20is%20False%20and%20R%20is%20true.
                    [option_2] => A%20is%20True%20and%20R%20is%20the%20correct%20explanation%20of%20A
                    [option_3] => %26nbsp%3BA%20is%20True%20and%20R%20is%20false
                    [option_4] => %26nbsp%3BBoth%20A%20and%20R%20are%20True
                    [iscorrect] => no
                    [answerGiven] => D
                    [marksobtain] => 0
                    [timetaken] => 2
                    [difficulty_levelval] => 2
                )

        )

)

code used i used to insert that data:

Code ::

<?php 
 //require_once("config_new2012.php");

require("codelibrary/fb/facebook.php");
include("codelibrary/inc/variables.php");
include_once(INC."functions.php");
include_once(CLASSES."frontuser_class.php");
include_once(CLASSES."testdetails_class.php");



$data = file_get_contents('php://input');
$arr_data = explode("=",$data);
$final_data = urldecode($arr_data[1]);
$final_data2 = json_decode($final_data);


 //print_r ($final_data2);



 if(is_array($final_data2)){
echo 'i am in array ';
    $sql = "INSERT INTO p_user_test_details(question_id, question, option_1, option_2, option_3, option_4,iscorrect,answerGiven,marksobtain,timetaken,difficulty_levelval) values ";

    $valuesArr = array();
    foreach($final_data2 as $row){

        $question_id = (int) $row['question_id'];
        $question = mysql_real_escape_string( $row['question'] );
        $option_1 = mysql_real_escape_string( $row['option_1'] );
    $option_2 = mysql_real_escape_string( $row['option_2'] );
    $option_3 = mysql_real_escape_string( $row['option_3'] );
    $option_4 = mysql_real_escape_string( $row['option_4'] );
    $iscorrect = mysql_real_escape_string( $row['iscorrect'] );
    $answerGiven = mysql_real_escape_string( $row['answerGiven'] );
        $marksobtain = mysql_real_escape_string( $row['marksobtain'] );
        $timetaken = mysql_real_escape_string( $row['timetaken'] );
        $difficulty_levelval = mysql_real_escape_string( $row['difficulty_levelval'] );
        $valuesArr[] = "('$question_id', '$question', '$option_1','$option_2','$option_3','$option_4','$iscorrect','$answerGiven','$marksobtain','$timetaken','$difficulty_levelval')";
    }

    $sql .= implode(',', $valuesArr);

    mysql_query($sql) or exit(mysql_error()); 
}
else{

echo 'no one is there ';
}