Update DB from CSV and send mail

[mozack]

Hi,

 

I'm trying to create a script that allow me to update my product quantity from external csv file. I've done successfully the first part, update db.

 

But now i want to send one mail with results that are not in db. I check DB from ean, and update product quantity by ean. Sometimes, the csv file have some ean that are not in db, i want to receive one e-mail with this ean's.

 

Here is my code:

 

<?php // Connect to MySQL mysql_connect("localhost", "username", "password") or die(mysql_error()); mysql_select_db("dbname") or die(mysql_error()); #if first row of csv file is headings set $row to 1. $row = 1; #database primary table $table_to_update = "table_name"; #get the csv file $handle = fopen("http://www.csvfilewebsite.com/file.csv", "r"); #go through the csv file and print each row with fields to the screen. #and import them into the database updating only the price and quantity  while (($data = fgetcsv($handle, 100000, ";")) !== FALSE) {     $num = count($data);     echo "<p> $num fields in line $row: <br /></p>\n";     $row++;     for ($c=0; $c < $num; $c++) {         if ($c = 1) {         $ean13 = $data[($c - 1)];         echo $ean13 . " SKU Assigned <br />\n";         }         if ($c = 3) {         $quantity = $data[($c - 1)];         mysql_query("UPDATE $table_to_update SET quantity='$quantity' WHERE ean13='$ean13'")          or die(mysql_error());          echo $product_quantity . " Imported for row $row in product $ean13 <br />\n";         }         // would have to add an additional  if statement for each field being updated and know the order of the fields from your csv file         //echo $data[$c] . "Imported <br />\n";                       } } fclose($handle); echo "<h1>Update Complete.</h1>"; ?> 

 

 

Now, how to check csv ean's that aren't in db, and send mail with this ean's?

 

Hope someone can help me.

 

Regards

 

Mozack

[schilly]

do a select and see if the ean is in the db. if is it update the record quantity, if not add to a debug string. at the end mail out the debug string.

[mozack]

Ok,

 

Thanks for your help... I'll try it.

[mikosiko]

this should help you to figure out the ean's that aren't in db

http://php.net/manual/en/function.mysql-affected-rows.php

 

only problem with this could be:

 

When using UPDATE, MySQL will not update columns where the new value is the same as the old value. This creates the possibility that mysql_affected_rows() may not actually equal the number of rows matched, only the number of rows that were literally affected by the query.

 

evaluate if  the last is a certain possibility (quantity = '$quantity') before decide to use mysql_affected_rows()... your alternatives then could be:

- Do a select for each element in your file before the update (I don't like this) or

- Store your file ean's in an array

- Use the generated array to construct an INSERT and insert those file ean's in a temporary table (memory) and JOIN it with your real table to obtain the missing ean's... (means 1 INSERT and 1 SELECT no multiples as in the first case)

- generate the mail from this result.

[mozack]

Thanks to all of you, i'll try the best way to do that.

 

Thanks a lot

 

Regards Mozack