php implode huge array

Question

I have a huge PHP array of the form:

Array
(
    [0] => ('A', 'B', 'C', 'D', 'E', 'F')
    [1] => ('G', 'H', 'I', 'J', 'K', 'L')
    [2] => ('M', 'N', 'O', 'P', 'Q', 'R')
    ....
    [30000] => ('S', 'T', 'U', 'V', 'W', 'X')
)

As you can see, my array contains 30,000 entries. I'm trying to use the implode() function to convert my array to a string of the form:

$values = ('A', 'B', 'C', 'D', 'E', 'F'), ('G', 'H', 'I', 'J', 'K', 'L'), ('M', 'N', 'O', 'P', 'Q', 'R'), ('S', 'T', 'U', 'V', 'W', 'X')

To be able to do a MySQL INSERT query. So in order words, I'm trying to insert 30,000 entries into a database table in one shot (without doing it in a loop or something) like:

INSERT INTO table_name VALUES $values;

The problem I'm having is that the implode function doesn't seem to implode my gigantic array. When I try to debug the string to see if my array has been imploded, I get empty string or something.

Does anybody know how I can fix this or if there is a better way to do this? Again, I don't want to query my database 30,000 times, I just want to do the insert at once.

Thank you

Is `('A', 'B', 'C', 'D', 'E', 'F')` stored in your array as a string or as an array? — gen_Eric, Aug 29 '12 at 18:41
Have you tried to use the same code but with a more managable-sized array? — Explosion Pills, Aug 29 '12 at 18:41
with **=> ('A','B'...)** you tell the array that comes a string A and a string B that means not, it comes as a whole string like => " ('A','B', ...) " . And don't forget all " ' " are gone. f.e. $array = array('lastname', 'email', 'phone'); $comma_separated = implode(",", $array); echo $comma_separated; // == "lastname,email,phone" all ' are gone. — moskito-x, Aug 29 '12 at 19:24

score 3 · Answer 1 · answered Aug 29 '12 at 18:41

3

implode doesn't work on multidimensional arrays. The values at each of your indices should be enclosed in double-quotes, so that they're interpreted as strings:

Array
(
    [0] => "('A', 'B', 'C', 'D', 'E', 'F')"
    [1] => "('G', 'H', 'I', 'J', 'K', 'L')"
    [2] => "('M', 'N', 'O', 'P', 'Q', 'R')"
    ....
    [30000] => "('S', 'T', 'U', 'V', 'W', 'X')"
)

Once you're dealing with a one-dimensional array, implode() will work.

answered Aug 29 '12 at 18:41

Matt

6,993
4
29
50

When I call gettype on ('A', 'B', 'C', 'D', 'E', 'F'), it says . Therefore ('A', 'B', 'C', 'D', 'E', 'F') is strored as a string, not an array – user765368 Aug 29 '12 at 18:54
Hmm...how are you using `implode()`? – Matt Aug 29 '12 at 18:56
1

Actually the code works, I just found out that was a Google Chrome problem, for some reason Chrome did not show me any output, but when I switched to Firefox, I got my output – user765368 Aug 29 '12 at 19:01

score 3 · Answer 2 · answered Aug 29 '12 at 18:43

To do it with PHP, this will handle the multidimensional array you have given:

foreach($array as $arr){
    $values[] = "('".implode("','", $arr)."')";
}

$values = implode(",", $values);
INSERT INTO table_name VALUES $values;

I would suggest you use transactions. Read up here for MySQLi or PDO

score 0 · Answer 3 · answered Aug 29 '12 at 18:43

0

Why don't you use a transaction (assuming you are using MySQL)? That way you can build up your query then submit it in one go?

Look at http://dev.mysql.com/doc/refman/5.0/en/commit.html

answered Aug 29 '12 at 18:43

dcbarans

482
7
14

OP clearly stated that `implode` is not functioning the way he thinks it does. The issue has nothing to do with the large insert. – Matt Aug 29 '12 at 18:44
@Matt OP also asked if there was a better way of doing this, and that he didn't want to query the DB 30k times. Only offering an alternative solution – dcbarans Aug 29 '12 at 18:55

score 0 · Answer 4 · answered Aug 29 '12 at 18:45

You're not joining strings, you're imploding arrays. If your data structure was proper strings, it'd dump more like

[0] => "(a,b,c)",
       ^-------^---
[1] => "(d,e,f)", 
       ^-------^--0
etc...

Note the quotes I've highlighted within. You'd first have to implode each of those sub-arrays individually, then implode the parent array,e .g.

foreach ($yourarr as $key => $subarr) {
    $yourarr[$key] = "('" . implode("','", $subarr) . "')";
}
$bigstring = implode($yourarr);

Plus, this also depends on the individual values in your child arrays not containing SQL metacharcters, like '. Be very very careful doing this sort of thing, because you can inject yourself and kill the insert query.

score 0 · Answer 5 · answered Aug 29 '12 at 18:45

0

Quick fix:

$rows = array();

foreach($originalArray as $row){

   $rows[] = '(' . implode(',' $row) . ')';

}

$finalString = implode(',', $rows);

Though I would split the query in chunks of let's say 1000 rows.

answered Aug 29 '12 at 18:45

moonwave99

21,957
3
43
64

score 0 · Answer 6 · answered Aug 29 '12 at 18:45

0

Implode isn't the job for this, you want to look at using a prepared SQL statement, then iterate through the array, executing the statement for each array.

http://php.net/manual/en/mysqli.prepare.php

foreach($your_array as $entry) {
    // execute the prepared sql statement here

}

answered Aug 29 '12 at 18:45

j5Dev

2,022
14
18

It's a mistake to loop through queries. You should just use one query to do it all. Think about server performance to execute 30000 queries – João Dias Aug 29 '12 at 18:46

php implode huge array

6 Answers6