Using MySQL Command Load Data Infile

[Bruce A. Julseth]

When I execute this SQL statement in my PHP code, I get an error
"File '.\Address.txt' not found (Errcode: 2)"

$File = addslashes(".\A ddress.txt");
$SQL = "Load Data InFile \"" . $File . "\" into table addresses";
$result = mysql_query($SQ L) or die(mysql_error ());

The file is located in the same directory as my .PHP file.

How do I generate a relative address for this file so that it can be found?
I'm running on XP Prof..

Thanks...

Bruce

[Chris Hope]

Re: Using MySQL Command Load Data Infile

Bruce A. Julseth wrote:
[color=blue]
> When I execute this SQL statement in my PHP code, I get an error
> "File '.\Address.txt' not found (Errcode: 2)"
>
> $File = addslashes(".\A ddress.txt");
> $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
> $result = mysql_query($SQ L) or die(mysql_error ());
>
> The file is located in the same directory as my .PHP file.
>
> How do I generate a relative address for this file so that it can be
> found? I'm running on XP Prof..[/color]

I suspect MySQL requires a full pathname to the file, because you're running
a SQL command. Although the file is relative to the PHP script MySQL has no
conception of that relative path. Try it with a full pathname and you
should be fine.

If it was a Unix/Linux box you'd need to make sure the MySQL server has
permissions to read from that directory and file, but you probably won't
have this problem on Windows.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

[Bruce A. Julseth]

Re: Using MySQL Command Load Data Infile

Thanks for the response. I have a problem using absolute path names. I am
developing on a local machine with Apache. When completed, I will upload to
an ISP and I have no idea what the absolute path to my file will be. Any
suggestions here??

Or, does someone else have an idea on how I can use an absolute path.

Thanks...

Bruce

"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:1088394833 _140918@216.128 .74.129...[color=blue]
> Bruce A. Julseth wrote:
>[color=green]
> > When I execute this SQL statement in my PHP code, I get an error
> > "File '.\Address.txt' not found (Errcode: 2)"
> >
> > $File = addslashes(".\A ddress.txt");
> > $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
> > $result = mysql_query($SQ L) or die(mysql_error ());
> >
> > The file is located in the same directory as my .PHP file.
> >
> > How do I generate a relative address for this file so that it can be
> > found? I'm running on XP Prof..[/color]
>
> I suspect MySQL requires a full pathname to the file, because you're[/color]
running[color=blue]
> a SQL command. Although the file is relative to the PHP script MySQL has[/color]
no[color=blue]
> conception of that relative path. Try it with a full pathname and you
> should be fine.
>
> If it was a Unix/Linux box you'd need to make sure the MySQL server has
> permissions to read from that directory and file, but you probably won't
> have this problem on Windows.
>
> --
> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/[/color]

[Five Cats]

Re: Using MySQL Command Load Data Infile

In message <40e010b5_1@new s1.prserv.net>, Bruce A. Julseth
<bruceaj@attglo bal.net> writes[color=blue]
>Thanks for the response. I have a problem using absolute path names. I am
>developing on a local machine with Apache. When completed, I will upload to
>an ISP and I have no idea what the absolute path to my file will be. Any
>suggestions here??
>
>Or, does someone else have an idea on how I can use an absolute path.[/color]

I think you need to look at some of the server variables which help with
paths. Whilst you don't know what the absolute path will be, surely you
will know the relative path?
[color=blue]
>
>Thanks...
>
>Bruce
>
>"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
>news:108839483 3_140918@216.12 8.74.129...[color=green]
>> Bruce A. Julseth wrote:
>>[color=darkred]
>> > When I execute this SQL statement in my PHP code, I get an error
>> > "File '.\Address.txt' not found (Errcode: 2)"
>> >
>> > $File = addslashes(".\A ddress.txt");
>> > $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
>> > $result = mysql_query($SQ L) or die(mysql_error ());
>> >
>> > The file is located in the same directory as my .PHP file.
>> >
>> > How do I generate a relative address for this file so that it can be
>> > found? I'm running on XP Prof..[/color]
>>
>> I suspect MySQL requires a full pathname to the file, because you're[/color]
>running[color=green]
>> a SQL command. Although the file is relative to the PHP script MySQL has[/color]
>no[color=green]
>> conception of that relative path. Try it with a full pathname and you
>> should be fine.
>>
>> If it was a Unix/Linux box you'd need to make sure the MySQL server has
>> permissions to read from that directory and file, but you probably won't
>> have this problem on Windows.
>>
>> --
>> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/[/color]
>
>[/color]

