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] => [email protected]             [name] => Bob         )      [1] => Array         (             [R_ID] => 32             [email] => [email protected]             [name] => Dan         )      [2] => Array         (             [R_ID] => 32             [email] => [email protected]             [name] => Paul         )      [3] => Array         (             [R_ID] => 35             [email] => [email protected]             [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.

Answer 1

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());  } 

Answer 2

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.