Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Database updates


lembi2001

Recommended Posts

Howdy people

 

I was wondering if anyone was able to help me setup a SQL query to update the prices of products in my database.

 

I have ZERO idea how to accomplish this. The prices are located on another website that provides the info free.

 

If someone can help me with even a basic query to update a price i might be able to modify it myself :)

 

Sorry for the vague question but i'm not sure what info is needed.

 

cheers

Link to comment
Share on other sites

UPDATE products SET products_price ='x.xx' WHERE products_model = 'some model #'

 

Is the basic statement and it would have to be repeated for every product.

 

That said it maybe easier to do this another way, that is with Easy Populate. How are your prices received? Meaning do you get you prices from a text file or csv file or what?

Link to comment
Share on other sites

i looked at easy populate but it is too much hassle for the site owner....

 

I don't mind compiling a huge SQL query, is it possible to set variables in a SQL query?

 

The price are taken from a websiter and can be written into the query before it is run.

 

My query would probably look something like this:

 

Var GoldSpot = 1658.67
Var SilverSpot = 32.50
Var PlatSpot = 1634.83
Var PallSpot = 648.72

 

then the query strings would contain the variables plus a fixed amount e.g.

 

UPDATE products SET products_price = GoldSpot + 'x.xx' WHERE products_model = 'some model #'

 

is this possible??

Link to comment
Share on other sites

update:

 

I have managed this so far

 

SET @GoldSpot = '1681.80';
SET @SilverSpot = '33.03';
SET @PlatSpot = '1654.90';
SET @PallSpot = '665.20';
update products SET products_price = @GoldSpot + '18.34' WHERE products_model = 'AGE1OZ';

 

I get the following output in mysql admin

 

SET @GoldSpot = '1681.80';# MySQL returned an empty result set (i.e. zero rows).
SET @SilverSpot = '33.03';# MySQL returned an empty result set (i.e. zero rows).
SET @PlatSpot = '1654.90';# MySQL returned an empty result set (i.e. zero rows).
SET @PallSpot = '665.20';# MySQL returned an empty result set (i.e. zero rows).

update products SET products_price = @GoldSpot + '18.34' WHERE products_model = 'AGE1OZ';# 1 row affected.

 

The price for the product is updated correctly but i'm concerned about the top entries. Are they anything to worry about?

Link to comment
Share on other sites

From my very limited knowledge of mysql I would suggest that you are getting these errors as the values you are trying to set are not in the database - which is OK as they are variables that may change often.

Now running on a fully modded, Mobile Friendly 2.3.4 Store with the Excellent MTS installed - See my profile for the mods installed ..... So much thanks for all the help given along the way by forum members.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...