Help with UPDATE statement from CSV file

[charmed ones]

I have a small database that needs to be updated from csv file that is dropped on the server. We only need to update one field in each line that is a purchase date, matched to the employee number. If the employee number isn't in the database we just want to ignore the update. we need to run this as a cron every night. I can't seem to figure out the update statement. Any help would be great!

<?
$dbhost = 'localhost';
$dbuser = '#*$!#*$!';
$dbpass = '#*$!#*$!';
$dbname = '#*$!#*$!';
$dblink = 'account_id';

$dblink = mysql_connect($dbhost, $dbuser, $dbpass); 
mysql_select_db($dbname, $dblink); 

$csvfile = "/home/.sites/75/site8/web/chrysler/csv/DCB.csv";
$seperator = ",";
$lines = file($csvfile);
foreach($lines as $line) {
$rows = explode($seperator,$line);
$sql = "UPDATE account_id SET Purchase_Date = 'Purchase_Date' Where Emp_No = 'Emp_No'"; 
$result = mysql_query($sql, $dblink) or die("load -" . mysql_error()); 
}

?>

How far off base am I here?

[Matthew1980]

Hi there charmed ones,

Welcome to WebmasterWorld: [webmasterworld.com ]

Well the good news is that your pretty much there with the code, just remember that when defining a file as php, treat the tags as such <?php?> this will save headaches later on.

foreach($lines as $line) {
$rows = explode($seperator,$line);
$sql = "UPDATE account_id SET Purchase_Date = 'Purchase_Date' Where Emp_No = 'Emp_No'";
$result = mysql_query($sql, $dblink) or die("load -" . mysql_error());
}

The foreach loop - your accessing the file and reading it into an array (if you want a string, use file_get_contents()) the foreaching over the returned array, then your taking that result and taking each line an splitting that into another array using the ',' as the separator - then doing nothing with it? I would have thought that dumping the contents of $rows (using print_r($rows)) within the loop to see if you are getting the results you expect before updating.

The Sql statement is fine, but as you have static (fixed content) values nothing will change, I'm kinda hoping that you have done that just to ask if the syntax is correct...

All you need to do is find out the array value ($rows['0']) and see what values/numbers hold the content you are after, then you can take it from there..

Hope that's clearer now anyway.

Cheers,
MRb

[charmed ones]

HI Matthew,
Thank you for the welcome....and all I can say is DUH?
you lost me in all the wonderful infomration you gave me...but it means nothing at this point since I'm still learning all this PHP and SQL stuff.
As you can see I haven't got a clue when it comes to this with regards to php.
My table is
Account|Emp_No|First_Name|Last_Name|Purchase_Date|Dept|Location|id the Purchase_Date field is blank, and the csv file holds the same table format with the data.
What I'm trying to do is match the emp_no and if they match to update the purchase_date field.

now I'm sure you gave me the right answer before but I'm sorry you lost me. Been reading so much in the last week to try and get this to work and nothing does. this is the first php script I've ever written. as you can tell!
Thanks for you time

[Matthew1980]

Hi there Charmed ones,

Sorry if I lost you, it was getting on for 1am here, so I think I babbled on a bit ;-p

On this part:-

foreach($lines as $line) {
$rows = explode($seperator,$line);
echo "<pre>";
print_r($rows);// add this instruction to see what the result is from the array
echo "</pre>";
exit;//terminate the script, no need to run this part yet
$sql = "UPDATE account_id SET Purchase_Date = 'Purchase_Date' Where Emp_No = 'Emp_No'";
$result = mysql_query($sql, $dblink) or die("load -" . mysql_error());
}

Pop in the four lines as I have suggested, then if the returned array isn't too huge (the contents of the file) copy and paste an excerpt of that so we can see what's happening, take it from there so that you understand what's going on..

Cheers,
MRb

[charmed ones]

Morning Mattew,
I popped in the lines you asked and this is what was returned which is the headers of the table
Array
(
[0] => Account
[1] => Emp_No
[2] => First_Name
[3] => Last_Nmae
[4] => Purchase_Date
[5] => Dept
[6] => Location

)

Is that what you are looking for?

[Matthew1980]

Hi there Charmed ones,

Ok, good, at least you know that the open/get is going as it should.

Next remove the exit; from the four lines I mentioned, and comment out the sql stuff like this:-

foreach($lines as $line) {
$rows = explode($seperator,$line);
echo "<pre>";
print_r($rows);// this enables you to see the data returned from the file in array format
echo "</pre>";
//$sql = "UPDATE account_id SET Purchase_Date = 'Purchase_Date' Where Emp_No = 'Emp_No'";
//$result = mysql_query($sql, $dblink) or die("load -" . mysql_error());
}

This should now give you the whole file echoed to screen in array format, this is now where you need to look at how the data is referenced...

Once you have that referenced, you can then use that data to populate the sql statement and begin the update.

When all of this is sorted the sql will look something like this:-

$sql = "UPDATE `account_id` SET `Purchase_Date` = '".$rows['Purchase_Date']."' WHERE `Emp_No` = '".$rows['Emp_No']."' ";
$result = mysql_query($sql, $dblink) or die("load -" . mysql_error());

I have done that just to show you what the syntax will look like, the array indexes are what you need to know: $rows['THIS_DATA'] so that for each iteration of the loop, the lines will be processed and the relevant data will be inserted into the DB, simple really!

Hope that help's anyway, I'm off shopping now, but if you get stuck there are plenty of poeple here who can help ;)

Cheers,
MRb

[charmed ones]

Thank you Matthew it worked like a charm! I didn't know you had to reference the array number and not the header name.
thank you so much you helped a newbie out more then you know!

[Matthew1980]

Hi there Charmed ones,

Cool, glad your sorted, we all have to start somewhere, have fun with the rest of it.

Cheers,
MRb

I hate going shopping - but it's needed, there is no beer in the fridge :-0