MySql Statement

[speed2]

Hi guys,

 

One of my manufacture decide to do a 1 month special for all of his product.

HOw do I update my DB with mysql statement?

 

So let say Manufacture id 1 is offering all of his product for $16.99 starting Dec 20, 2004 to Jan 20, 2005

 

how do I this???

 

thank you in advance

[Rob123]

Do you want to simply reduce your prices by a certain percentage?

OR

Do you want to create specials with reduced prices?

 

I'm asking because it's two different statements that affect different tables.

 

Robert

[speed2]

HI Robert, I just want to offer special discount price only for a month or 2. how do I do that???

 

starting today, Dec 20, 2004 to Jan 20, 2005 for a Manufacture_id = 2

[Rob123]

Ok... I made an assumption that you want to reduce your prices by 10%. (Note that in the code below there is a .9 (1 - .10) . A 5% reduction would be .95

 

Required disclosure:

Back up your database first then run this in your favorite SQL admin tool. It is also suggested that you run this against a test dB first.

 

Step 1 (Optional)

Delete all of your existing specials.

delete from specials;

 

Step 2 (Required)

Run the following "Insert Select statement"

INSERT specials (products_id, specials_new_products_price, specials_date_added, expires_date, status) SELECT products_id, products_price * .9 , now(), '2005-01-20', '1' from products where manufacturers_id = '2';

 

Once this is complete you can look in your admin and view your specials....

Administration > Catalog > Specials

 

Let me know how it goes,

Robert

[speed2]

Hey robert, thank you very much, by running your step 1, it will remove all the special (Including other manufature specials)

How do I remove this particular manufactures special first ????

 

 

 

Ok... I made an assumption that you want to reduce your prices by 10%.  (Note that in the code below there is a .9  (1 - .10) .    A 5% reduction would be .95

 

Required disclosure:

Back up your database first then run this in your favorite SQL admin tool.  It is also suggested that you run this against a test dB first.

 

Step 1 (Optional)

Delete all of your existing specials.

delete from specials;

 

Step 2 (Required)

Run the following "Insert Select statement"

INSERT specials (products_id, specials_new_products_price, specials_date_added, expires_date, status) SELECT products_id, products_price * .9 , now(), '2005-01-20', '1' from products where manufacturers_id = '2';

 

Once this is complete you can look in your admin and view your specials....

Administration > Catalog > Specials

 

Let me know how it goes,

Robert

<{POST_SNAPBACK}>

[Rob123]

Hey robert, thank you very much, by running your step 1, it will remove all the special (Including other manufature specials)

How do I remove this particular manufactures special first ????

<{POST_SNAPBACK}>

 

Ah... you're asking a trick question here. You're asking how to use mySQL to perform a delete with a subquery or a join.

mySQL versions before 4.1 could not support this. Posting a solution that may or may not work depending on your version wouldn't be right (for you and future board readers).

 

I do not have access to my servers right now. But later today, I'll whip-up a php solution for you that works with most (maybe all) mySQL versions.

Simply copy and paste the code that I will post, save it in your admin folder and run it.

 

Robert

[Rob123]

Ok... here it goes.

 

Copy the code below and paste it into a brand new file called wheteveryouwant.php.

Modify $manufacturers_id = "2"; to the appropiate manufacturers_id

 

FTP it to your site in the admin folder.

 

Browse to the file www.yoursite.com/admin/whateveryouwant.php and voila (in theory at least)

 

<?PHP
 require('../includes/configure.php');
 function db_connect() {
   global $db_link;
   @$db_link = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
   if ($db_link) @mysql_select_db(DB_DATABASE);
   return $db_link;
 }
 db_connect() or die('Unable to connect to database server!');


 $manufacturers_id = "2";
 
 $query = mysql_query("select * from specials  left join products on 
     specials.products_id=products.products_id where products.manufacturers_id = '$manufacturers_id';");
 $returns = @mysql_num_rows($query);

 if (@mysql_num_rows($query) > 0) {
while ($row = mysql_fetch_array($query)) {
  mysql_query("delete from specials where products_id = '" . $row['products_id'] . "'");
  print $row['products_model'] . " (" . $row['products_id'] . ") deleted <BR>\n";
}
 } Else {
 Print "Could not find any specials with manufacturers_id = " . $manufacturers_id;
}
?>

 

I admit that the code may not be cleanest or the pretiest but it works after 20 minutes of effort.

 

Backup your database first and let me know how it goes,

Robert

[speed2]

Hi Rob, Its worked very well for me, thank a lot.

 

Hey you should make a module for this, I am sure this is very usefull for many people.

 

THanks a lot for your help rob!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Let me know if you decided to make a module, cos I wanted to download that first!!!!

[Guest]

Ok... here it goes.

 

Copy the code below and paste it into a brand new file called wheteveryouwant.php.

Modify $manufacturers_id = "2"; to the appropiate manufacturers_id

 

FTP it to your site in the admin folder.

 

Browse to the file www.yoursite.com/admin/whateveryouwant.php and voila (in theory at least)

 

<?PHP
 require('../includes/configure.php');
 function db_connect() {
   global $db_link;
   @$db_link = mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD);
   if ($db_link) @mysql_select_db(DB_DATABASE);
   return $db_link;
 }
 db_connect() or die('Unable to connect to database server!');
 $manufacturers_id = "2";
 
 $query = mysql_query("select * from specials  left join products on 
     specials.products_id=products.products_id where products.manufacturers_id = '$manufacturers_id';");
 $returns = @mysql_num_rows($query);

 if (@mysql_num_rows($query) > 0) {
while ($row = mysql_fetch_array($query)) {
  mysql_query("delete from specials where products_id = '" . $row['products_id'] . "'");
  print $row['products_model'] . " (" . $row['products_id'] . ") deleted <BR>\n";
}
 } Else {
 Print "Could not find any specials with manufacturers_id = " . $manufacturers_id;
}
?>

 

I admit that the code may not be cleanest or the pretiest but it works after 20 minutes of effort.

 

Backup your database first and let me know how it goes,

Robert

<{POST_SNAPBACK}>

 

 

I dont get it to work, I have now made this file, should I also do the rest on top of this page?

[Rob123]

I dont get it to work, I have now made this file, should I also do the rest on top of this page?

<{POST_SNAPBACK}>

The file that you quoted above is used to delete all specials that have a certain manufacturer.

 

In order to create specials with a certain manufacturer look at Post #4

 

HTH,

Robert

[Guest]

Ok... I made an assumption that you want to reduce your prices by 10%.  (Note that in the code below there is a .9  (1 - .10) .    A 5% reduction would be .95

 

Required disclosure:

Back up your database first then run this in your favorite SQL admin tool.  It is also suggested that you run this against a test dB first.

 

Step 1 (Optional)

Delete all of your existing specials.

delete from specials;

 

Step 2 (Required)

Run the following "Insert Select statement"

INSERT specials (products_id, specials_new_products_price, specials_date_added, expires_date, status) SELECT products_id, products_price * .9 , now(), '2005-01-20', '1' from products where manufacturers_id = '2';

 

Once this is complete you can look in your admin and view your specials....

Administration > Catalog > Specials

 

Let me know how it goes,

Robert

<{POST_SNAPBACK}>

 

 

What do you mean with:

"Run the following "Insert Select statement" "

 

Where/How do I do that? Thanks for help!

[Rob123]

What do you mean with:

"Run the following "Insert Select statement" "

 

Where/How do I do that? Thanks for help!

<{POST_SNAPBACK}>

You would run the "code" above using phpMyAdmin or your favorite MySQL connector or administration tool.

 

PM me if you need more assistance.

 

Robert