Insert php array into mysql table

Question

Update: I forgot to mention that echo $matstring outputs '65.70', 'Coles','34 days','14' - which would appear to be the right syntax?

I'm a php/mysql newbie, and I think this is fairly basic, but having read all of the other stackoverflow questions on this topic and fiddling with different versions of my code for several hours I can't understand what I'm doing wrong. Would very much appreciate any help/suggestions.

Aim: pass data from my php array ($matrix) into a mysql table

$matrix[1]=
( [0] => 65.70 [1] => Coles [2] => 34 days [3] => 14 )

$matrix[2]=
( [0] => 62.70 [1] => Coles [2] => 13 days [3] => 14 )

$matrix[3]=
( [0] => 12.70 [1] => Safeway [2] => 43 days [3] => 14 )

Code:

$matstring=implode("','",$matrix[1]);
$matstring="'".$matstring."'";
mysql_query('INSERT INTO Australia (Price, Company, Days, Weight) VALUES ('$matstring')');

you can use serialize($data) before inserting and deserialize when pulling the data. or use json_encode and json_decode.. either way really. — skrilled, Feb 24 '13 at 20:54
That isn't the answer. Try changing mysql_query to echo, then checking the syntax. Most likely the extra `'` around $matstring are meaning you are having `''` around the values. — Rich Bradshaw, Feb 24 '13 at 20:56
oh nevermind i just read the title and assumed it was exactly what i was to expect.. and also using native queries seems a bad idea vs using PDO and easily making it something like $dbh->prepare("INSERT INTO Australia (Price, Company, Days, Weight) VALUES (?, ?, ?, ?)"); $stmt->execute($matrix[1]); — skrilled, Feb 24 '13 at 20:59

score 2 · Answer 1 · answered Feb 24 '13 at 21:25

when i run this code :

$matrix = array();
$matrix[1] = array( 0 => 65.70, 1 => 'Coles', 2 => '34 days', 3 => 14 );
$matstring=implode("','",$matrix[1]);
$matstring="'".$matstring."'";
print "INSERT INTO Australia (`Price`, `Company`, `Days`, `Weight`) VALUES ($matstring)";

become result:

INSERT INTO Australia (`Price`, `Company`, `Days`, `Weight`) VALUES ('65.7','Coles','34 days','14')

score 1 · Accepted Answer · edited Aug 27 '13 at 10:49

Corrected code:

$matstring=implode("','",$matrix[1]);

mysql_query("INSERT INTO Australia (Price, Company, Days, Weight) VALUES ('$matstring')");

(i.e. delete the second line from original code and put double quotes around the argument of mysql_query)

Appreciate user1847757's help - as s/he pointed out, $matstring itself was correct, but the single quotes inside of VALUES(' ') were being joined to the single quotes added to $matstring in the 2nd line of my original code, resulting in VALUES(''65.70','Coles','34 days','14'')

Thanks all for your help & suggestions

score 1 · Answer 3 · edited Mar 21 '18 at 11:57

$arr = array(
'Afghanistan',
'Albania',
'Algeria',
'American Samoa',
'Andorra',
'Angola',
'Anguilla',
'Antarctica',
'Antigua and Barbuda',
'Argentina',
'Armenia',
'Aruba',
'Australia',
'Austria',
'Azerbaijan',
'Bahamas',
'Bahrain',
'Bangladesh',
'Barbados',
'Belarus',
'Belgium',
'Belize',
'Benin',
'Bermuda',
'Bhutan',
'Bolivia',
'Bosnia and Herzegovina',
'Botswana',
'Bouvet Island',
'Brazil',
'British Indian Ocean Territory',
'Brunei Darussalam',
'Bulgaria',
'Burkina Faso',
'Burundi',
'Cambodia',
'Cameroon',
'Canada',
'Cape Verde',
'Cayman Islands',
'Central African Republic',
'Chad',
'Chile',
'China',
'Christmas Island',
'Cocos (Keeling) Islands',
'Colombia',
'Comoros',
'Congo',
'Cook Islands',
'Costa Rica',
'Croatia (Hrvatska)',
'Cuba',
'Cyprus',
'Czech Republic',
'Denmark',
'Djibouti',
'Dominica',
'Dominican Republic',
'East Timor',
'Ecuador',
'Egypt',
'El Salvador',
'Equatorial Guinea',
'Eritrea',
'Estonia',
'Ethiopia',
'Falkland Islands (Malvinas)',
'Faroe Islands',
'Fiji',
'Finland',
'France',
'France, Metropolitan',
'French Guiana',
'French Polynesia',
'French Southern Territories',
'Gabon',
'Gambia',
'Georgia',
'Germany',
'Ghana',
'Gibraltar',
'Guernsey',
'Greece',
'Greenland',
'Grenada',
'Guadeloupe',
'Guam',
'Guatemala',
'Guinea',
'Guinea-Bissau',
'Guyana',
'Haiti',
'Heard and Mc Donald Islands',
'Honduras',
'Hong Kong',
'Hungary',
'Iceland',
'India',
'Isle of Man',
'Indonesia',
'Iran (Islamic Republic of)',
'Iraq',
'Ireland',
'Israel',
'Italy',
'Ivory Coast',
'Jersey',
'Jamaica',
'Japan',
'Jordan',
'Kazakhstan',
'Kenya',
'Kiribati',
'Korea, Democratic People\'s Republic of',
'Korea, Republic of',
'Kosovo',
'Kuwait',
'Kyrgyzstan',
'Lao People\'s Democratic Republic',
'Latvia',
'Lebanon',
'Lesotho',
'Liberia',
'Libyan Arab Jamahiriya',
'Liechtenstein',
'Lithuania',
'Luxembourg',
'Macau',
'Macedonia',
'Madagascar',
'Malawi',
'Malaysia',
'Maldives',
'Mali',
'Malta',
'Marshall Islands',
'Martinique',
'Mauritania',
'Mauritius',
'Mayotte',
'Mexico',
'Micronesia, Federated States of',
'Moldova, Republic of',
'Monaco',
'Mongolia',
'Montenegro',
'Montserrat',
'Morocco',
'Mozambique',
'Myanmar',
'Namibia',
'Nauru',
'Nepal',
'Netherlands',
'Netherlands Antilles',
'New Caledonia',
'New Zealand',
'Nicaragua',
'Niger',
'Nigeria',
'Niue',
'Norfolk Island',
'Northern Mariana Islands',
'Norway',
'Oman',
'Pakistan',
'Palau',
'Palestine',
'Panama',
'Papua New Guinea',
'Paraguay',
'Peru',
'Philippines',
'Pitcairn',
'Poland',
'Portugal',
'Puerto Rico',
'Qatar',
'Reunion',
'Romania',
'Russian Federation',
'Rwanda',
'Saint Kitts and Nevis',
'Saint Lucia',
'Saint Vincent and the Grenadines',
'Samoa',
'San Marino',
'Sao Tome and Principe',
'Saudi Arabia',
'Senegal',
'Serbia',
'Seychelles',
'Sierra Leone',
'Singapore',
'Slovakia',
'Slovenia',
'Solomon Islands',
'Somalia',
'South Africa',
'South Sudan',
'South Georgia South Sandwich Islands',
'Spain',
'Sri Lanka',
'St. Helena',
'St. Pierre and Miquelon',
'Sudan',
'Suriname',
'Svalbard and Jan Mayen Islands',
'Swaziland',
'Sweden',
'Switzerland',
'Syrian Arab Republic',
'Taiwan',
'Tajikistan',
'Tanzania, United Republic of',
'Thailand',
'Togo',
'Tokelau',
'Tonga',
'Trinidad and Tobago',
'Tunisia',
'Turkey',
'Turkmenistan',
'Turks and Caicos Islands',
'Tuvalu',
'Uganda',
'Ukraine',
'United Arab Emirates',
'United Kingdom',
'United States',
'United States minor outlying islands',
'Uruguay',
'Uzbekistan',
'Vanuatu',
'Vatican City State',
'Venezuela',
'Vietnam',
'Virgin Islands (British)',
'Virgin Islands (U.S.)',
'Wallis and Futuna Islands',
'Western Sahara',
'Yemen',
'Zaire',
'Zambia',
'Zimbabwe'
);
foreach($arr as $ar){

  $query = "INSERT INTO country(country) VALUES('$ar') ";
  mysqli_query($connect,$query);

}
die;

score 0 · Answer 4 · answered Feb 24 '13 at 20:56

0

you're building the query wrong. it'll look like this:

INSERT INTO ... VALUES (''65.70,Coles,34 days,14'');

note how all 4 values are inside a SINGLE string.. but the string's also wrong ('').

you need to quote each individual value of the array:

('65.70', 'Coles', '34 days', '14')

THEN you implode it, giving you

INSERT INTO ... VALUES ('65.70', 'Coles', etc...)

answered Feb 24 '13 at 20:56

Marc B

356,200
43
426
500

I'm still a bit confused. When I echo $matstring with the code I posted, it outputs '65.70', 'Coles','34 days','14'. So isn't that exactly the syntax I require? Why can't I pass it into VALUES('$matstring')? – user2037290 Feb 24 '13 at 21:08
Your $matstring is correct; however, when you use VALUES ('$matstring') in your SQL string, you are adding additional quotations around it, creating VALUES(''65.70','Coles','34 days',14'') – user18477575 Feb 25 '13 at 00:42

Eugen · Answer 5 · 2013-02-24T21:17:04.187

0

mysql_query("INSERT INTO Australia (`Price`, `Company`, `Days`, `Weight`) VALUES ($matstring)");

edited Feb 24 '13 at 21:17

answered Feb 24 '13 at 21:00

Eugen

1,356
12
15

I tried this correction but it still didn't work (script breaks) – user2037290 Feb 24 '13 at 21:09

score 0 · Answer 6 · answered Sep 06 '13 at 14:09

I faced similiar problem today and solved it like this: in my case $bank_info is an array that holds the data to insert as a new row...

$sql = "INSERT INTO user_bank_info (user_id,
                                    name,
                                    address,
                                    ZIP,
                                    city,
                                    state,
                                    country,
                                    wat_id,
                                    bank_bic,
                                    account_iban)
        VALUES  ('$id_user',
                 '{$bank_info['name']}',
                 '{$bank_info['addr']}',
                 '{$bank_info['zipn']}',
                 '{$bank_info['city']}',
                 '{$bank_info['stat']}',
                 '{$bank_info['ctry']}',
                 '{$bank_info['watn']}',
                 '{$bank_info['bbic']}',
                 '{$bank_info['iban']}')";

score 0 · Answer 7 · edited Dec 22 '14 at 07:38

0

Use serialize function for adding array data into table. Ex-

$array["a"] = "Foo";
$array["b"] = "Bar";
$array["c"] = "Baz";
$array["d"] = "Wom";
$str = serialize($array);

add $str into table.When we get data then use unserialize function.Ex- $arr = unserialize(urldecode($strenc));

Note: For URL, use urldecode function.

edited Dec 22 '14 at 07:38

shankar.parshimoni

1,289
5
22
42

answered Dec 22 '14 at 07:10

Tapeshwar

1

score 0 · Answer 8 · edited Oct 20 '15 at 16:20

Here's how I do it (very simple):

//Fetch the original array:

$query = mysql_query("SELECT * FROM Original_Table") or die(mysql_error());

while($record = mysql_fetch_array($query)){

$num=count($record); //Count The Number Of elements in the array

$num=$num/2; //use this if the array has both key numbers AND names

$cnum=0;

for($cnum;$cnum<$num;$cnum++){ //cycle through the elements of the array

$entry=$entry."'".$record[$cnum]."',";

}

$entry=substr($entry,0,strlen($entry)-1);// this just removes the final comma

// Now we can add the entry into the new table:

mysql_query("insert into New Table (Field1, Field2,Field3.....etc) values($entry)")or die(mysql_error());

}//end while

Insert php array into mysql table

8 Answers8

Linked