Page 1 of 3

Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 12:30 pm
by bidntrade
i have a csv file ....

one of the fields is :

product_id


my database has a products
with a field product_id


i would like to check my database for
product_id that is in the database but
not listed in the csv file ...

and then delete those from the database
since they are not in the csv file ....


i have an xml file available also with the same info
if it would be easier to get it from there ....


anyone have any idea what to do ?

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 12:35 pm
by VladSun
Load the CSV into a temporary table and use "plain" SQL queries ;)

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 12:50 pm
by bidntrade
VladSun wrote:Load the CSV into a temporary table and use "plain" SQL queries ;)

example please .... Im very new to php ....

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 1:08 pm
by VladSun

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 1:19 pm
by bidntrade
VladSun wrote:No PHP involved. Pure SQL.

http://dev.mysql.com/doc/refman/5.0/en/mysqlimport.html

yea i need to do this auto 2 times a day ... from a cron ...
so it needs to be php ....


the csv is a feed from a supplier .....
and i want to remove discontinued products from my database..

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 1:30 pm
by VladSun
bidntrade wrote:yea i need to do this auto 2 times a day ... from a cron ...
so it needs to be php ....
No, it really doesn't - Bash is far more appropriate than PHP in this case.
I'm not going to write it for you, but I am truly willing to help you solving your own script issues.

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 1:38 pm
by bidntrade
VladSun wrote:
bidntrade wrote:yea i need to do this auto 2 times a day ... from a cron ...
so it needs to be php ....
No, it really doesn't - Bash is far more appropriate than PHP in this case.
I'm not going to write it for you, but I am truly willing to help you solving your own script issues.

dont have Bash...

no ssh account....

i really have no idea where to start

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 1:42 pm
by VladSun
If you are able to start your MySQL server with --local-infile=1 then you can import you CSV directly into a temporary table.
If you can't the you will need to parse the CSV file by using http://us3.php.net/manual/en/function.fgetcsv.php.

Try them ...

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 1:54 pm
by bidntrade
this is the code im using right now to update the prices...

now i just want to delete the products from my database that
does not exist in this csv...

Code: Select all

$link = mysql_connect($mysqlHost, $user, $password) or die('Could not
connect: ' . mysql_error());
//$handle = fopen("DataFeed.csv", "r");
 
$handle = fopen("DataFeed.csv", "r");
 
 
mysql_select_db($database, $link);
 
 
// loop content of csv file, using comma as delemiter
while (($data = fgetcsv($handle)) !== FALSE) {
 
 
 
$id = (int) $data[0];
$orgprice = floatval($data[2]);
$weight = floatval($data['15']);
$qty = floatval($data[8]);
$title = $data[1];
 
 
 
if($orgprice <= 4) { 
$price = ($orgprice * 3);
}else if ($orgprice <= 10) {
$price = ($orgprice * 2);
}else if ($orgprice <= 35) {
$price = ($orgprice / 0.70);
}else if ($orgprice <= 45) {
$price = ($orgprice / 0.76);
}else if ($orgprice <= 150) {
$price = ($orgprice / 0.80);
}else if ($orgprice <= 250) {
$price = ($orgprice / 0.83);
}else{
$price = ($orgprice / 0.85);
}
 
$price = round($price, 2);
 
 
if($go){
 
$query = 'SELECT cscart_products.product_id, cscart_product_prices.price, cscart_products.amount FROM cscart_products LEFT JOIN cscart_product_prices USING (product_id) WHERE product_code = ' . $id . ' AND cscart_products.weight ='.$weight . ' LIMIT 1';
 
if (!$r = mysql_query($query)) { die(mysql_error()); }
 
// IF MYSQL NUM ROWS IS ZERO, IT IS THE SAME AS FALSE; IF ONE IT IS TRUE
if (mysql_num_rows($r)) {
 
 
$getr = mysql_fetch_row($r);
 
if(($getr[1] != $price) OR ($getr[2] != $qty)){
 
// entry exists update
if (mysql_query("UPDATE cscart_product_prices PP LEFT JOIN cscart_products PI USING (product_id) SET PP.price = '$price', PI.amount = '$qty' WHERE PI.product_code = '$id' AND PI.weight = '$weight'")){
 
////////////////////////////////////////////////////////////////////////////////////
// this section is inactive . only for adding condition new if forgot .... Updates all products the same
/*
$fixcondition = 'SELECT product_id FROM cscart_products WHERE product_code = '.$id.' AND weight = ' . $weight;
$getid = mysql_query($fixcondition);
$row = mysql_fetch_row($getid);
$prid = $row[0];
if($prid){
mysql_query("INSERT INTO cscart_product_features_values (feature_id, product_id, variant_id) VALUES('9','$prid','24' ) ") or die(mysql_error());  
mysql_free_result($row);
}
*/
////////////////////////////////////////////////////////////////////////////////////
if($getr[1] > $price){
$updown = " -- ";
$diff = $getr[1] - $price;
}else{
$updown = " + ";
$diff = $price - $getr[1];
}
 
 
$emailinfo .= "Product ".$id." Price Updated To ".$price. " Diff : ".$updown." ".$diff. " /  Stock= " . $qty . " .\n";
//echo "Product ".$id." Price Updated To ".$price. " Stock= " . $qty . " .<br>";
}
 
// check prices end
}
 
mysql_free_result($r);
} else {
   // product not in database not found
$nothere .= "Product ".$id."  Title : ". $title . "  -  Stock= " . $qty ."\n";
//$nothere .= "#".$weight."#\n";
}
 
}
 
 
 
$go='1';
 
}
 
fclose($handle);
mysql_close($link);
 
# set to the date and time the script was run
$runtime = (date("d M Y H:i"));
 
if(!$emailinfo){
$emailinfo = "None Updated\n";
}
 
# add the run time to the body of the email message
$message .= "Products That Was Updated :\n";
$message .= "---------------------------------------------------------------------------------------------------\n";
$message .= $emailinfo;
$message .= "---------------------------------------------------------------------------------------------------\n";
$message .= "Products That Are Not Found On Our Site :\n";
$message .= "---------------------------------------------------------------------------------------------------\n";
$message .= $nothere;
$message .= "\n\n--------------------------------------------------------------------------------------------";
$message .= "\nTime of the message: $runtime (server time zone)\n\n";
 
# contains the email address you want the results sent to
$emailaddress = "kdjfkjdkf@fdmkfjdk.com";
 
# contains the subject you want the message to have
$subject = "Daily Software Products Update";
 
# contains the email address that will show in the from line
$emailfrom = "hsdjshdsh@dksjdksj.com";
 
# Send the email message
mail($emailaddress, $subject, $message, "From: '$emailfrom'");
 

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 2:03 pm
by VladSun
OK, by using a very similar script (and far simplier), create a temporary SQL table, fill it with the IDs parsed from the CSV and then execute an SQL query that deletes from the main table all records that have no corresponding IDs in the temporary table.

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 9:25 pm
by bidntrade
well i can compare the temp table like this to get rid of orphans.

Code: Select all

DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product);
problem is i can not use something this simple ...

