Help - csv and mysql database compare ?
Moderator: General Moderators
Help - csv and mysql database compare ?
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 ?
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 ?
Load the CSV into a temporary table and use "plain" SQL queries 
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
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 ?
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
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 ?
No, it really doesn't - Bash is far more appropriate than PHP in this case.bidntrade wrote:yea i need to do this auto 2 times a day ... from a cron ...
so it needs to be php ....
I'm not going to write it for you, but I am truly willing to help you solving your own script issues.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
VladSun wrote:No, it really doesn't - Bash is far more appropriate than PHP in this case.bidntrade wrote:yea i need to do this auto 2 times a day ... from a cron ...
so it needs to be php ....
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 ?
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 ...
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 ...
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
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...
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 ?
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.
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
well i can compare the temp table like this to get rid of orphans.
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
Code: Select all
DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product);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 ?
would something like this work ?
any idea if this would work ?
i hate to try it on my live database ....
Code: Select all
DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND substring(product_code,1)='XSD';i hate to try it on my live database ....
Re: Help - csv and mysql database compare ?
I don't know, becuase I don't know nothing about your DB design.bidntrade wrote:would something like this work ?
any idea if this would 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';
i hate to try it on my live database ....
"Clone" your original table and try it on it:
http://dev.mysql.com/doc/refman/5.0/en/ ... elect.html
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
Sound like you need to normalize your DB design.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 .
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"
There are 10 types of people in this world, those who understand binary and those who don't
Re: Help - csv and mysql database compare ?
VladSun wrote:I don't know, becuase I don't know nothing about your DB design.bidntrade wrote:would something like this work ?
any idea if this would 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';
i hate to try it on my live database ....
"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';XSD1234
ERT3456
HHH2356
XSD8475
thats what i would like the outcome to be