--
Five Cats
Email to: cats_spam at uk2 dot net

[Bruce A. Julseth]

Re: Using MySQL Command Load Data Infile

You suggested "Server Variables." I'm new to this game. Can you give me a
few suggestions?

Thanks...

Bruce

"Five Cats" <cats_spam@[127.0.0.1]> wrote in message
news:oXyfPRARNB 4AFwPc@[127.0.0.1]...[color=blue]
> In message <40e010b5_1@new s1.prserv.net>, Bruce A. Julseth
> <bruceaj@attglo bal.net> writes[color=green]
> >Thanks for the response. I have a problem using absolute path names. I am
> >developing on a local machine with Apache. When completed, I will upload[/color][/color]
to[color=blue][color=green]
> >an ISP and I have no idea what the absolute path to my file will be. Any
> >suggestions here??
> >
> >Or, does someone else have an idea on how I can use an absolute path.[/color]
>
> I think you need to look at some of the server variables which help with
> paths. Whilst you don't know what the absolute path will be, surely you
> will know the relative path?
>[color=green]
> >
> >Thanks...
> >
> >Bruce
> >
> >"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
> >news:108839483 3_140918@216.12 8.74.129...[color=darkred]
> >> Bruce A. Julseth wrote:
> >>
> >> > When I execute this SQL statement in my PHP code, I get an error
> >> > "File '.\Address.txt' not found (Errcode: 2)"
> >> >
> >> > $File = addslashes(".\A ddress.txt");
> >> > $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
> >> > $result = mysql_query($SQ L) or die(mysql_error ());
> >> >
> >> > The file is located in the same directory as my .PHP file.
> >> >
> >> > How do I generate a relative address for this file so that it can be
> >> > found? I'm running on XP Prof..
> >>
> >> I suspect MySQL requires a full pathname to the file, because you're[/color]
> >running[color=darkred]
> >> a SQL command. Although the file is relative to the PHP script MySQL[/color][/color][/color]
has[color=blue][color=green]
> >no[color=darkred]
> >> conception of that relative path. Try it with a full pathname and you
> >> should be fine.
> >>
> >> If it was a Unix/Linux box you'd need to make sure the MySQL server has
> >> permissions to read from that directory and file, but you probably[/color][/color][/color]
won't[color=blue][color=green][color=darkred]
> >> have this problem on Windows.
> >>
> >> --
> >> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/[/color]
> >
> >[/color]
>
> --
> Five Cats
> Email to: cats_spam at uk2 dot net[/color]

[Gary L. Burnore]

Re: Using MySQL Command Load Data Infile

On Mon, 28 Jun 2004 12:27:30 -0400, "Bruce A. Julseth"
<bruceaj@attglo bal.net> wrote:
[color=blue]
>You suggested "Server Variables." I'm new to this game. Can you give me a
>few suggestions?
>[/color]
PLEASE stop top posting.

Server Variables:


