0

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?

Community
  • 1
  • 1
Bhavesh
  • 4,607
  • 13
  • 43
  • 70
  • 1
    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. –  Mar 20 '12 at 22:40
  • Silly mistake was made because I'm not much familiar with PHP. I'm from Java background. – Bhavesh Mar 20 '12 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. :) –  Mar 21 '12 at 09:08

3 Answers3

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.

Frederick Behrends
  • 3,075
  • 23
  • 47
  • what is the use of `$temp` here? – xkeshav May 26 '12 at 13:33
  • inst the ` $sql[]='('.$colour_id[$i].', '.$_POST['prod_id'].')';` supposed to `be $sql[$i]` ? – ianace Aug 23 '13 at 09:12
  • `$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. – Frederick Behrends Aug 30 '13 at 11:29
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).

jli
  • 6,523
  • 2
  • 29
  • 37
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.

haltabush
  • 4,508
  • 2
  • 24
  • 41