$Sql = "LOAD DATE.... Problem

[Otto]

Hello to all

I have a problem how to write correctly a PHP string.

I did the following import with the help of PHPMyAdmin:

Nombre d'enregistremen ts insérés : 364 (traitement: 0.0553
sec.)
requête SQL:
LOAD DATA LOCAL INFILE '/tmp/phpVfO8r8' INTO TABLE `fncid`
FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'

I used this request in my PHP script like this:

$sql = "LOAD DATA LOCAL INFILE 'fncid.csv' INTO TABLE
'fncid' FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED
BY '\\\' LINES TERMINATED BY '\r\n'";

and get the following error:

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 ''fncid' FIELDS
TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES
TERMINATE' at line 1

I tried to remove the '' for the table name:

$Sql = "LOAD DATA LOCAL INFILE 'fncid.csv' INTO TABLE fncid
FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\\'
LINES TERMINATED BY '\r\n'";
mysql_query($sq l);
if(mysql_error( )) {
echo mysql_error() ."<br>\n";
}

and this time I get the following error message:
The used command is not allowed with this MySQL version.
I use this request bei the same Provider, same database and
same table as the PHPMyAdmin did it!

I think I don't write correctly the PHP string for this
request!

Could somebody help me.

Regards

Otto

[Andy Hassall]

Re: $Sql = &quot;LOAD DATE.... Problem

On Sat, 19 Feb 2005 09:18:30 +0100, Otto <ohaldi@freesur f.ch> wrote:
[color=blue]
>I have a problem how to write correctly a PHP string.
>
>I did the following import with the help of PHPMyAdmin:
>
>Nombre d'enregistremen ts insérés : 364 (traitement: 0.0553
>sec.)
>requête SQL:
>LOAD DATA LOCAL INFILE '/tmp/phpVfO8r8' INTO TABLE `fncid`[/color]

Note the sort of quotes here - backticks, ` not single quotes '. This is an
identifier (a table in this case) - not a string value.
[color=blue]
>FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'
>LINES TERMINATED BY '\r\n'
>
>I used this request in my PHP script like this:
>
>$sql = "LOAD DATA LOCAL INFILE 'fncid.csv' INTO TABLE
>'fncid' FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED[/color]

Now you've used single quotes - this won't work.
[color=blue]
>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 ''fncid' FIELDS[/color]

The parser's correctly pointed out the exact point of the error - the first
single quote starting a string literal where it's expecting a table identifier.
[color=blue]
>TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' LINES
>TERMINATE' at line 1
>
>I tried to remove the '' for the table name:[/color]

Which should have been ``, but removing them altogether is generally even
better.
[color=blue]
>$Sql = "LOAD DATA LOCAL INFILE 'fncid.csv' INTO TABLE fncid[/color]

OK.
[color=blue]
>FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\\'
>LINES TERMINATED BY '\r\n'";
>mysql_query($s ql);
>if(mysql_error ()) {
> echo mysql_error() ."<br>\n";
>}
>
>and this time I get the following error message:
>The used command is not allowed with this MySQL version.
>I use this request bei the same Provider, same database and
>same table as the PHPMyAdmin did it![/color]

Is this a version of PHPMyAdmin you installed yourself, or one provided by the
host? LOAD DATA LOCAL INFILE is subject to some security restrictions in MySQL;
if it's a host-provided PHPMyAdmin it's entirely possible they've got it
running using a slightly different setup that allows it, whereas other users
are restricted?

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

[Otto]

Re: $Sql = &quot;LOAD DATE.... Problem

Hello Andy
[color=blue]
> Is this a version of PHPMyAdmin you installed yourself, or one provided by the
> host? LOAD DATA LOCAL INFILE is subject to some security restrictions in MySQL;
> if it's a host-provided PHPMyAdmin it's entirely possible they've got it
> running using a slightly different setup that allows it, whereas other users
> are restricted?
>[/color]
Many thanks four your quick answer. Yes I used the version installed from the
host. I will try to upload on my self to see if it's work...

Have a nice weekend

Otto

[Otto]

Re: $Sql = &quot;LOAD DATE.... Problem

In article <liie11ht7jo75k j214hj9ee3dlpm2 vq83k@4ax.com>, Andy Hassall wrote:[color=blue]
> Is this a version of PHPMyAdmin you installed yourself, or one provided by the
> host? LOAD DATA LOCAL INFILE is subject to some security restrictions in MySQL;
> if it's a host-provided PHPMyAdmin it's entirely possible they've got it
> running using a slightly different setup that allows it, whereas other users
> are restricted?
>[/color]
Your are right. I just installed my version of PHPMyAdmin and get this error!

Requête SQL:
LOAD DATA LOCAL INFILE '/tmp/phpTMofAQ' INTO TABLE `fncid` FIELDS TERMINATED BY
';' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n'
MySQL a répondu:

#1148 - The used command is not allowed with this MySQL version


I will look for a another solution.

Thanks for your help.

Otto