Solved

A sql error

Posted on 2009-05-13
7
377 Views
Last Modified: 2013-12-12
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
Comment
Question by:syedasimmeesaq
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24379533
this looks like $Table is empty?
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 24379582
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
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 24379614
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24379618
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24379638
I see now. you write:

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

and it must be:

FIELDS TERMINATED BY ',' ENCLOSED BY '\"'
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24379640
0
 
LVL 9

Author Comment

by:syedasimmeesaq
ID: 24379795
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

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How to properly use session_regenerate_id when user logs in 7 42
php string detection problem 7 34
error log using ftp 7 41
two ways encryption with php 3 28
Author Note: Since this E-E article was originally written, years ago, formal testing has come into common use in the world of PHP.  PHPUnit (http://en.wikipedia.org/wiki/PHPUnit) and similar technologies have enjoyed wide adoption, making it possib…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question