With bourne: (/bin/sh) and Bash (/usr/bin/bash>

$ VARIABLENAME="v ariable contents"
$ export VARIABLE NAME

With K shell (/usr/bin/ksh)

export VARIABLENAME="v iarable contents"

With CSH (/bin/csh>

setenv VARIABLENAME "variable contents"


type: "set" or "printenv" to see what your settings are now.

Oh, and did I mention you should stop top posting? :)


--
gburnore@databa six dot com
---------------------------------------------------------------------------
How you look depends on where you go.
---------------------------------------------------------------------------
Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
DataBasix | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
| ÝÛ³ 3 4 1 4 2 ݳ޳ 6 9 0 6 9 ÝÛ³
Black Helicopter Repair Svcs Division | Official Proof of Purchase
=============== =============== =============== =============== ===============
Want one? GET one! http://www.databasix.com
=============== =============== =============== =============== ===============

[Five Cats]

Re: Using MySQL Command Load Data Infile

In message <40e048cd_4@new s1.prserv.net>, Bruce A. Julseth
<bruceaj@attglo bal.net> writes[color=blue]
>You suggested "Server Variables." I'm new to this game. Can you give me a
>few suggestions?[/color]

http://www.php.net/manual/en/language.variables.predefined.php

Also create the following script and look at it's output, on both
machines:

<?php
phpinfo();
?>

and see

http://uk2.php.net/manual/en/function.phpinfo.php

[color=blue]
>
>Thanks...
>
>Bruce
>
>"Five Cats" <cats_spam@[127.0.0.1]> wrote in message
>news:oXyfPRARN B4AFwPc@[127.0.0.1]...[color=green]
>> In message <40e010b5_1@new s1.prserv.net>, Bruce A. Julseth
>> <bruceaj@attglo bal.net> writes[color=darkred]
>> >Thanks for the response. I have a problem using absolute path names. I am
>> >developing on a local machine with Apache. When completed, I will upload[/color][/color]
>to[color=green][color=darkred]
>> >an ISP and I have no idea what the absolute path to my file will be. Any
>> >suggestions here??
>> >
>> >Or, does someone else have an idea on how I can use an absolute path.[/color]
>>
>> I think you need to look at some of the server variables which help with
>> paths. Whilst you don't know what the absolute path will be, surely you
>> will know the relative path?
>>[color=darkred]
>> >
>> >Thanks...
>> >
>> >Bruce
>> >
>> >"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
>> >news:108839483 3_140918@216.12 8.74.129...
>> >> Bruce A. Julseth wrote:
>> >>
>> >> > When I execute this SQL statement in my PHP code, I get an error
>> >> > "File '.\Address.txt' not found (Errcode: 2)"
>> >> >
>> >> > $File = addslashes(".\A ddress.txt");
>> >> > $SQL = "Load Data InFile \"" . $File . "\" into table addresses";
>> >> > $result = mysql_query($SQ L) or die(mysql_error ());
>> >> >
>> >> > The file is located in the same directory as my .PHP file.
>> >> >
>> >> > How do I generate a relative address for this file so that it can be
>> >> > found? I'm running on XP Prof..
>> >>
>> >> I suspect MySQL requires a full pathname to the file, because you're
>> >running
>> >> a SQL command. Although the file is relative to the PHP script MySQL[/color][/color]
>has[color=green][color=darkred]
>> >no
>> >> conception of that relative path. Try it with a full pathname and you
>> >> should be fine.
>> >>
>> >> If it was a Unix/Linux box you'd need to make sure the MySQL server has
>> >> permissions to read from that directory and file, but you probably[/color][/color]
>won't[color=green][color=darkred]
>> >> have this problem on Windows.
>> >>
>> >> --
>> >> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/
>> >
>> >[/color]
>>
>> --
>> Five Cats
>> Email to: cats_spam at uk2 dot net[/color]
>
>[/color]

--
Five Cats
Email to: cats_spam at uk2 dot net

[Andy Hassall]

Re: Using MySQL Command Load Data Infile

On Sun, 27 Jun 2004 23:28:57 -0400, "Bruce A. Julseth" <bruceaj@attglo bal.net>
wrote:
[color=blue]
>When I execute this SQL statement in my PHP code, I get an error
>"File '.\Address.txt' not found (Errcode: 2)"
>
>$File = addslashes(".\A ddress.txt");
>$SQL = "Load Data InFile \"" . $File . "\" into table addresses";
>$result = mysql_query($SQ L) or die(mysql_error ());
>
>The file is located in the same directory as my .PHP file.[/color]

Then then command you want would probably be LOAD DATA LOCAL INFILE - note the
LOCAL. Without it, I believe the path is relative to the database data
directory. Or you need an absolute path and stick with LOAD DATA INFILE,
assuming that the MySQL database is running on the same machine as the
webserver.

You can get the current directory with getcwd.

http://uk2.php.net/manual/en/function.getcwd.php

Or if you're not necessarily in that directory (perhaps in an include file?)
then dirname(__FILE_ _).

http://uk2.php.net/manual/en/function.dirname.php

I also remember there are security restrictions on usage of LOAD DATA LOCAL
INFILE that depend on MySQL version and compile-time options (possibly
changeable in my.cnf?). See the manual for more information.

http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html

http://www.mysql.com/news-and-events/newsletter/2002-05/a0000000012.html

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

[Bruce A. Julseth]

Re: Using MySQL Command Load Data Infile

Okay, I'll be happy to stop top posting if I only knew what it is? What is
"top Posting?"

Bruce

"Gary L. Burnore" <gburnore@datab asix.com> wrote in message
news:cbph22$cf1 $1@blackhelicop ter.databasix.c om...[color=blue]
> On Mon, 28 Jun 2004 12:27:30 -0400, "Bruce A. Julseth"
> <bruceaj@attglo bal.net> wrote:
>[color=green]
> >You suggested "Server Variables." I'm new to this game. Can you give me a
> >few suggestions?
> >[/color]
> PLEASE stop top posting.
>
> Server Variables:
>
>
> With bourne: (/bin/sh) and Bash (/usr/bin/bash>
>
> $ VARIABLENAME="v ariable contents"
> $ export VARIABLE NAME
>
> With K shell (/usr/bin/ksh)
>
> export VARIABLENAME="v iarable contents"
>
> With CSH (/bin/csh>
>
> setenv VARIABLENAME "variable contents"
>
>
> type: "set" or "printenv" to see what your settings are now.
>
> Oh, and did I mention you should stop top posting? :)
>
>
> --
> gburnore@databa six dot com
> --------------------------------------------------------------------------[/color]
-[color=blue]
> How you look depends on where you go.
> --------------------------------------------------------------------------[/color]
-[color=blue]
> Gary L. Burnore | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
> | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
> DataBasix | ÝÛ³ºÝ³Þ³ºÝ³³ÝÛº ݳ޳ºÝ³Ý³Þ³ºÝ³Ý ÝÛ³
> | ÝÛ³ 3 4 1 4 2 ݳ޳ 6 9 0 6 9 ÝÛ³
> Black Helicopter Repair Svcs Division | Official Proof of Purchase
>[/color]
=============== =============== =============== =============== ===============[color=blue]
> Want one? GET one! http://www.databasix.com
>[/color]
=============== =============== =============== =============== ===============

[Michael Austin]

Re: Using MySQL Command Load Data Infile

Bruce A. Julseth wrote:[color=blue]
> Okay, I'll be happy to stop top posting if I only knew what it is? What is
> "top Posting?"
>
> Bruce
>[/color]
<snip>

just that. you are responding at the TOP of the orignal message when
netiquette dictates it should FOLLOW (be at the bottom) of the original
message... and if the original message is too long, it can be
appropriate to "snip" the irrelevant parts in your reply.

....like this response.

Michael Austin.

[Bruce A. Julseth]

Re: Using MySQL Command Load Data Infile


"Michael Austin" <maustin@firstd basource.com> wrote in message
news:XggEc.9786 $oJ5.6105@newss vr24.news.prodi gy.com...[color=blue]
> Bruce A. Julseth wrote:[color=green]
> > Okay, I'll be happy to stop top posting if I only knew what it is? What[/color][/color]
is[color=blue][color=green]
> > "top Posting?"
> >
> > Bruce
> >[/color]
> <snip>
>
> just that. you are responding at the TOP of the orignal message when
> netiquette dictates it should FOLLOW (be at the bottom) of the original
> message... and if the original message is too long, it can be
> appropriate to "snip" the irrelevant parts in your reply.
>
> ...like this response.
>
> Michael Austin.[/color]

Okay... I'll try to remember this in the future. I've only been using
newgroups for about 20 years and this is the first time anyone has told me
that I was wrong...

Bruce

[Bruce A. Julseth]

Re: Using MySQL Command Load Data Infile


"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:qqt0e01nff sucimnbip19klcg kp0lvjh0j@4ax.c om...[color=blue]
> On Sun, 27 Jun 2004 23:28:57 -0400, "Bruce A. Julseth"[/color]
<bruceaj@attglo bal.net>[color=blue]
> wrote:
>[color=green]
> >When I execute this SQL statement in my PHP code, I get an error
> >"File '.\Address.txt' not found (Errcode: 2)"
> >
> >$File = addslashes(".\A ddress.txt");
> >$SQL = "Load Data InFile \"" . $File . "\" into table addresses";
> >$result = mysql_query($SQ L) or die(mysql_error ());
> >
> >The file is located in the same directory as my .PHP file.[/color]
>
> Then then command you want would probably be LOAD DATA LOCAL INFILE -[/color]
note the[color=blue]
> LOCAL. Without it, I believe the path is relative to the database data
> directory. Or you need an absolute path and stick with LOAD DATA INFILE,
> assuming that the MySQL database is running on the same machine as the
> webserver.[/color]

I'll give this a try. I like it and hope it works.
[color=blue]
>
> You can get the current directory with getcwd.[/color]

I used this and it solved the problem.
[color=blue]
>
> http://uk2.php.net/manual/en/function.getcwd.php
>
> Or if you're not necessarily in that directory (perhaps in an include[/color]
file?)[color=blue]
> then dirname(__FILE_ _).
>
> http://uk2.php.net/manual/en/function.dirname.php
>
> I also remember there are security restrictions on usage of LOAD DATA[/color]
LOCAL[color=blue]
> INFILE that depend on MySQL version and compile-time options (possibly
> changeable in my.cnf?). See the manual for more information.
>
> http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
> http://www.mysql.com/news-and-events...000000012.html[/color]

I'll give these a try. Thanks.

[color=blue]
>
> --
> Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
> http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space[/color]

[Michael Austin]

Re: Using MySQL Command Load Data Infile

Bruce A. Julseth wrote:
[color=blue]
> "Michael Austin" <maustin@firstd basource.com> wrote in message
> news:XggEc.9786 $oJ5.6105@newss vr24.news.prodi gy.com...
>[color=green]
>>Bruce A. Julseth wrote:
>>[color=darkred]
>>>Okay, I'll be happy to stop top posting if I only knew what it is? What[/color][/color]
>
> is
>[color=green][color=darkred]
>>>"top Posting?"
>>>
>>>Bruce
>>>[/color]
>>
>><snip>
>>
>>just that. you are responding at the TOP of the orignal message when
>>netiquette dictates it should FOLLOW (be at the bottom) of the original
>>message... and if the original message is too long, it can be
>>appropriate to "snip" the irrelevant parts in your reply.
>>
>>...like this response.
>>
>>Michael Austin.[/color]
>
>
> Okay... I'll try to remember this in the future. I've only been using
> newgroups for about 20 years and this is the first time anyone has told me
> that I was wrong...[/color]

.... me too, and I have always been aware of it.. :)
[color=blue]
>
> Bruce
>
>
>[/color]

