Inserting multiple rows into MySql with one INSERT statement using PHP implode function

Question

I'm trying to insert multiple rows into MySql with only one INSERT INTO statement using the implode function. It was found from here.

My sample code goes below.

$sql[]=array();
$len=count($colour_id);
for($i=0;$i<$len;$i++)
{
     $sql[]='('.$colour_id[$i].', '.$_POST['prod_id'].')';
}

$l=count($sql);
foreach($sql as $temp)
{
     echo $temp;
}

echo 'insert into product_colour (colour_id, prod_id)values '.implode(',', $sql);

The above code simply initializes the $sql array and the foreach loop iterates over the array and displays the content of the array as follows.

Array(1, 1)(2, 1)(3, 1)

but while echoing the last statement (insert statement), it shows the following error.

Notice: Array to string conversion in C:\wamp\www\wagafashion\ProductColour.php on line 70
insert into product_colour (colour_id, prod_id)values Array,(1, 1),(2, 1),(3, 1)

(line no 70 means the last line in the above code snippet). What changes should be made so that I can insert the values stored in the array into MySql database?

how about using instead of an array just a string named $sql and do $sql .= '('.$colour_id[$i].', '.$_POST['prod_id'].'), '; Then u will not get the "Array,(1, 1),(2, 1),(3, 1)" thing and only "(1, 1),(2, 1),(3, 1) " btw: u have to delete your last comma, if u use this way. — user945967
– user945967, Commented Mar 20, 2012 at 22:40
Silly mistake was made because I'm not much familiar with PHP. I'm from Java background. — Bhavesh
– Bhavesh, Commented Mar 20, 2012 at 23:29
Yes conversation from one to another language is always a bit tricky. But in PHP you have to read the Errors-Codes really carefully, because they give you good hints, whats wrong with your code. :) — user945967
– user945967, Commented Mar 21, 2012 at 9:08

Frederick Behrends · Accepted Answer · 2012-03-20 22:41:53Z

8

your code should be:

$sql=array();
$len=count($colour_id);
for($i=0;$i<$len;$i++)
{
     $sql[]='('.$colour_id[$i].', '.$_POST['prod_id'].')';
}

$l=count($sql);
foreach($sql as $temp)
{
     echo $temp;
}

echo 'insert into product_colour (colour_id, prod_id)values '.implode(',', $sql);

Take a look at the first line.

answered Mar 20, 2012 at 22:41

Frederick Behrends

3,09526 silver badges47 bronze badges

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

3 Comments

xkeshav Over a year ago

what is the use of $temp here?

ianace Over a year ago

inst the ` $sql[]='('.$colour_id[$i].', '.$_POST['prod_id'].')';` supposed to be $sql[$i] ?

Frederick Behrends Over a year ago

$sql[] appends a new field to the end of the array. $sql[$i] adds the field $i to the array, or if the field already exists, it overrides the data in $sql[$i], so in this example it doesn't matter, but in an other context you may have to choose very carefully, what alternative you should use.

jli · Accepted Answer · 2012-03-20 22:42:14Z

1

I see an extra comma in your statement (between Array and the first ().

Try changing the first line you have there to:

$sql = array();

What you had there sets the first element of $sql to a new array, it doesn't set $sql to a new array.

With this, your query should become: insert into product_colour (colour_id, prod_id)values (1, 1),(2, 1),(3, 1).

answered Mar 20, 2012 at 22:42

jli

6,6432 gold badges31 silver badges37 bronze badges

Comments

haltabush · Accepted Answer · 2012-03-20 22:45:07Z

1

Your problem comes from the 1st line of this snippet : you're doing $sql[] = array(); you should write $sql=array(); if you want a good initializatino.

Don't forget to sanitize your input before sending it to Mysql.

answered Mar 20, 2012 at 22:45

haltabush

4,5382 gold badges26 silver badges42 bronze badges

Collectives™ on Stack Overflow

Inserting multiple rows into MySql with one INSERT statement using PHP implode function

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related