PHP To Process a CSV and Update A Table

[j.smith1981]

Hi there, I tried posting this question previously, but for some reason it wouldnt post it.

 

Is there any basic tutorial or guidelines you can give me for the following problem:

 

I am wanting to download a CSV from an ftp site yea?

 

I can get the Linux shell to actually do the downloading to say /home/user1/download/

 

Then I want to start off a php application (probably a script in the shell), to basically go through say it has 3 columns yea?

 

Supplier code | Our Code(SKU) | Price |  QTY

Product1        |  OU_Product1  |  20        25

 

 

It finds Product1 entry in the database, realises the price is set to say 10 previously, then updates it with the new price.

 

Carries on for say 700 products, updating both the 'Price' and the Qty, if it finds any new ones, just ignores them.

 

Is there any way of doing this in PHP please?

 

Thanks in advance for any help,

Jeremy.

[developerdave]

I'm not entirely sure why you would want to do this all via the shell. But hey ho, You should hit up the documentation for PHP's function passthru();

 

Its exactly like exec(); but you get a second parameter which is the shell output.

 

I guess your best bet is to foreach an SQL query and check the results against the shell output.

(This code wont fix it its just a representation of my idea)

 

$sql = mysql_query("SELECT * FROM PRODUCTS");
$results = mysql_fetch_array($sql);
array_pop($results);

foreach($results as $record)
{
      passthru('however you plan to read your csv',$r);
      if($r == $record['column'] or $r == $record['column'])
            mysql_query("UPDATE TABLE PRODUCTS blah blah");
}

[j.smith1981]

Thats ace!

 

I will give it a go and fiddle around with it, be really good to get this working, would work allot quicker than what we do currently.

 

Thanks ever so much for that!

[j.smith1981]

I'm not entirely sure why you would want to do this all via the shell. But hey ho, You should hit up the documentation for PHP's function passthru();

 

Its exactly like exec(); but you get a second parameter which is the shell output.

 

I guess your best bet is to foreach an SQL query and check the results against the shell output.

(This code wont fix it its just a representation of my idea)

 

$sql = mysql_query("SELECT * FROM PRODUCTS");
$results = mysql_fetch_array($sql);
array_pop($results);

foreach($results as $record)
{
      passthru('however you plan to read your csv',$r);
      if($r == $record['column'] or $r == $record['column'])
            mysql_query("UPDATE TABLE PRODUCTS blah blah");
}

 

I just wanted to update you on my progress, I think from the example you have given is a good way of doing it but I was meaning sorry from the commandline in just testing the functionality of this script.

 

This what I have played around with for a bit is as follows:

 

$row = 1; // Variable named row with a value integer of '1'

if (($handle = fopen("pricelist.csv", "r")) !== FALSE) {
while(($data = fgetcsv($handle, 0, ",")) !== FALSE) {
	$num = count($data);
	echo "Select row: $row\n";
	$row++;
}
}

 

I took this from the php site and just did it the way I want it, all it does is count up the number of rows (wanted to make sure this worked with setting the limit I think it is to 0) in the fgetcsv() function.

 

The general idea is that, I was wanting to run this from a crontab every hour of every day of the week, so it just does this automatically, then keep an eye on it just to see how long it takes to do this products update system.

 

Any ideas of how to get it to select a specific column though in advance?

 

Just for thoughts really.

 

I look forward to anyones reply in advance,

Jeremy

[litebearer]

to get a specific column, explode each line using the pipe as the delimiter. You then have an array where (using your example) element 0 is the code, element 1 is the sku etc etc

 

not sure it that is what you are looking for

[j.smith1981]

to get a specific column, explode each line using the pipe as the delimiter. You then have an array where (using your example) element 0 is the code, element 1 is the sku etc etc

 

not sure it that is what you are looking for

 

I know its been over 30 days since I replied to this but this is direct to this topic and dont want to spam the forum with 2 threads regarding this problem.

 

What I was looking for is some way of updating product quantity's, I will start with a max of 6 products just to see how it behaves.

 

But it goes in there, finds a productcode say BRLC02BK, which has say a quantity of 6 and a price of (off the top of my head) £6.

 

Thats within the CSV file (a row as it where in the CSV file).

 

What I want it to do, is look up our values, in some form of X reference, where it finds BRLC02BK, but then finds the manufacturer part code for that product which is LC02BK (cant use some kind of substring function since these are of varying length the prefix to the start of the manufacturing part code as it where).

 

So in a nut shell.

 

Locates BRLC02BK.

 

Finds BRLC02BK in a table row of the xreference table.

 

Uses the LC02BK value and then applies the correct quantity and price of that.

 

Also for some kind of error handling, any rows in the CSV file it cant apply to be in a report for me to organise some implementation as to why its not being applied or if its my own error in the xreference table, just for information gathering.

 

But thats another part of the problem so its not entirely essential at the moment.

 

Just wanted to get the jist of whats to come here.

 

I'd appreciate any replies to be really honest, would be quite interesting and will post any modifications to help other people out.

 

Kind regards and I look forward to any replies,

Jeremy.