php array mysql insert

Question

This foreach loop only inserts one (the last value in the array) record, the array contains about 20 values. Can someone please help I can;t seem to work this one out

foreach ($array as $key => $value) {


 $sql = " INSERT INTO table3 (date, header, header2) VALUES (CURRENT_DATE(), '".$key."', '".$value."') " ;

 }

You are overriding the $sql variable on every loop. Try using .= — IanS
– IanS, Commented Oct 18, 2016 at 12:52
because u r executing your query outside the loop, you need to execute query inside the loop — devpro
– devpro, Commented Oct 18, 2016 at 12:52
@IanS In that case, it will require a semicolon at the end of each mysql statement — Bence Gajdán
– Bence Gajdán, Commented Oct 18, 2016 at 12:55

shubham715 · Accepted Answer · 2016-10-18 12:53:46Z

2

Try this

foreach ($array as $key => $value) {


 $sql = " INSERT INTO table3 (date, header, header2) VALUES (CURRENT_DATE(), '".$key."', '".$value."') " ;
 $sqlquery = mysqli_query($conn, $sql); //$conn is your connection variable 
 }

answered Oct 18, 2016 at 12:53

shubham715

3,3001 gold badge20 silver badges27 bronze badges

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

5 Comments

devpro Over a year ago

but you can optimize it, you can use one query instead of multiple @spyda46

shubham715 Over a year ago

don't forget to accept answer if its helpful for you. :)

shubham715 Over a year ago

its an example of that what he miss in his code. :)

spyda46 Over a year ago

the only problem I am still having is the end value, is inserted twice - any ideas ?

jogoe Over a year ago

@spyda46 remember to remove your sql-execution after the loop. Thats probably why it inserts the last one twice. :)

jogoe · Accepted Answer · 2016-10-18 12:52:28Z

2

Thats because you only define $sql, which is just a string, but don't actually execute the sql-statement.

You have to move the execution of your sql-statement into the loop for this to work.

e.g.:

foreach ($array as $key => $value) {

 $sql = " INSERT INTO table3 (date, header, header2) VALUES (CURRENT_DATE(), '".$key."', '".$value."') " ;
 $mysqli->query($sql);

 }

answered Oct 18, 2016 at 12:52

jogoe

3347 silver badges18 bronze badges

Comments

himeshc_IB · Accepted Answer · 2016-10-18 13:45:33Z

2

Try this optimized way as it will reduce your database operation and boost execution speed..

$sql = " INSERT INTO table3 (date, header, header2) VALUES ";
foreach ($array as $key => $value) {
 $sql .= "(`".CURRENT_DATE()"`, `".$key."`, `".$value."`),";
}
$sql = trim($sql ,',');
$mysqli->query($conn, $sql);

edited Oct 18, 2016 at 13:45

answered Oct 18, 2016 at 12:58

himeshc_IB

8634 silver badges10 bronze badges

3 Comments

devpro Over a year ago

best way, execute query at once.

spyda46 Over a year ago

this gives me -- PHP Parse error: syntax error, unexpected T_FOREACH

spyda46 Over a year ago

try to get it to work, but the above gives -- PHP Parse error: syntax error, unexpected T_CONSTANT_ENCAPSED_STRING

Collectives™ on Stack Overflow

php array mysql insert

3 Answers 3

5 Comments

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

3 Comments

Related