Help - csv and mysql database compare ?
Moderator: General Moderators
Re: Help - csv and mysql database compare ?
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.
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.
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 ?
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?
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 ?
In your case - yes, it's correctbidntrade 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?
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 ?
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
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 ?
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 ?
cool this worked
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 .
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 ?
Yes, you canbidntrade 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 .
Use the same WHERE clause and write it yourself
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 ?
im thinking maybe
seems to work ok ....
is this correct
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 ?
Seems correctbidntrade 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
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 ?
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 ..
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 ?
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
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
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 ?
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 .....
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 ?
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'");
?!? You wrote the whole script and can't modify it to insert ID only?!? Shame on youbidntrade wrote:dont see how .....
Good pytrin
http://www.techfounder.net/2009/05/14/m ... mysql-php/
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 ?
i was think of adding a insert here
but is this not going to execute the insert 4000 times .....
is this going to cause a huge load on the server ......
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 ?
Did you take a look at pytrin's blog I posted in my previous post?
There are 10 types of people in this world, those who understand binary and those who don't