how to update a mysql table from a file??

[halfasleeps]

Im am trying to set up a data-integration with a diamond vender so that I can make a diamond search application.

they said they can place a file on my server via ftp everyday in any format I want with the data.

so what I want to do is make a table on my DB to hold this data and make a script that can take that file and update the entire table with all the information from the file.

the problem is I dont know how to make a script to do that or what kind of file I would need.

If anyone can shed any light on this I would really appreciate it.

(By the way there will be around 40,000 diamonds/rows passed)

THANKS.

[_Steve_]

One possible simple format would be an entry one line, with values (fields) separated by comma like a csv file.
Then parsing is easy, you loop each line, explode it & construct the query.
But. Data entry from users is always prone to errors, because users don't have technical abilities. I would suggest using a small data entry application, which guides the user to successfull data entry.

[halfasleeps]

@steve I am not sure what you mean?

the data is coming from the diamond venders data-base I am sure. I don't think anyone is actually typing up 40,000 diamonds each day. I am sure they can give me a .csv file, they said any format I want.

the problem is I don't know how to make a script that can take the csv or whatever type of file and replace the information in my database with the information from the file.

if there is a way I can just select the information from the file like I would from a data-base that would work too and I wouldnt need to add it to the data-base, but I am not sure if there is a way to do that.

[_Steve_]

OK, I thought that they will add some rows each day and the database is already 40K.

Then ask for a format with each row in a line,separated with commas and string data quoted.

Then parsing would be:

<?
$fp = fopen($pathtofile,'r');
$line ='';
$data_arr = array();
while (!feof($fp)) {
    $line = fgets($fp);
    $tmp_arr = explode(',', $line);
    array_push($data_arr, $tmp_arr);
    $tmp_arr = array();
        }
fclose($fp);
?>

This will give you a 2-dimensional array which you can loop and add data to your database.

[halfasleeps]

thank you, the data is always changing because other wholesalers might have sold diamonds and what not so its always going to wipe clean and reinsert the diamonds usually around 40,000


ok so if my table is like this:

Shape | Carat | Cut | Price | Stock_num

I should just get a text file and it should look like:

"round","1.0","good","5000","3dfss45"
"princess","0.96","excellent","7000","dfg56gt"
"marquise","1.06","fair","2000","gdfgdff"
"oval","2.0","poor","6000","et5g54trt"

Please specify.

also if it is a loop of 40,000 will it be ok or crash?


Thank you soo much for your help.

[halfasleeps]

I just built a sample to test it out and it works great THANK YOU SO MUCH!!!

i thought it would be alot more code than that but thats pretty simple.

[php_daemon]

Some notes,

There's more to csv than just commas, so I'd suggest using fgetcsv to read it.

As far as the size, 40k is not that much and running it on daily basis won't crash it. But if you do not need any pre-processing, you probably will not want to read it into memory (array), rather just add to database as you read from file.

[r937]

another possibility is to simply load the incoming csv file into a table

advantage: 1 command



the only reason you would loop over the individual rows and construst an INSERT statement for each one would be because you needed to do editing, transformations, error-correction, and so on (if this is not needed, the LOAD is definitely more efficient)

once you have uploaded the file to a "holding" table, you can do editing, transformations, error-correction with simple SQL queries, including saving clean results to your application's tables

[r937]

I am sure they can give me a .csv file, they said any format I want.

everybody can do csv, so start with that, but if they can do xml, that's a stronger option

[halfasleeps]

hmm, I was jsut using a .txt file with my test, would it be better to use a csv file?

i dont think anything needs to be updated, I just need to remove all fields from data base and then reinsert the data.

Is there a way to remove all rows from the table before I insert the new rows?

thanks guys

[halfasleeps]

everybody can do csv, so start with that, but if they can do xml, that's a stronger option

yes they said they can do xml, how would i code it to take the data from the xml and insert it into the db?

Thanks.

[Hammer65]

If no altering of data needs to be done before inserting it, I would recommend truncating the table, then using the INFILE features of MySQL (I assume that's what you are using) to get the data into the database.

[_Steve_]

Some notes,

There's more to csv than just commas, so I'd suggest using fgetcsv to read it.

As far as the size, 40k is not that much and running it on daily basis won't crash it. But if you do not need any pre-processing, you probably will not want to read it into memory (array), rather just add to database as you read from file.

I'm knit-picking but, without using arrays first (memory) you get a hold of the file much longer (locked) + you hold the connection object longer which in turn doesn't let you run an optimized insert (INSERT INTO tbl VALUES (),(),(),()...() <-- semi-colon

[php_daemon]

I'm knit-picking but, without using arrays first (memory) you get a hold of the file much longer (locked) + you hold the connection object longer which in turn doesn't let you run an optimized insert (INSERT INTO tbl VALUES (),(),(),()...() <-- semi-colon

Point taken, I asked for it.

[halfasleeps]

ok guys I finally got the data-feed set up and when I run the script I get this:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 35 bytes) in /home/httpd/vhosts/sussexjewelers.com/httpdocs/diamond_data/get_data.php on line 9

line 9 is:
$tmp_arr = explode(',', $line);

Please advise on what I should do now?

[halfasleeps]

ok i did it all in the first callout without loading the content into one large array first and it worked.