CODEWITHSUNDEEP
php
Bhavesh
Bhavesh

Reputation: 4677

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

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?

Upvotes: 0

Views: 6459

Answers (3)

haltabush
haltabush

Reputation: 4528

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.

Upvotes: 1

jli
jli

Reputation: 6623

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).

Upvotes: 1

Frederick Behrends
Frederick Behrends

Reputation: 3095

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.

Upvotes: 8

Related Questions