Insert array into MySQL database with PHP

insert array into one column mysql
how to insert multiple array values into database php
insert associative array into mysql php
how to store values to array from mysql database in php
insert array into mysql node js
insert array into mysql java
how to store array value in database using php
insert textbox array in php mysql database

I have the following array I want to store in my database...

$insData = array(
    'uid' => $fbme['id'],
    'first_name' => $fbme['first_name'],
    'last_name' => $fbme['last_name'],
    'email' => isset($fbme['email']) ? $fbme['email'] : '',
    'link' => $fbme['link'],
    'affiliations' => $networks,
    'birthday' => $info[0]['birthday_date'],
    'current_location' => isset($fbme['location']['name']) ? $fbme['location']['name'] : '',
    'education_history' => $education,
    'work' => $workInfo,
    'hometown_location' => isset($fbme['hometown']['name']) ? $fbme['hometown']['name'] : '',
    'interests' => $info[0]['interests'],
    'locale' => $info[0]['locale'],
    'movies' => $movies,
    'music' => $music,
    'political' => $info[0]['political'],
    'relationship_status' => $info[0]['relationship_status'],
    'sex' =>  isset($fbme['gender']) ? $fbme['gender'] : '',
    'tv' => $television,
    'status' => '0',
    'created' => $now,
    'updated' => $now,
);

I've tried searching google on how to do this and all I can find is information stating my array needs to be split, before inserting into the table. Is this correct? Sorry for the naivity, very new to php.

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 an sql statement. This can be done manually or by a library. The output should be an INSERT statement.

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

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.

Insert array into MySQL database with PHP, to insert all the values of the array into the database. Every time I try it I don't get any errors but the information is never entered into MySQL. I want to insert above PHP array into mysql database table.We have Two option to insert php array into MySQL using PHP. Using Repetitive Insert command on each row. Using Single Insert command by concatenating all array values into array.

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

Insert Array Into Database - PHP, Inserting An Array into a MySQL Database Table <?php; function mysql_insert_array($table, $data, $exclude = array()) {; $fields = $values  Method 1) Serialize Array and Save into MySQL. Now let's see about storing array in mysql with the above method. First we should convert the given array to string with serialize() function and then insert into database. Here's the php code to do it.

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

Inserting An Array into a MySQL Database Table · A Beautiful Site, You have to execute the SQL insert for each row. So just move the operation into the foreach loop: Hide Expand Copy Code. foreach ($tds as  This lets you insert data from a PHP array into your MySQL database table. It will insert multiple rows into the table, where each row will take values from one array element. Before inserting, you must prepare your data into an array consisting of records that you want to insert into the database.

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.

How to Enter PHP Array within MySQL Database, Sometimes, require to store Array to MySQL database and retrieve it. Insert record $sql = "INSERT INTO contents_arr(arr_serialize1  When a user clicks the submit button of the add record HTML form, in the example above, the form data is sent to 'insert.php' file. The 'insert.php' file connects to the MySQL database server, retrieves forms fields using the PHP $_REQUEST variables and finally execute the insert query to add the records.

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.

How do I insert PHP array into MYSQL database?, Since databases don't support array data types, there is no direct way to store them in db. But you can convert array as string and insert into mysql  Insert Data Into MySQL Using MySQLi and PDO. After a database and a table have been created, we can start adding data in them. Here are some syntax rules to follow: The SQL query must be quoted in PHP. String values inside the SQL query must be quoted. Numeric values must not be quoted. The word NULL must not be quoted.

How to Store Array in MySQL with PHP, Insert Multiple Records Into MySQL Using MySQLi and PDO. Multiple SQL statements must be executed with the mysqli_multi_query() function. The following  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).

Insert Array into MySQL Database using PHP, This lets you insert data from a PHP array into your MySQL database table. It will insert multiple rows into the table, where each row will take  I found your article "Simple Way to Convert HTML Table Data into PHP Array" and it happened to create an array beautifully with a project I was working on. I am trying to enter this array into my mysql db. Unfortunately without much success.

PHP MySQL Insert Multiple Records, [code]<?php $json _value = <some json source>; $array = json_decode($json_value,true); require_once 'db.php'; foreach($array as $item) { $insert_value = &quot;INSERT

Comments
  • Your first step is probably to define your table(s) structure; you can do this very easily in phpMyAdmin. Then do some research around inserting into tables - a web search on "PHP PDO insert example" should bring back hundreds of results. Finally give that a go, paste it into your question, and someone will offer pointers!
  • "my array needs to be split before inserting into the table" - that depends. Does your array above contain several tables/rows worth of information? If yes to the second, yes to the first.
  • Does a table/tables match the exact schema?
  • Note that escaping your values is a very important step. It prevents sql injection.
  • Also note that you may have to add single quotes in the $values string for it to work correctly. $values = implode("', '", $escaped_values); $sql = "INSERT INTO fbdata ($columns) VALUES ('$values')";
  • If you have some data that's just a single item, e.g. name, and then other data that comes as an array that you want to split up into columns e.g. favourite movies, can you do this (where columns/values are built according to the example in this answer: INSERT INTO table(name, $columns) VALUES ('my name', $values)
  • A Fully Working php code of this would be appreciated.
  • Don't use mysql_real_escape_string, rather you should use mysqli_real_escape_string !!!
  • Just for others who may look at this code. It took me a couple tries before I figured out that you have an extra > directly after $prep[':'.$k] =. If you remove the greater than sign, your code works.
  • i dont got it why you add ` : ` before key?