can i use LOAD DATA INFILE with an online file

[designergav]

Hi
I've been trying without success to read/parse either an xml or csv file convert to text and insert into mysql. BUT now I'm trying LOAD DATA INFILE to bypass the whole reading the file bit. Sure it adds some crap to the table but I can just ignore and use the bits I need.

I'm using this code:
[code]
<?php
// connect to database
$connect = mysql_connect("host", "user", "password") or
die  ("Mysql connection error");

mysql_select_db('databasename')
  or die('Cannot connect to database');

// clear the table down
$sql = "TRUNCATE TABLE tablename";

// run the first query to clear table
mysql_query($sql) or die('Error truncating existing');

// set up query to import data
$sql = "LOAD DATA INFILE 'myfile.csv' INTO TABLE 'tablename' FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ESCAPED BY '\\' LINE TERMINATED BY '\r'";
// run the query to load the data
mysql_query($sql) or die('Error loading new');
?> 
[/code]

I am always replacing the contents so the script empties the table first. This part works but the problem is it doesn't put the new data in. Running the same query in phpmyadmin and choosing the file on my machine works fine. Which leads me to believe that I have a problem referencing the file.

How to I refer to a file that will only exist as a webpage/url ie. http://www.site.com/myfile.csv

I'm trying to elimenate the manual part of the process so I can set this as a schedule to run daily without any human interaction.

I've been trying to get this running since last year so any help would be most welcome.
Ta
DesignerGav

[fenway]

I don't think you can... it has to be local... not that you couldn't run a quick shell script to make that happen.

[designergav]

I've got a script to read my csv file and I've run some str_replace to get the formating required to insert into mysql table.

The problem is i get an error inserting. I think it is because there is extra text at the start that doesn't match my insert command. I'm inserting this this:
[code]
$sql = "INSERT into currencies (csymbol, cname, crate) VALUES $singlequotes";
[/code]
where $singlequotes is my csv string. The first part of the string contain info I don't need and may be causing the INSERT to fail. It ends with:

('Days until new access period begins','1'),('UTC Timestamp','2005.01.09 17:00:00'),('Local Timezone','GMT'),('Local Timestamp','2005.01.09 17:00:00'),('Base Currency','GBP'),

How can I remove this beginning of the string so that it start with the next bit:
('AED','United Arab Emirates Dirhams','6.8712288192'),('AFA','Afghanistan Afghanis','80.0505170552'),('ALL','Albania Leke','180.5912341387')

The problem is that the text in the first part will change each time I run the script, although the format is always the same.

I'm still fairly new to php so any help would be great.
Thanks
DesignerGav

[designergav]

I've now got a script to read a csv file and insert the info into a mysql table

The file I'm reading for testing is in the same web directory as the script and this works file:
[code]
$filename = "/filetoread.csv";
$fd = fopen ($filename, "r");
$contents = fread ($fd,filesize ($filename));
[/code]

[fenway]

So it does work/

[designergav]

Well yes and no. It works perfectly when the file I'm reading is in the same directory as the php file.
The proble is that when this goes live the file being read will be located at a specific url hosted somewhere completely different.

How do I reference the location of the file if it is at http://www.fileshere.com/csvfiles/myfile.csv?username=me&password=me

When using a test location I get a gci application error could not complete http headers.

[fenway]

I'm pretty sure that you can't... it's a security issue.

[designergav]

AH.

So is there any way around this. Here's the challenge.

I will have access to a currency feed supplied as a csv file. I can only access this file once a day and need to put the contents into a mySQL table. This needs to be done every day so obviously I'm trying to cut out any human interaction.

The plan was to use the script I created to read and insert and use cronjob or windows scheduler to run this file once a day. It's taken 3months to get to here.

So any ideas for PLAN B.
DesignerGav

[fenway]

Well, I think I mentioned before that you can simply pull this file from wherever and dump it on the server, provided you have the right credentials on that box.  Alternatively, why not just read the CSV file into PHP, parse it, and make INSERT statements yourself?

[designergav]

Umm. Isn't that what my script does?
Its reads and inserts just fine it the file I'm reading is in the same directory as the script. My problem is that iit won't be. I guess I just need to find a way of grabbing/downloading  to my server the file I'm trying to read and use that location as the source for the script.

Problem is won't I need to start messing around with apache and htaccess to allow write access to the folder i need to store the file in. My (client's) host doesn't allow access to these files or to mess around with their servers.

Don't forget I'm still fairly new to php and I've never touched Apache. So I'm learning as I go.

I'm tantalisingly close to finishing this but the last step is a bit tricky.
Thanks for your help so far. 

[fenway]

No, it looks like your script is trying to bulk insert.

[designergav]

Ooh. Maybe I need to update the code. Here's the latest file.
[code]
<?PHP include ("dbaccessfile.php");
$filename = "mycsvfile.csv";
$fd = fopen ($filename, "r");
$contents = fread ($fd,filesize ($filename));
fclose ($fd);

$delimiter = ",";
$splitcontents = explode($delimiter, $csvfile);
$counter = "";

//remove any single quotes
$noquotes = str_replace("'", "", $csvfile);
//remove linebreaks and replace with ),(
$linebreak = str_replace("\r", "),(", $noquotes);
//replace " with '
$singlequotes = str_replace("\"", "'", $linebreak);
$singlequotes = $singlequotes."'end','end','end";
//remove everything before 1st currency
$trimmed = strstr($singlequotes, "AED");
//print "<H1>trimmed</h1><p> $trimmed";

//DELETE EXISTING DATA
$sql = "TRUNCATE TABLE mytable";
$result = mysql_query($sql)
or die(mysql_error());

//INSERT currencies into DB
$sql = "INSERT into currencies (csymbol, cname, crate) VALUES ('$trimmed"."')";
$result = mysql_query($sql)
or die(mysql_error());
if (!$result) {
print "<p><h3>Error inserting data</h3>";
} else {
print "<p><h3>CURRENCY UPDATE DONE!!</h3>";
}
?>
[/code]

[fenway]

Ah... then you're asking how to load in a remote file?  That's a PHP issue, I have no idea.