• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

A sql error

I am using this the code below and it is giving me this error
Bad query: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 'TERMINATED BY ',' FIELDS ENCLOSED BY '"' LINES TERMINATED BY '\n'' at line 2.

I am not sure why. The code is attached below. Thanks

function import_files($path) {
  $d = dir($path);
  echo "Processing $path folder...<br />\n";
  while(($file=$d->read())!==false) {
    if(($file=='.') or ($file=='..')) continue;
    if(is_dir("$path/$file"))
      import_files("$path/$file"); # import from sub-folder
    else mysql_query(
     "LOAD DATA INFILE '$path/$file' INTO TABLE $Table
      FIELDS TERMINATED BY ','
	  FIELDS ENCLOSED BY '\"'
      LINES TERMINATED BY '\\n'") or die('Bad query:'.mysql_error());
  }
}

Open in new window

0
syedasimmeesaq
Asked:
syedasimmeesaq
  • 4
  • 3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this looks like $Table is empty?
0
 
syedasimmeesaqAuthor Commented:
no I have entry for table
here is the complete code
$Table = 'dn_table';  # table name
$StartFolder = 'test1';  # folder to import from
 
 
 
function import_files($path) {
  $d = dir($path);
  echo "Processing $path folder...<br />\n";
  while(($file=$d->read())!==false) {
    if(($file=='.') or ($file=='..')) continue;
    if(is_dir("$path/$file"))
      import_files("$path/$file"); # import from sub-folder
    else mysql_query(
     "LOAD DATA INFILE '$path/$file' INTO TABLE $Table
      FIELDS TERMINATED BY ','
	  FIELDS ENCLOSED BY '\"'
      LINES TERMINATED BY '\\n'") or die('Bad query:'.mysql_error());
  }
}
 
# import data 
echo "Starting import<br />\n";
import_files($StartFolder);
echo "Done!";

Open in new window

0
 
syedasimmeesaqAuthor Commented:
tried like this, by hard coding table but still doesnt work

function import_files($path) {
  $d = dir($path);
  echo "Processing $path folder...<br />\n";
  while(($file=$d->read())!==false) {
    if(($file=='.') or ($file=='..')) continue;
    if(is_dir("$path/$file"))
      import_files("$path/$file"); # import from sub-folder
    else mysql_query(
     "LOAD DATA INFILE '$path/$file' INTO TABLE dn_table     
            FIELDS TERMINATED BY ','
	  FIELDS ENCLOSED BY '\"'
      LINES TERMINATED BY '\\n'") or die('Bad query:'.mysql_error());
  }
}

Open in new window

0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that would be almost fine, but the $Table variable is not known inside the function, unless you use global:
$Table = 'dn_table';  # table name
$StartFolder = 'test1';  # folder to import from
 
 
 
function import_files($path) {
  global $Table;
 
  $d = dir($path);
  echo "Processing $path folder...<br />\n";
  while(($file=$d->read())!==false) {
    if(($file=='.') or ($file=='..')) continue;
    if(is_dir("$path/$file"))
      import_files("$path/$file"); # import from sub-folder
    else mysql_query(
     "LOAD DATA INFILE '$path/$file' INTO TABLE $Table
      FIELDS TERMINATED BY ','
          FIELDS ENCLOSED BY '\"'
      LINES TERMINATED BY '\\n'") or die('Bad query:'.mysql_error());
  }
}
 
# import data 
echo "Starting import<br />\n";
import_files($StartFolder);
echo "Done!";

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I see now. you write:

FIELDS TERMINATED BY ','
          FIELDS ENCLOSED BY '\"'

and it must be:

FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
syedasimmeesaqAuthor Commented:
ok now its not giving error but entering the wrong data. It skips customer name totally and then just mess up the fields for after first line.
here is how I have the data in text file
"cutomer1","","City","AK","99501","907-123-4567","",""
"cutomer2","","City","AK","99501","907-123-4567","",""
"cutomer3","","City","AK","99501","907-123-4567","",""

Thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now