Store array contents in database

Question

I have a php function getContactList():

$res = getContactList(trim($_POST['username']), trim($_POST['password']));

which returns this array:

$contactList[] = array('name' => $name, 'email' =>$email);

I need to store the contents of this array into a MySQL database. Somehow can i store the entire contents of the array in the database at one go ?? The number of array items will be more than 500 at each go , so i wanna avoid the usual looping practice and calling the "Insert" statement in the for loop as this will need a long time to execute.

Note: I need to store the results in separate columns - One for Name and another for Email. With 500 items in that array I need to have 500 rows inserted - one Name-Email pair per row.

Salman A · Accepted Answer · 2011-10-21T12:38:58.050

6

$values = array();
// the above array stores strings such as:
// ('username', 'user@domain.com')
// ('o\'brien', 'baaz@domain.com')
// etc
foreach($contactList as $i => $contact) {
    $values[] = sprintf(
        "('%s', '%s')",
        mysql_real_escape_string($contact['name'], $an_open_mysql_connection_identifier),
        mysql_real_escape_string($contact['email'], $an_open_mysql_connection_identifier)
    );
}
$query = sprintf(
    "INSERT INTO that_table(name, email) VALUES %s",
    implode(",", $values)
);

edited Oct 21 '11 at 12:38

answered Oct 21 '11 at 10:12

Salman A

262,204
82
430
521

1

Regarding string escaping: not "could", "should"! – middus Oct 21 '11 at 10:47
@middus: yes I was being lazy :) – Salman A Oct 21 '11 at 10:56
@Johan: I've suggested using `mysql_real_escape_string` in my comment already. – Salman A Oct 21 '11 at 12:21
1

Code does not run in comments, it runs in the body. Put the code where it will actually work, don't put unsafe and nonfunctional code in the body and the real solution in a comment. It makes no sense. – Johan Oct 21 '11 at 12:32
just executed a query and found that if one of the names in the insert query is ( John D' Souza ) MySQL gives error !! I think its the special character [ ' ] which is the cause ! Can u pls help !! – Sandy505 Oct 21 '11 at 16:11
Single quotes in data could cause a problem but not in this case since the example uses [mysql_real_escape_string()](http://php.net/manual/en/function.mysql-real-escape-string.php). If you are using that function correctly you should not get mismatching or unescaped `'`. – Salman A Oct 22 '11 at 10:18
1

Yet another thing you don't have to worry about when you're using PDO's prepared statements ;-). – middus Oct 23 '11 at 11:24

score 5 · Answer 2 · answered Oct 21 '11 at 10:53

5

If you are trying to insert many rows, i.e. run the same query many times, use a prepared statement. Using PDO, you can do this:

// prepare statement
$stmt = $dbh->prepare("INSERT INTO contacts (email, name) VALUES (:email, :name);");
// execute it a few times
foreach($contactList as $contact) {
    $stmt->bindValue(':email', $contact['email'], PDO::PARAM_STR);
    $stmt->bindValue(':name', $contact['name'], PDO::PARAM_STR);
    $stmt->execute();
}

PDO will also take care of proper string escaping.

answered Oct 21 '11 at 10:53

middus

9,103
1
31
33

you execute the query each time for each row, where as Salman do it only once for all rows – Irishka Oct 21 '11 at 11:05
1

That's the point. I execute the same query as many times as needed. However, the statement is prepared and uses placeholders that are substituted for each execution. See the link to the php manual in my answer for more on prepared statements. – middus Oct 21 '11 at 11:06
1

but OP asks "so i wanna avoid the usual looping practice and calling the "Insert" statement in the for loop" – Irishka Oct 21 '11 at 11:10
2

Yes, he probably wants to avoid it because he's afraid of the performance implications of sending 500 different queries. Using prepared statements, this is not an issue because the query is only compiled once (see link to php manual). In my opinion, this screams "use case for prepared statements"! – middus Oct 21 '11 at 11:12

score 2 · Answer 3 · answered Oct 21 '11 at 10:06

2

use the standard php serialize function

$serData_str = serialize($contactList);

then save it in the DB

after reading your data from DB simply unserialize it

$myData_arr = unserialize($serContactList);

answered Oct 21 '11 at 10:06

Irishka

1,136
6
12

1

Probably better to json_encode the data as other languages can then interpret it. Using serialize means you're stuck with PHP and in the long run this might not be very useful. – Flukey Oct 21 '11 at 10:10
see here http://stackoverflow.com/questions/804045/preferred-method-to-store-php-arrays-json-encode-vs-serialize – Irishka Oct 21 '11 at 10:15
1

Right but I don't think OP stated anywhere in his question that he wants to store the entire list of contacts in *one column*. – Salman A Oct 21 '11 at 10:15
Indeed, I see your point Salman A however the quesiton is open to interpretation. He says `he number of array items will be more than 500 at each go` I doubt that he'll have more than 500 colums in the table therefore it's safe to assume that he wants to store it in one column. However, we need more information from OP. :-) – Flukey Oct 21 '11 at 10:19
1

I need to store the results in separate columns - One for Name and another for Email. What i meant by 500 is that the array will have 500 items in count and i need to have 500 rows inserted - for inserting 'Name' and 'Email' in each of the rows – Sandy505 Oct 21 '11 at 10:25

score 0 · Answer 4 · answered Oct 21 '11 at 10:03

In the loop you can create an insert statement and execute it after the loop. the insert statement will include all content.

The statement would look like

INSERT INTO employee (name) VALUES ('Arrayelement[1]'),('Arrayelement[2]'), ('Arrayelement[3]'),('Arrayelement[4]');

Store array contents in database

4 Answers4