PHP: Iterate over multiple arrays and build SQL INSERT query

Question

I have PHP code that gets multiple arrays:

<?php
$checkKey = $_POST['key'];

if ($key === $checkKey)
{

  $a = array_values($_POST['a']);
  $b = array_values($_POST['b']);
  $c = array_values($_POST['c']);
  $d = array_values($_POST['d']);

  if ( (count($a) !== count($b)) || (count($a) !== count($c)) || (count($a) !== count($d)) )
  {
    die ('Not enough parameters!');
  }
  else
  {

  }


}
?>

I want to iterate over all the arrays an build a SQL INSERT query like this:

INSERT INTO xyz (a,b,c,d) VALUES (1,2,3,4), (4, 5, 6, 7);

Where the values are stored in each array (i.e. in this example a contains the values 1 and 4, b the values 2 and 5, etc.)

How can I achieve that?

Sidenote (a look ahead): If and when you do get someone to give you a working answer, do remember that key and keys are MySQL reserved words. — Funk Forty Niner
– Funk Forty Niner, Commented May 12, 2014 at 13:42
I presume that $key is defined somewhere? You might want to check that $a, $b, $c, and $d are set (isset()) before using, unless you can guarantee that all are present. Why are you using "not-equivalent" !== instead of "not-equal" != when comparing the count()'s? SQL does have an array type -- are you using that? — Phil Perry
– Phil Perry, Commented May 12, 2014 at 13:46
It would be much simpler and cleaner using abstract models or prepared statements, or some kind of query builder. — Daniel W.
– Daniel W., Commented May 12, 2014 at 13:46

complex857 · Accepted Answer · 2014-05-12 13:43:30Z

1

Just use a for() loop to iterate over the indexes from 0 to count($a) (you could use any of the arrays, they checked to be the same size).

$sql = 'insert into xyz (a, b, c, d) values ';
for ($i = 0, $l = count($a); $i < $l; ++$i) {
     $sql .= "('".
         // it's really important to escape the input!
         mysqli_real_escape_string($link, $a[$i]).','.
         mysqli_real_escape_string($link, $b[$i]).','.
         mysqli_real_escape_string($link, $c[$i]).','.
         mysqli_real_escape_string($link, $d[$i]).
     "'), ";
}
$sql = substr($sql, 0, -2); // trim down the last ', '

answered May 12, 2014 at 13:43

complex857

20.9k6 gold badges54 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

laurent · Accepted Answer · 2014-05-12 13:47:08Z

1

use this:

function transpose($array) {
    array_unshift($array, null);
    return call_user_func_array('array_map', $array);
}
//example
$a=array(1,3);
$b=array(2,4);
$c=array(5,4);
$d=array(10,12);

$r= transpose(array($a,$b,$c,$d));
$sql='INSERT INTO xyz (a,b,c,d) VALUES ';
foreach($r as $values){
$sql.='('.implode(',',$values).'),';
}
$sql=rtrim($sql,',');
echo $sql;

answered May 12, 2014 at 13:47

laurent

4283 silver badges8 bronze badges

Comments

Thomas · Accepted Answer · 2014-05-12 14:00:42Z

0

I think there is no build-in php function so you can just do this:

<?PHP

$count = count($a);
$sqlout = 'INSERT INTO xyz (a,b,c,d) VALUES ';

for($i=0; $i<$count; $i++)
{
   $sqlout .= ( $i>0 ? ',' : '') . '('. $a[$i] .', '. $b[$i] .', '. $c[$i] .', '. $d[$i] .') ';
}


?>

and make sure to escape your input to avoid SQL Injection!

edited May 12, 2014 at 14:00

answered May 12, 2014 at 13:43

Thomas

2,4631 gold badge19 silver badges19 bronze badges

1 Comment

John Bupit Over a year ago

You're missing the , between the rows.

Kickstart · Accepted Answer · 2014-05-12 15:26:41Z

0

Simple loop around

<?php
$checkKey = $_POST['key'];

if ($key === $checkKey)
{

    $a = array_values($_POST['a']);
    $b = array_values($_POST['b']);
    $c = array_values($_POST['c']);
    $d = array_values($_POST['d']);

    if ( (count($a) !== count($b)) || (count($a) !== count($c)) || (count($a) !== count($d)) )
    {
        die ('Not enough parameters!');
    }
    else
    {
        $sql_array = array();
        foreach($a AS $key=>$values)
        {
            $sql_array[] = "(".(int)$a[$key].",".(int)$b[$key].",".(int)$c[$key].",".(int)$d[$key].")";
        }
        if (count($sql_array) > 0)
        {
            $sql = "INSERT INTO xyz (a,b,c,d) VALUES ".implode(",", $sql_array);
            // Execute it here
        }
    }


}
?>

edited May 12, 2014 at 15:26

answered May 12, 2014 at 13:47

Kickstart

21.5k2 gold badges26 silver badges33 bronze badges

Collectives™ on Stack Overflow

PHP: Iterate over multiple arrays and build SQL INSERT query

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related