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 :P

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?