Insert values into database from arrays

Question

My php looks like this:

for ($i = 0, $count = count($ingredientQTY); $i < $count; $i++) {

            $rows[] = array(
                'ingredientamount'         => $ingredientQTY[$i],
                'ingredientType' =>  $measurements[$i],
                'ingredientname'        => $ingredientNAME[$i]
            );
            print_r($rows[$i]);
}

which prints out an array like this:

Array ( 
[ingredientamount] => 34 
[ingredientType] => Centiliter 
[ingredientname] => CHicken ) 

Array ( 
[ingredientamount] => 26 
[ingredientType] => Grams 
[ingredientname] => Cheddar Cheese )

I have three fieldnames in my database called ingredientamount, ingredientType, and ingredientname, which correspond to the two arrays which are assigned to $rows[]. So, if I had the arrays working, my database would look like this:

ingredientamount => 34, ingredientType => Centiliter, ingredientname => Chicken
ingredientamount => 26, ingredientType => Grams, ingredientname => Cheddar Cheese

My question is: How can I use my code to take the $rows[] (which is a multidimensional array) and insert each row into a database like mine?

Thanks for all help!

Why $count = count($ingredientQTY); $i < $count; instead of $i < count($ingredientQTY); ? — hek2mgl
– hek2mgl, Commented Mar 27, 2013 at 18:10

Denis O. · Accepted Answer · 2013-03-27 18:17:59Z

1

Try this code:

<?php

$rows = array();
$rows[] = array('ingredientamount' => '34', 'ingredientType' => 'Centiliter', 'ingredientname' => 'CHicken' );
$rows[] = array('ingredientamount' => '26', 'ingredientType' => 'Grams', 'ingredientname' => 'Cheddar Cheese' );

$sql = "INSERT `myTableName` (`ingredientamount`,`ingredientType`,`ingredientname`) VALUES ";
$coma = '';
foreach ($rows as $oneRow) {
    $sql .= $coma."('".implode("','",$oneRow)."')";
    $coma = ', ';
}

$result = $db->query($sql);


?>

You'll get something like this in $sql after all
INSERT `myTableName` (`ingredientamount`,`ingredientType`,`ingredientname`) VALUES ('34','Centiliter','CHicken'), ('26','Grams','Cheddar Cheese')
It is just draft code with no errors checking or input control that is actually required, but it gives the idea of how it works. I believe you can add all the rest by yourself.

EDIT

thanks for the answer but the values of ingredientamount, ingredientType, and ingredientname are dynamic so I dont think this would work?

not a problem at all. just change this line
$sql = "INSERT `myTableName` (`ingredientamount`,`ingredientType`,`ingredientname`) VALUES ";
to this
$sql = "INSERT `myTableName` (`".implode('`,`',array_keys($rows[0]))."`) VALUES ";

edited Mar 27, 2013 at 18:17

answered Mar 27, 2013 at 18:12

Denis O.

1,85020 silver badges40 bronze badges

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

1 Comment

Denis O. Over a year ago

@Jake, welcome, but do not forget to make strong input control. Using this code in raw is very unsafe and makes your app vulnerable for the SQL injections. it is important. good luck.

Collectives™ on Stack Overflow

Insert values into database from arrays

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related