How can we Interlace the Array with php?

Question

I am trying to build an SQL statement like this:

INSERT INTO mytable(`People`, `Places`, `Ideas`)
       VALUES ('40', '15',  null),
              (null, '5',   '10'),
              ('10',  null, '11');

We have three arrays with following values....

$people = array('45','null','10');
$places = array('15','5','null');
$ideas = array('null','11');

How can I build the corresponding SQL Query from my array?

You'd better use PDO or something similar. `mysql_query()` is deprecated. — MightyPork, Aug 01 '13 at 16:34
Noway. Unless you have NULL values instead of strings 'null' in your array — Your Common Sense, Aug 01 '13 at 16:35
possible duplicate of [Multiple mysql INSERT statements in one query php](http://stackoverflow.com/questions/1307618/multiple-mysql-insert-statements-in-one-query-php) — Brad, Aug 01 '13 at 16:35
You can go through the arrays with a loop, and build the query string. But can be tricky. — MightyPork, Aug 01 '13 at 16:36
Questions asking for code must **demonstrate a minimal understanding of the problem being solved**. Include attempted solutions, why they didn't work, and the *expected* results. See also: [Stack Overflow question checklist](http://meta.stackexchange.com/questions/156810/stack-overflow-question-checklist) — Madara's Ghost, Aug 01 '13 at 16:36
@MadaraUchiha I'd think the "expected result" is the query string. — MightyPork, Aug 01 '13 at 16:38
@MightyPork: That's fine, but there are no attempted solutions. (This text is directly copied from the Off-topic close reason. You'll see it when you get to 3000 rep :) — Madara's Ghost, Aug 01 '13 at 16:38
@MadaraUchiha Oh god, just looked at your rep. It's intimidating! — MightyPork, Aug 01 '13 at 16:40
You haven't explained the problem. Your arrays cannot machinatically create the query string specified (your second array has only two values, you appear to want the machine to 'know' that the second array value should go in the third SQL row). — Glitch Desire, Aug 01 '13 at 16:41

score 0 · Accepted Answer · edited May 23 '17 at 12:20

implode makes this easy:

$query = 'INSERT INTO mytable(`People`, `Places`, `Ideas`) VALUES ';

$value_arrs = array($people, $places, $ideas);
$value_arrs_size = count($value_arrs);

for ($i = 0; $i < $value_arrs_size; ++$i) {
    $query .= '(' . implode(', ', $value_arrays[$i]) . ')';

    if ($i < $value_arrs_size - 1) {
        $query .= ', ';
    }
}

This will only work as long as your values are all numbers or null. You'll need to add quotes around any strings you may want in your SQL statements.

Also, if this is user supplied input, you have to sanitize to prevent SQL Injection.

How can we Interlace the Array with php?

1 Answers1