How to insert array of data into mysql using php

Question

Currently I have an Array that looks like the following when output thru print_r();

Array
(
    [0] => Array
        (
            [R_ID] => 32
            [email] => a@a.com
            [name] => Bob
        )

    [1] => Array
        (
            [R_ID] => 32
            [email] => b@b.com
            [name] => Dan
        )

    [2] => Array
        (
            [R_ID] => 32
            [email] => c@c.com
            [name] => Paul
        )

    [3] => Array
        (
            [R_ID] => 35
            [email] => d@d.com
            [name] => Mike
        )  
)

I would like to insert this data into one table with each element value belonging to its respective field.

Currently my php code looks like the following

if(is_array($EMailArr)){
    foreach($EMailArr as $R_ID => $email => $name){

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ('$R_ID', '$email', '$name')";
    mysql_query($sql) or exit(mysql_error()); 
    }
}

*Note : R_ID is NOT the primary key in this table.*

Can someone help me understand how I should approach this situation? Thank you for reading and your help!

Regards.

You should first stop using `mysql_` functions. Next, you should use prepared statements. [See this question](http://stackoverflow.com/questions/14860606/mysqli-inserting-multiple-rows-with-one-prepared-statement) on how to do it. — Kermit, Feb 21 '13 at 22:15

dxvargas · Accepted Answer · 2013-02-21T22:27:58.643

34

I would avoid to do a query for each entry.

if(is_array($EMailArr)){

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ";

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

        $R_ID = (int) $row['R_ID'];
        $email = mysql_real_escape_string( $row['email'] );
        $name = mysql_real_escape_string( $row['name'] );

        $valuesArr[] = "('$R_ID', '$email', '$name')";
    }

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

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

edited Feb 21 '13 at 22:27

answered Feb 21 '13 at 22:21

dxvargas

811
14
23

How to retrieve this? – Iman Marashi Feb 22 '16 at 18:31

score 9 · Answer 2 · answered Feb 21 '13 at 22:20

First of all you should stop using mysql_*. MySQL supports multiple inserting like

INSERT INTO example
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

You just have to build one string in your foreach loop which looks like that

$values = "(100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1'), (100, 'Name 1', 'Value 1', 'Other 1')";

and then insert it after the loop

$sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) VALUES ".$values;

Another way would be Prepared Statements, which are even more suited for your situation.

score 1 · Answer 3 · answered Feb 21 '13 at 22:14

1

if(is_array($EMailArr)){
    foreach($EMailArr as $key => $value){

    $R_ID = (int) $value['R_ID'];
    $email = mysql_real_escape_string( $value['email'] );
    $name = mysql_real_escape_string( $value['name'] );

    $sql = "INSERT INTO email_list (R_ID, EMAIL, NAME) values ('$R_ID', '$email', '$name')";
    mysql_query($sql) or exit(mysql_error()); 
    }
}

A better example solution with PDO:

 $q = $sql->prepare("INSERT INTO `email_list` 
                     SET `R_ID` = ?, `EMAIL` = ?, `NAME` = ?");

 foreach($EMailArr as $value){
   $q ->execute( array( $value['R_ID'], $value['email'], $value['name'] ));
 }

answered Feb 21 '13 at 22:14

Green Black

5,037
1
17
29

I would advise against multiple `INSERT` statements. – Kermit Feb 21 '13 at 22:15
This is just a quick and dirty solution to his problem. – Green Black Feb 21 '13 at 22:15
@John I get this error PHP Fatal error: Call to a member function prepare() on a non-object – BaconJuice Feb 21 '13 at 23:06
@baconjuice You have to make a PDO connection first. This is just an example. – Green Black Feb 21 '13 at 23:10

score 0 · Answer 4 · answered Jun 05 '18 at 11:00

I've a PHP library which helps to insert array into MySQL Database. By using this you can create update and delete. Your array key value should be same as the table column value. Just using a single line code for the create operation

DB::create($db, 'YOUR_TABLE_NAME', $dataArray);

where $db is your Database connection.

Similarly, You can use this for update and delete. Select operation will be available soon. Github link to download : https://github.com/pairavanvvl/crud

How to insert array of data into mysql using php

4 Answers4

Linked