i cant compare all the products in cscart_products since they come from different suppliers.

the ones that i want to run the compare on all start with XSD in the product_code field....
example :
XSD1234
XSD2345

I have other products that have No Prefixs and Some With other Prefixes ...
I dont want those deleted ... Only the XSD ones that are not in the temp table .

I have no idea how to do this ....

and to complicate things a little

when i delete i need to also delete a couple other entries in other tables that are linked by Product_id maybe using a left join or something ....

can someone please Help

Re: Help - csv and mysql database compare ?

Posted: Thu Jul 30, 2009 10:08 pm
by bidntrade
would something like this work ?

Code: Select all

DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND substring(product_code,1)='XSD';
any idea if this would work ?

i hate to try it on my live database ....

Re: Help - csv and mysql database compare ?

Posted: Fri Jul 31, 2009 1:05 am
by VladSun
bidntrade wrote:would something like this work ?

Code: Select all

DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND substring(product_code,1)='XSD';
any idea if this would work ?

i hate to try it on my live database ....
I don't know, becuase I don't know nothing about your DB design.

"Clone" your original table and try it on it:
http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html

Re: Help - csv and mysql database compare ?

Posted: Fri Jul 31, 2009 1:08 am
by VladSun
bidntrade wrote:i cant compare all the products in cscart_products since they come from different suppliers.

the ones that i want to run the compare on all start with XSD in the product_code field....
example :
XSD1234
XSD2345

I have other products that have No Prefixs and Some With other Prefixes ...
I dont want those deleted ... Only the XSD ones that are not in the temp table .
Sound like you need to normalize your DB design.
It should be like this:
- no prefixes/suffixes, just a numerical product ID;
- additional numeric fields - "supplier_ID" (foreign key);
- additional table "suppliers" with two (at least) fields - "ID", and "name"

Re: Help - csv and mysql database compare ?

Posted: Fri Jul 31, 2009 8:28 am
by bidntrade
VladSun wrote:
bidntrade wrote:would something like this work ?

Code: Select all

DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND substring(product_code,1)='XSD';
any idea if this would work ?

i hate to try it on my live database ....
I don't know, becuase I don't know nothing about your DB design.

"Clone" your original table and try it on it:
http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html

ok ....


i have a table : cscart_products

it has many fields ....

id,product_code,weight,price, etc etc etc

i also have table . temp_product

with 1 field called product_code ....

in the table cscart_products
i have many products with all different product_codes ....
XSD1234
XSD5621
ERT3456
HHH2356
XSD8475

i only want to affect those with XSD in the product_code.

so if the table temp_product contained these

XSD1234
XSD8475

if i run this

Code: Select all

DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND substring(product_code,1)='XSD';
would i only be left with this
XSD1234
ERT3456
HHH2356
XSD8475

thats what i would like the outcome to be