MySQL help needed

[jhande]

Here’s my scenario:

I just received a notification from my wholesaler that all Testor products will have a price increase come June 1^st.

I figured it’s not a big problem, although I carry over 200 of their products due to the increase being category specific. Meaning it’s broken down as such:

Testors ¼ oz Enamel Bottles from $1.49 to $1.64 (45 items)

Testors 3 oz Enamel Spray Cans from $4.49 to $4.94 (39 items)

Model Master ½ oz Enamel Bottles from $2.99 to $3.29 (60 items)

Model Master 3 oz Enamel Spray Cans from $4.99 to $5.49 (50 items)

Paint kits, Acrylic Paints, Boyd Paints, etc, etc…

I have the same category (sub-catergory) structure utilized on my shop. So I figured a couple of simple MySQL queries could easily update the prices in all the rows per category. Something like this:

UPDATE PRODUCTS

SET PRODUCTS_PRICE = ‘1.64’

WHERE CATEGORIES_ID = ‘problem here’ :o

Problem = no CATEGORIES_ID column in the PRODUCTS table.

So I checked the CATEGORIES table and found that the records only seem to point to the PARENT_ID (main category not the sub-category).

The only work-around I can figure out is to run this query:

UPDATE PRODUCTS

SET PRODUCTS_PRICE = ‘1.64’

WHERE PRODUCTS_PRICE = ‘1.49’

But that would obviously change every product price that is $1.49 to $1.64, not good.

