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')');

Answer 1

$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;

Answer 2

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

Answer 3

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.

Answer 4

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']}')";

Answer 5

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

Answer 6

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

Answer 7

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

Answer 8

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