Add multiply rows on a table. PHP-MySQL

Question

I retrieve part of the below mentioned code from the following url: insert multiple rows via a php array into mysql However I face a problem. My code is as following:

//create an array
$array = array();

//add some values

//1st var_dump($array);
var_dump($array);

$msql = array(); 
foreach( $array as $row ) {
    $msql[] = '('.$row['trend'].', '.$row['image_url'].','.$row['sku'].')';
}

 var_dump($msql);   



 $insertData = "INSERT INTO Data_Info (trend, image_url,sku) VALUES ".implode(',', $msql);
 mysql_query($insertData) or die(mysql_error());

 mysql_close($conn);

The table that I have created contains three fields-> trend, image_url,sku.

Results of the 1st var_dump:

array(6) { 
   [0]=> string(7) "mytrend" 
   [1]=> string(70) "http://re.n.o.coat.png" 
   [2]=> string(12) "militarycoat" 
   [3]=> string(7) "mytrend" 
   [4]=> string(73) "http://re.n.o.padded.png" 
   [5]=> string(15) "signaturepadded" 
}

Results of the 2n var_dump:

array(6) {
   [0]=> string(8) "(m, m,m)" 
   [1]=> string(8) "(h, h,h)" 
   [2]=> string(8) "(m, m,m)" 
   [3]=> string(8) "(m, m,m)" 
   [4]=> string(8) "(h, h,h)" 
   [5]=> string(8) "(s, s,s)" 
} 
Unknown column 'm' in 'field list'

I can't understand what is going wrong. Can anyone help me?

score 1 · Answer 1 · answered Nov 22 '12 at 14:58

1

You are looping over every element of your input array and then trying to access properties on a string which has none. (surprised it even does anything)

try a for loop.

for($i = 0; $i < count($array); $i+=3)
{
    $msql[] = '('.$array[$i].', '.$array[$i+1].','.$array[$i+2].')';
}

or define your $array differently to suit your loop.

answered Nov 22 '12 at 14:58

Manuel Schweigert

4,884
4
20
32

You are correct. However, now I'm taking the follwoing exception: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '://re.n.o.coat.png,militarycoat)' at line 1 – Nick Robertson Nov 22 '12 at 15:07
Can you please add the whole query that was generated? Mysql-syntax errors are always kind of obfuscated. – Manuel Schweigert Dec 05 '12 at 07:47

score 0 · Answer 2 · answered Nov 22 '12 at 15:01

You are trying to access an associative array of arrays(multidimensional) with $row['trend'] etc... while you don't have it, as indicated in your first var_dump. your first var_dump should look like this:

$array = array(0 => array("trend"=> "value", "image_url" => "value, "sku" => "value"), 1=> array( .... ) and so on.

score 0 · Answer 3 · answered Nov 22 '12 at 15:15

Unknown column 'm' in 'field list' is generated by:

$insertData = "INSERT INTO Data_Info (trend, image_url,sku) VALUES ".implode(',', $msql);

The VALUES must be encapsulated in brackets. Also data should be in quotes. It now is:

VALUES (m, m, m),(h, h, h)

and it should be:

VALUES (('m', 'm', 'm'),('h', 'h', 'h'))
       ^                               ^
       |_________encapsulated__________|

Otherwise the data is seen as column (Unknown column 'm' in 'field list')

Secondly, your array doesn't contain the keys trend, image_url and sku, so the values you get from them are the first letter of each string in that array:

(m, m,m) is from the first letter of "mytrend"
(h, h,h) is from "http://re.n.o.coat.png"
etc

So create your array like this:

$array = array(0 => array("trend"     => "put", 
                          "image_url" => "something", 
                          "sku"       => "in here"), 
               1 => array("trend"     => "value1", 
                          "image_url" => "value2", 
                          "sku"       => "value3"),
               /* etc.. */
               );

Add multiply rows on a table. PHP-MySQL

3 Answers3