Pset8 - Import using PHP array

Question

Here's a fragment of my code containing the elements pertinent to my question. I am trying to use a PHP data array to send data to the Import routine. I establish the array $keywords and apply preg_split in a loop to the lines of US.txt. I have verified that preg_split is working as expected insofar as it fills the array with the geonames data elements .

$keywords = preg_split(.....

$sql = 'INSERT INTO pset8.places '.
'(id,country_code,postal_code,place_name,admin_name1,admin_code1,admin_name2,admin_code2,admin_name3,admin_code3,latitude,longitude,accuracy) '

'VALUES(NULL,$keywords[0],$keywords[1],$keywords[2]$,keywords[3],$keywords[4],$keywords[5],$keywords[6],$keywords[7],$keywords[8],$keywords[9],$keywords[10],$keywords[11])';

However, the data is failing to get written to the SQL data base. Here is the error message I get -

Could not enter data: 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 '[0],$keywords[1],$keywords[2]$,keywords[3],$keywords[4],$keywords[5],$keywords[6' at line

I am not looking for the correct code. Just some guidance as to why the above is not working, what I'm not considering or not understanding. Thanks.

Answer 1

Try dump($keywords) to verify that the array data structure contains what you want it to.

Also, if preg_split() is giving you trouble here, you may find it easier to use fgetcsv().

Hope this helps.

Answer 2

@Matt Obert, I get different results based on whether I do this -

while (($keywords = fgets($handle)) !== false) {
$keywords = preg_split("/[\t]/",$keywords);
print_r($keywords);
var_dump($keywords);
print "\nkeywords[2]:".$keywords[2]."\n";

or this -

while (($keywords = fgetcsv($handle,"/[\t]/") ) !== FALSE ) {
print_r($keywords);
var_dump($keywords);
print "\nkeywords[2]:".$keywords[2]."\n";

and I'm not sure why there is a difference.

Here are the respective outputs of the above:

Array ( [0] => US [1] => 82730 [2] => Upton [3] => Wyoming [4] => WY [5] => Weston [6] => 045 [7] => [8] => [9] => 44.0893 [10] => -104.6352 [11] => 4

) array(12) { [0]=> string(2) "US" [1]=> string(5) "82730" [2]=> string(5) "Upton" [3]=> string(7) "Wyoming" [4]=> string(2) "WY" [5]=> string(6) "Weston" [6]=> string(3) "045" [7]=> string(0) "" [8]=> string(0) "" [9]=> string(7) "44.0893" [10]=> string(9) "-104.6352" [11]=> string(2) "4 " }

keywords[2]:Upton

====================================================

and from the fgetcsv -

Array ( [0] => US 82730 Upton Wyoming WY Weston 045 44.0893 -104.6352 4 ) array(1) { [0]=> string(58) "US 82730 Upton Wyoming WY Weston 045 44.0893 -104.6352 4" }

keywords[2]: