Hi,

I'm working on a script where a client can upload a txt file made from microsoft excel containing the inventory of all of his clients to the database. Later I will make it so the clients can type in their individual account numbers to view their inventory. But for right now I'm having trouble sending the data to the DB from the txt file.

Everytime I try to run the code below I get an error that says...

Parse error: parse error, unexpected T_STRING in /home/username/public_html/test.php on line 14

Here's the code. I looked it over and I guess I'm just blind because I can't seem to find exactly what the problem is. Can anyone lend me a hand? Thanks in advance!

<?php 

@ $db = mysql_pconnect('localhost', 'user', 'password'); 
if (!$db) 
{ 
echo 'Error: Could not connect to database. Please try again later.'; 
exit; 
} 
mysql_select_db('evanbart_test'); 

$file = "InventoryUpload.txt"; 
$query = "LOAD DATA INFILE '$file' INTO TABLE inv (client, anum, qnty, uom, pon, rnu, des, ware) FIELDS TERMINATED BY \';\' 

ENCLOSED BY \'|\' ESCAPED BY \'\\' LINES TERMINATED BY \'\r\n\'';

$result = mysql_query($query) or die ("Error: $query" . "mysql_error()); 

if ($result) 
echo ' Data Inserted Into The Database.<br>'; 

?>

    Well the syntax highlighting seems to bring out where the error is exactly.

    <?php 

$db = mysql_pconnect('localhost', 'user', 'password')or die ("MySQL Connection Error " . mysql_error()); 
if (!$db) 
{ 
	echo 'Error: Could not connect to database. Please try again later.'; 
	exit; 
} 
mysql_select_db('evanbart_test')or die ("MySQL Error " . mysql_error()); 

$file = "InventoryUpload.txt";

$query = "LOAD DATA INFILE '$file' INTO TABLE inv (client, anum, qnty, uom, pon, rnu, des, ware) FIELDS TERMINATED BY \';\' 

ENCLOSED BY \'|\' ESCAPED BY \'\' LINES TERMINATED BY \'\r\n\'";

$result = mysql_query($query) or die ("Error: $query" . mysql_error()); 

if ($result) 
	echo ' Data Inserted Into The Database.<br>'; 

?>

      Thanks a lot for your help. I can't believe I didn't even notice those. I guess I just overlooked them. Now I'm getting another error though.

      Error: LOAD DATA INFILE 'InventoryUpload.txt' INTO TABLE inv (client, anum, qnty, uom, pon, rnu, des, ware) FIELDS TERMINATED BY \';\' ENCLOSED BY \'|\' ESCAPED BY \'\' LINES TERMINATED BY \' \'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 'FIELDS TERMINATED BY \';\' ENCLOSED BY \'|\' ESCAPED BY \'[/QUOTE]

      Any ideas? Thanks again!

        Anyone got any ideas? I've looked around the net and can't seem to figure out what the problem would be.

          thanks for your help. didn't even realize that i needed to move the column name down to after the field stuff. i looked over that page a bunch and couldn't really figure out my problem. i took the column names out for now to atleast try to get this thing working but still get the error:

          Error: LOAD DATA INFILE 'InventoryUpload.txt' REPLACE INTO TABLE inv FIELDS TERMINATED BY ';' ENCLOSED BY '|' ESCAPED BY '\' LINES TERMINATED BY ' '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 ''' at line 2

          I'm so lost at this point.

            Change this last part of your SQL:

            LINES TERMINATED BY '\r\n'";

            to this:

            LINES TERMINATED BY '\r\n'";

            or:

            LINES TERMINATED BY '" . '\r\n' . "'";

            You want it to be '\r\n' in the SQL. Right now, PHP is converting it to a carriage return and newline. But MySQL wants it literally as '\r\n' you see.

            hth.

              Well I tried what you said and had no luck. When PHP was returning the error it was even returning it as \r\n this time rather than just a new line, but now I still get the message.

              Error: LOAD DATA INFILE 'InventoryUpload.txt' REPLACE INTO TABLE inv FIELDS TERMINATED BY ';' ENCLOSED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'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 '\r\n'' at line

              Here's my code. Anymore ideas? Sorry for this being such a hassle.

              <?php  

$db = mysql_pconnect('localhost', 'username', 'password') or die ("MySQL Connection Error " . mysql_error());
if (!$db)
{
    echo 'Error: Could not connect to database. Please try again later.';
    exit;
}
mysql_select_db('database_name') or die ("MySQL Error " . mysql_error());

$file = "InventoryUpload.txt";

$query = "LOAD DATA INFILE '$file' REPLACE INTO TABLE inv FIELDS TERMINATED BY '" . ';' . "' ENCLOSED BY '" . '|' . "' 

ESCAPED BY '" . '\\' . "' LINES TERMINATED BY '" . '\r\n' . "'"; 

$result = mysql_query($query) or die ("Error: $query" . mysql_error());

if ($result)
    echo ' Data Inserted Into The Database.<br>';

?>

                *bump

                Anyone got any ideas? I could really use some help right now. I had a friend of mine take a look at it and he couldn't seem to figure it out either.

                  Show us the exact query (echo out $query) and the full exact error message again. But it seems it doesn't like whatever is after the '\r\n' part.

                  FYI:

                  http://dev.mysql.com/doc/mysql/en/load-data.html#id2880657 wrote:

                  For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege.

                    Ok I echo'd out the query and this is what it came out with.

                    LOAD DATA INFILE 'InventoryUpload.txt' REPLACE INTO TABLE inv FIELDS TERMINATED BY ';' ENCLOSED BY '|' ESCAPED BY '\' LINES TERMINATED BY '\r\n'

                    Is that what you wanted? Because that's what it's printing out. As for the file privilege does that mean I have to chmd? I did that too. Still no luck.

                      What's the error that goes with the query last posted - is it the same as the last one posted?

                      Try it in your back end first. Like using phpMyAdmin or MySQL command prompt. See if it works that way first before trying to use it within PHP.

                      The username your using to connect to MySQL must have 'file' privileges. You have to use GRANT to do it if you haven't already. Also, make sure the database you're selecting has the inv table.

                      .

                        I decided to take your SQL and try it myself. I get the same result, however, if I remove:

                        ESCAPED BY '\'

                        then it doesn't give an SQL syntax error.

                        hth.

                          yeah checked my db. right table and everything. i got rid of the syntax error by changing the \ to a ,. that was really wierd. now i'm getting access denied. wierd.

                          Error: LOAD DATA INFILE 'InventoryUpload.txt' REPLACE INTO TABLE inv FIELDS TERMINATED BY ';' ENCLOSED BY '|' ESCAPED BY ',' LINES TERMINATED BY '\r\n'Access denied for user 'evanbart_evanbar'@'localhost' (using password: YES)

                            thanks, i'll check that out. i appreciate all your help!

                              I'm working on getting the grant function to work. Don't know what I've done wrong now.

                              $grant = "GRANT ALL ON inv * TO 'root'@'localhost' IDENTIFIED BY 'password'";

$query = "LOAD DATA INFILE '$file' REPLACE INTO TABLE inv FIELDS TERMINATED BY '" . ';' . "' ENCLOSED BY '" . '|' . "' 

ESCAPED BY '" . ',' . "' LINES TERMINATED BY '" . '\r\n' . "'";

$result = mysql_query($grant) or die ("Error: $grant" . mysql_error());

$result = mysql_query($query) or die ("Error: $query" . mysql_error());

                              Don't know if there's a better way I can do this or not. Some pointers or examples would be appreciated.

                              As of right now it's printing out an error when I try to run it.

                              Error: GRANT ALL ON inv TO 'root'@'localhost' IDENTIFIED BY 'XXX'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 ' TO 'root'@'localhost' IDENTIFIED BY 'XXXX'' at line 1

                                You have to do it in your back end system (phpmyadmin through cpanel as an example).

                                Don't grant in PHP. Don't grant "ALL" since it's a security risk. Even if you were to try it in PHP, then the username you're connecting with must be root or have "grant" privileges to begin with!

                                .

                                  Oops ok. Well by looking at the code is that the code I use in my PHP my admin. When I ran it in my panel I got the syntax error at line 1 problem again.

                                  GRANT ALL ON inv * TO 'root'@'localhost' IDENTIFIED BY 'password'

                                  What should I run it at if not "GRANT ALL"?

                                    Grant the username 'file' and anything else that username may need. Something like:

                                    GRANT SELECT ,
INSERT ,
UPDATE ,
DELETE ,
FILE ON *.* TO 'username_here'@'hostname_here'

                                    You can replace . with your table name if you want (to narrow user access to just that table).

                                    Warning: You do NOT want to change the 'root' username!! I assume you've created and are using another username. The 'root' already has all privileges.

                                    All this is mute, if you're using a web host provider and they don't allow the use of "LOAD DATA INFILE". So, check with them first.

                                    hth.