current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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.

share|improve this question
1  
You should first stop using mysql_ functions. Next, you should use prepared statements. See this question on how to do it. –  Kermit Feb 21 '13 at 22:15

3 Answers 3

up vote 5 down vote accepted

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()); 
}
share|improve this answer
    
Thank you very much. –  BaconJuice Feb 22 '13 at 14:07
up vote 0 down vote 
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'] ));
 }
share|improve this answer
    
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. –  John 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. –  John Feb 21 '13 at 23:10
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.