Any ideas how I can accomplish my task without going through the Admin Panel and changing each individual price? :(

 

[♥FWR Media]

Here’s my scenario:

I just received a notification from my wholesaler that all Testor products will have a price increase come June 1^st.

I figured it’s not a big problem, although I carry over 200 of their products due to the increase being category specific. Meaning it’s broken down as such:

Testors ¼ oz Enamel Bottles from $1.49 to $1.64 (45 items)

Testors 3 oz Enamel Spray Cans from $4.49 to $4.94 (39 items)

Model Master ½ oz Enamel Bottles from $2.99 to $3.29 (60 items)

Model Master 3 oz Enamel Spray Cans from $4.99 to $5.49 (50 items)

Paint kits, Acrylic Paints, Boyd Paints, etc, etc…

I have the same category (sub-catergory) structure utilized on my shop. So I figured a couple of simple MySQL queries could easily update the prices in all the rows per category. Something like this:

UPDATE PRODUCTS

SET PRODUCTS_PRICE = ‘1.64’

WHERE CATEGORIES_ID = ‘problem here’ :o

Problem = no CATEGORIES_ID column in the PRODUCTS table.

So I checked the CATEGORIES table and found that the records only seem to point to the PARENT_ID (main category not the sub-category).

The only work-around I can figure out is to run this query:

UPDATE PRODUCTS

SET PRODUCTS_PRICE = ‘1.64’

WHERE PRODUCTS_PRICE = ‘1.49’

But that would obviously change every product price that is $1.49 to $1.64, not good.

Any ideas how I can accomplish my task without going through the Admin Panel and changing each individual price? :(

 

 

Well what do you know?

 

Are all the 1.49 - 1.64 under the same category?

 

Is there any uniformity between the categories_id and the price changes?

[♥FWR Media]

Well you could try the following but DO NOT try it on a live database.

 

I have made several assumptions e.g. that a specific category will ONLY contain products from this supplier and therefore the prices can be updated.

 

To try it save the file in catalog root named something like priceupdate.php then browse to it.

 

Firstly the following line will need to be changed to contain all of the specific categories: -

 

  $categories_to_update = "'10','15','4'"; // Include all of the categories ids to be changed here

 

Basically a comma seperated list of category ids with each id surrounded by single quotes like '4' there should be no commas at the beginning or at the end.

 

 

<?php

 require('includes/application_top.php');

 $categories_to_update = "'10','15','4'"; // Include all of the categories ids to be changed here

 $p2cquery = "
SELECT p2c.products_id, p.products_price 
FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
INNER JOIN " . TABLE_PRODUCTS . " p 
ON p2c.products_id = p.products_id
WHERE p2c.categories_id IN ( " . $categories_to_update . " )";

 $result = tep_db_query($p2cquery)
 or die('Query failed');

 while($tochange = tep_db_fetch_array($result) ) {

switch($tochange['products_price']) {
  case( $tochange['products_price'] == '1.49' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '1.64'
	WHERE products_price = '1.49'
	AND products_id = '" . $tochange['products_id'] . "'";
  break;
  case( $tochange['products_price'] == '4.49' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '4.94'
	WHERE products_price = '4.49'
	AND products_id = '" . $tochange['products_id'] . "'";
  break;
  case( $tochange['products_price'] == '2.99' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '3.29'
	WHERE products_price = '2.99'
	AND products_id = '" . $tochange['products_id'] . "'";
  break;
  case( $tochange['products_price'] == '4.99' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '5.49'
	WHERE products_price = '4.99'
	AND products_id = '" . $tochange['products_id'] . "'";
  break;

}

 }
 tep_db_free_result($result);

?>

 

It may only give you some ideas but I hope it helps.

[♥FWR Media]

Ooops forgot the update query

 

<?php

 require('includes/application_top.php');

 $categories_to_update = "'10','15','4'"; // Include all of the categories ids to be changed here

 $p2cquery = "
SELECT p2c.products_id, p.products_price 
FROM " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c
INNER JOIN " . TABLE_PRODUCTS . " p 
ON p2c.products_id = p.products_id
WHERE p2c.categories_id IN ( " . $categories_to_update . " )";

 $result = tep_db_query($p2cquery)
 or die('Query failed');

 while($tochange = tep_db_fetch_array($result) ) {

switch($tochange['products_price']) {
  case( $tochange['products_price'] == '1.49' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '1.64'
	WHERE products_price = '1.49'
	AND products_id = '" . $tochange['products_id'] . "'";
	tep_db_query($updatesql);
  break;
  case( $tochange['products_price'] == '4.49' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '4.94'
	WHERE products_price = '4.49'
	AND products_id = '" . $tochange['products_id'] . "'";
	tep_db_query($updatesql);
  break;
  case( $tochange['products_price'] == '2.99' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '3.29'
	WHERE products_price = '2.99'
	AND products_id = '" . $tochange['products_id'] . "'";
	tep_db_query($updatesql);
  break;
  case( $tochange['products_price'] == '4.99' ):
	$updatesql = "
	UPDATE " . TABLE_PRODUCTS . "
	SET products_price = '5.49'
	WHERE products_price = '4.99'
	AND products_id = '" . $tochange['products_id'] . "'";
	tep_db_query($updatesql);
  break;
  default:

}

 }
 tep_db_free_result($result);

?>

[jhande]

Well what do you know?

Not much, just enough to get me in trouble. :(

 

Are all the 1.49 - 1.64 under the same category?

Yes, they are under the same sub-category:

CATEGORIES table / CATEGORIES_ID = 41

 

Is there any uniformity between the categories_id and the price changes?

Now that I had 6 cups of coffee and looking at the CATAGORIES table again, yes. :blush:

CATEGORIES table / CATEGORIES_ID = 41 / $1.49 to $1.64

CATEGORIES table / CATEGORIES_ID = 43 / $4.49 to $4.94

CATEGORIES table / CATEGORIES_ID = 42 / $2.99 to $3.29

CATEGORIES table / CATEGORIES_ID = 44 / $4.99 to $5.49

[♥FWR Media]

Not much, just enough to get me in trouble. :(

 

 

Yes, they are under the same sub-category:

CATEGORIES table / CATEGORIES_ID = 41

 

 

Now that I had 6 cups of coffee and looking at the CATAGORIES table again, yes. :blush:

CATEGORIES table / CATEGORIES_ID = 41 / $1.49 to $1.64

CATEGORIES table / CATEGORIES_ID = 43 / $4.49 to $4.94

CATEGORIES table / CATEGORIES_ID = 42 / $2.99 to $3.29

CATEGORIES table / CATEGORIES_ID = 44 / $4.99 to $5.49

 

$categories_to_update = "'41','42','43','44'";

 

What the script will do is pull ALL product ids from those categories then update ALL prices e.g. from 1.49 to 1.64 WHERE the product_id is within one of those categories.

[jhande]

A huge thank you Robert :D

 

I will give that a try on my local host.

 

Greatly appreciated... ;)

 

-- edit --

 

I totally over looked the PRODUCTS_TO_CATEGORIES table.

Even if I noticed it I would have no clue as to how I could update prices utilizing , what, 2 or 3 tables. :blush:

[♥FWR Media]

A huge thank you Robert :D

 

I will give that a try on my local host.

 

Greatly appreciated... ;)

 

I wrote it on the fly and couldn't test so let me know how it goes.

[jhande]

I wrote it on the fly and couldn't test so let me know how it goes.

 

Ok I will, thank you again for your help!

 

:)

[♥FWR Media]

Ok I will, thank you again for your help!

 

:)

 

Oh and in case you get confused when you browse to the file.

 

You won't see anything but a blank screen (assuming you get no errors), after browsing to the file check the database/site to see if the changes have been made.

[jhande]

Oh and in case you get confused when you browse to the file.

 

You won't see anything but a blank screen (assuming you get no errors), after browsing to the file check the database/site to see if the changes have been made.

 

Thanks for that tip Robert.

 

I would probably of gone crazy wondering why my screen was blank. :blush: LOL

[♥FWR Media]

Thanks for that tip Robert.

 

I would probably of gone crazy wondering why my screen was blank. :blush: LOL

 

When will you try it? I'll try and be about.

[jhande]

When will you try it? I'll try and be about.

 

I'll probably give it a try in about 1 hour or so. I need to find something for my kids to do so they will leave me alone now that Mom is off to work, LOL.

[jhande]

I am so sorry Robert that I was unable to get back here as I said I would.

 

I had an unexpected emergency and had to take off. By the time I got back home my meds kicked in (I have a few medical issues) and I slept the rest of the evening away. :mellow:

 

I will give your script a try later this (Monday) evening when I return from my parents cookout. I haven't seen them in about a year and they're now up at there place in VT. Figured it was time to say hi at our yearly Memorial Day get together... LOL

 

I do appreciate that you took the time to write that for me and I'm anxious to give it a try. But it's 2:30 am, my brain is still foggy and we're hitting the road around 5:00 am.

 

Have a fun holiday and catch you latter. ;)

 

Again I'm sorry I was MIA... :(

[jhande]

Robert, you are a scholar and a gentleman! :)

 

Thank you so very much, it worked just great. ;)

 

Again I am so sorry I could not get to it sooner. :(

 

Have you thought about taking that code a bit further and turning it into a contribution?

Making it something that could be accessed from the admin panel and chosing a category from maybe a drop down list and inputting the prices?

 

I think that would be awsome!

 

Again thank you Robert, your help is very much appreciated. :D