Page 2 of 3
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 8:46 am
by VladSun
Clone and try it.
Also, the
[sql]AND substring(product_code,1)='XSD';[/sql] part is not needed because in your temp_product you have your IDs together with their suffixes.
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 8:52 am
by bidntrade
i have the substring part there because i dont want to delete
the ones that begin with something other than XSD
if they begin with say HHH i dont want it deleted even if its not in the temp_product table
is this not correct?
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:04 am
by VladSun
bidntrade wrote:i have the substring part there because i dont want to delete
the ones that begin with something other than XSD
if they begin with say HHH i dont want it deleted even if its not in the temp_product table
is this not correct?
In your case - yes, it's correct

Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:07 am
by bidntrade
i tried it ....
but if i leave the
AND substring(product_code,1)='XSD';
it does not delete anything
but if i remove that it make the temp_product table and the cscart_products table the
same as far as product_codes
i dont want them to be the same ...
i just want to remove the XSD ones that are not in the temp table ....
it removed even the HHH and all others
how can i do this .. this is driving me nuts
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:10 am
by VladSun
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:15 am
by bidntrade
cool this worked
Code: Select all
DELETE FROM cscart_products WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND LEFT(product_code, 3)='XSD';
Now if i just wanted to update say the cscart_products table rather than deleteing them ...
say there is a field called enabled (true or false )
could i do the same with a update statement .
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:20 am
by VladSun
bidntrade wrote:Now if i just wanted to update say the cscart_products table rather than deleteing them ...
say there is a field called enabled (true or false )
could i do the same with a update statement .
Yes, you can
Use the same WHERE clause and write it yourself

Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:21 am
by bidntrade
im thinking maybe
Code: Select all
UPDATE cscart_products SET enabled=0 WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND LEFT(product_code, 3)='XSD';
seems to work ok ....
is this correct
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:23 am
by VladSun
bidntrade wrote:im thinking maybe
Code: Select all
UPDATE cscart_products SET enabled=0 WHERE product_code NOT IN (SELECT product_code FROM temp_product) AND LEFT(product_code, 3)='XSD';
seems to work ok ....
is this correct
Seems correct

Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:30 am
by bidntrade
one last thing and i thank you for your guidance
if i want to import a csv file to the temp_product table .....
but i only want to import just the product_code since this
is all i need for my purpose.... and this needs to be done
from the php script.
my script im using this in is already looping through the csv file
one at a time .... the csv has about 4000 items ....
would it be better to do like
INSERT INTO temp_product (product_code) VALUES ($id);
this would only insert one at a time into the database . seems like a big load all those inserts...
or is there another way that i could do it more effective ..
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:35 am
by VladSun
You'd better construct a single INSERT query and pass it to the SQL server.
An INSERT query for multiple values looks like this:
[sql]INSERT INTO my_table(field1, field2 ...)VALUES('1.1', '1.2'),('2.1', '2.2'),.......('N.1', 'N.2')[/sql]
and if you don't need any of the other fields except ID - go ahead, skip'em

Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:45 am
by bidntrade
i only need the ID
but the csv file contains 4000 IDS
so how would i extract them from the csv and form a singe insert....
dont see how .....
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:52 am
by VladSun
bidntrade wrote: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'");
bidntrade wrote:dont see how .....
?!? You wrote the whole script and can't modify it to insert ID only?!? Shame on you
Good
pytrin
http://www.techfounder.net/2009/05/14/m ... mysql-php/
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:58 am
by bidntrade
i was think of adding a insert here
Code: Select all
// 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];
mysql_query("INSERT INTO temp_product (product_code) VALUES('$id') ") or die(mysql_error());
but is this not going to execute the insert 4000 times .....
is this going to cause a huge load on the server ......
Re: Help - csv and mysql database compare ?
Posted: Fri Jul 31, 2009 9:59 am
by VladSun
Did you take a look at pytrin's blog I posted in my previous post?