Inserting multiple rows into the sql database in php

Question

How I can insert multiple rows in sql statement in the php. It seems the array does not work. Some of my variables are in array and some are fixed in the loop.

$sql = array();
    for ($i=0; $i< count($datesArray); $i++){
        array_push($sql, "INSERT INTO courseSchedule (isc, date, startTime, endTime, sessionTitle, sessionDescription) VALUES ('$isc', '$datesArray[$i]', '$TimesArrayStart[$i]', '$TimesArrayEnd[$i]', '$TitlesArray[$i]', '$DescriptionsArray[$i]')");
    }
    $query = mysqli_query($db_conx, $sql);

I have changed the code something like this, but still no entry.

$sql = array();
for ($i=0; $i< count($datesArray); $i++){
    $sqlarray[] = '('.$isc.', '.$datesArray[$i].', '.$TimesArrayStart[$i].', '.$TimesArrayEnd[$i].', '.$TitlesArray[$i].', '.$DescriptionsArray[$i].')';
}
$sql = 'INSERT INTO courseSchedule (isc, date, startTime, endTime, sessionTitle, sessionDescription) VALUES'.implode(',', $sqlarray);
$query = mysqli_query($db_conx, $sql);

Using mysqli_multi_query also doesn't work:

 $sql   = "";
$count = count($datesArray);
for ($i = 0; $i < $count; $i++){
    $sql .= "INSERT INTO courseSchedule (isc, date, startTime, endTime, sessionTitle, sessionDescription) VALUES ('$isc', '$datesArray[$i]', '$TimesArrayStart[$i]', '$TimesArrayEnd[$i]', '$TitlesArray[$i]', '$DescriptionsArray[$i]')";
}
echo($sql);
$query = mysqli_multi_query($db_conx, $sql);

For mysql_multi_query, you need to add ';' at final of each query. By the way, my second solution is more efficient to execute. — fntneves
– fntneves, Commented Jul 1, 2014 at 18:15

fntneves · Accepted Answer · 2014-07-01 18:20:26Z

2

Concatenate queries and use mysqli_multi_query(). Note that you need to end SQL Statement with ;

Take a look http://php.net/manual/en/mysqli.multi-query.php

Or, for INSERT, you can build you query like this:

INSERT INTO example
  (example_id, name, value, other_value)
VALUES
  (100, 'Name 1', 'Value 1', 'Other 1'),
  (101, 'Name 2', 'Value 2', 'Other 2'),
  (102, 'Name 3', 'Value 3', 'Other 3'),
  (103, 'Name 4', 'Value 4', 'Other 4');

edited Jul 1, 2014 at 18:20

answered Jul 1, 2014 at 15:34

fntneves

4143 silver badges6 bronze badges

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

Comments

MrMarlow · Accepted Answer · 2014-07-01 15:52:21Z

0

Something along these lines should work - in fact, this should work.

Untested, but quickly wrote it from my work desk, in stealth mode.

$sql   = 'INSERT INTO courseSchedule (isc, date, startTime, endTime, sessionTitle, sessionDescription) VALUES ';
$count = count($datesArray);
for ($i = 0; $i < $count); $i++)
{
    $sql .= "('$isc', '$datesArray[$i]', '$TimesArrayStart[$i]', '$TimesArrayEnd[$i]', '$TitlesArray[$i]', '$DescriptionsArray[$i]')" . (($i + 1) == $count ? '' : ',');
}
$query = mysqli_query($db_conx, $sql);

answered Jul 1, 2014 at 15:52

MrMarlow

8564 silver badges17 bronze badges

5 Comments

Ahad Over a year ago

what is this statement st the end of $sql for? (($i + 1) == $count ? '' : ',' =====> I got it. I won't put a , at the end.

Ahad Over a year ago

I tried this code. I echoed the $sql and it seems it is okay but nothing inserts into the table. With one row it works, but multiple rows doesn't.

user3741598 Over a year ago

Have you verified the id you're connecting to the MySQL db has the correct GRANT access to write?

Ahad Over a year ago

I am not sure what your point is. But I checked without a for loop just insert one row, everything is okay. But multiple values in the sql statement does not work to insert multiple rows. It's strange here. Everything and the code seems okay but can't catch it what is wrong.

Ahad Over a year ago

Problem solved. There was a restriction in the table with fk which did not allow to all multiple rows with the same id of a column.

user3741598 · Accepted Answer · 2014-07-01 15:47:50Z

0

By process of elimination AND because you don't show us your table, since your PHP for syntax is correct and your INSERT is correct, it's obvious you don't have values for each value (that can't be NULL) in your INSERT. Maybe you should echo those values before the for to verify. Good luck.

answered Jul 1, 2014 at 15:47

user3741598

2951 silver badge12 bronze badges

1 Comment

Ahad Over a year ago

Thanks for the reply. I actually echo all variables and all are not null during the insert process.

Collectives™ on Stack Overflow

Inserting multiple rows into the sql database in php

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related