Michael Austin.

[Bruce A. Julseth]

Re: Using MySQL Command Load Data Infile


"Andy Hassall" <andy@andyh.co. uk> wrote in message
news:qqt0e01nff sucimnbip19klcg kp0lvjh0j@4ax.c om...[color=blue]
> On Sun, 27 Jun 2004 23:28:57 -0400, "Bruce A. Julseth"[/color]
<bruceaj@attglo bal.net>[color=blue]
> wrote:
>[color=green]
> >When I execute this SQL statement in my PHP code, I get an error
> >"File '.\Address.txt' not found (Errcode: 2)"
> >
> >$File = addslashes(".\A ddress.txt");
> >$SQL = "Load Data InFile \"" . $File . "\" into table addresses";
> >$result = mysql_query($SQ L) or die(mysql_error ());
> >
> >The file is located in the same directory as my .PHP file.[/color]
>
> Then then command you want would probably be LOAD DATA LOCAL INFILE -[/color]
note the[color=blue]
> LOCAL. Without it, I believe the path is relative to the database data
> directory. Or you need an absolute path and stick with LOAD DATA INFILE,
> assuming that the MySQL database is running on the same machine as the
> webserver.[/color]

I get an error message that this command (LOAD DATA INFILE....) is not
allowed with this version of MySQL. I'm using version 4.0.14-max-debug.
[color=blue]
>
> You can get the current directory with getcwd.
>
> http://uk2.php.net/manual/en/function.getcwd.php
>
> Or if you're not necessarily in that directory (perhaps in an include[/color]
file?)[color=blue]
> then dirname(__FILE_ _).
>
> http://uk2.php.net/manual/en/function.dirname.php
>
> I also remember there are security restrictions on usage of LOAD DATA[/color]
LOCAL[color=blue]
> INFILE that depend on MySQL version and compile-time options (possibly
> changeable in my.cnf?). See the manual for more information.
>
> http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
> http://www.mysql.com/news-and-events...000000012.html
>
> --
> Andy Hassall <andy@andyh.co. uk> / Space: disk usage analysis tool
> http://www.andyh.co.uk / http://www.andyhsoftware.co.uk/space[/color]

[Andy Hassall]

Re: Using MySQL Command Load Data Infile

On Wed, 30 Jun 2004 13:37:17 -0400, "Bruce A. Julseth" <bruceaj@attglo bal.net>
wrote:
[color=blue]
>I get an error message that this command (LOAD DATA INFILE....) is not
>allowed with this version of MySQL. I'm using version 4.0.14-max-debug.[/color]

That's why I posted:
[color=blue][color=green]
>> I also remember there are security restrictions on usage of LOAD DATA
>> LOCAL INFILE that depend on MySQL version and compile-time options (possibly
>> changeable in my.cnf?). See the manual for more information.
>>
>> http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html
>> http://www.mysql.com/news-and-events...000000012.html[/color][/color]

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