[Resolved] CLI PHP/MySQL insertion from CSV problem

ppowell

This is the command line SQL statement I wind up generating:

SQL:

mysql -h localhost --port=3306 -u me --password=ifyouonlyknew -D folks -e "LOAD DATA LOCAL INFILE '/home/me/docs/cma/data.csv' INTO TABLE contacts FIELDS TERMINATED BY ',' ESCAPED BY '\' OPTIONALLY ENCLOSED BY '""' LINES TERMINATED BY '\n' (title, first_name, last_name, position, school_company_org, department, address1, address2, address3, city, state, country, zip, main_phone, alt_phone, mobile_phone, fax, pager, main_email, alt_email, url )"

This is done via PHP script:

  $sql = "mysql -h $hostname --port=$port -u $username --password=$password -D $dbDefaultName -e \"" .
  	 "LOAD DATA LOCAL INFILE '$datafile' INTO TABLE contacts " .
         " FIELDS TERMINATED BY ',' ESCAPED BY '\\\\' OPTIONALLY ENCLOSED BY '\"\"' " .
         " LINES TERMINATED BY '\\n' " .
         " (title, first_name, last_name, position, school_company_org, department, " .
	 "  address1, address2, address3, city, state, country, zip, main_phone, " .
         "  alt_phone, mobile_phone, fax, pager, main_email, alt_email, url" .
         " )\" ";

I am using the PHP script as CLI PHP to populate a MySQL database table with data from a CSV file in a command-line one-time shot. However, upon attempting to execute the value of $sql:

$msg = exec("$msg 2>&1");
if ($msg) echo $msg;

I get the wacky message:

PAGER set to stdout

And nothing is done. No table updates, nothing. I am using MySQL 4.0.10 and PHP 4.3.8 within Red Hat 7.3 environment.

Does anyone know what "PAGER set to stdout" means and why that appears, also, why my data cannot seem to be inserted into table? I have full permissions to do so.

Thanx
Phil

nemonoman

I suppose you meant to write:
$msg = exec("$sql 2>&1");

I'm going to go out on a limb and suggest that your code has hard returns.
$sql, therefore, is being processed line by line, even though you mean it to be one big string.

The only part of these lines that your server understands is 'pager', which is *ix command. Thus the returned $msg.

Try this: Remove all the returns, make it one long one-line string, and see what happens.

ppowell

Sorry but there are no returns in $sql, it is one big long string as it's meant to be.

I never figured it out until I backwards-engineered the whole thing around mysql functions that PHP has (mysql_connect(), etc.) and then it worked.

I am still not sure why it did not work at first.

Phil

gmdavis

I was looking through the archives for info on importing csv data into mySQL. I ran across this post from about a year ago. What you are describing is exactly what I am trying to do (CLI and all). However, your post is marked resolved without much detail on how you solved it. Would you be willing to share that solution? It seems you original code was close but not quite. What was thie fix?