0

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.

2
  • I guess it should be places rather than pset8.places. Also Null in the VALUESand id in the brackets (just before country_code,postal_code) can be omitted. Oct 24 '16 at 5:22
  • Thanks for the comments, Srikar. However, it works with pset8.places. When I stub the VALUES line with explicit data, it writes out to all 4095 rows. Also, in that case, the NULL works, and incrementing Indexes are generated.
    – rl777
    Oct 24 '16 at 12:06
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.

4
  • Matt, trying provide a response but, as a relative newbie, having trouble with this response editor.
    – rl777
    Oct 24 '16 at 14:46
  • 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.
    – rl777
    Oct 24 '16 at 14:50
  • guess I don't understand how mini_Markdown works.
    – rl777
    Oct 24 '16 at 14:53
  • If I use the line: 'VALUES (NULL,"US", "99661","Sand Point","Alaska","AK","Aleutians","East","xxx","013",55.3192,-160.4914,1)'; I get 4095 entries of this - ** link **But I get an error if I use: '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])';
    – rl777
    Oct 24 '16 at 16:32
-1

@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]:

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .