Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Updating price specials after expiration. Still expired.


Guest

Recommended Posts

Hello All,

My site is not quite operational yet, but hopefully it will be this weekend.

I set it up months ago (I've been busy with our first baby) and set up special pricing on some items.

Now those specials have expired.

So I go into specials (admin-catalog-specials) and change the expires date to 07/31/06 and update.

No change, the special is still expired.

I figured that maybe the date is only checked once a day so I let it go, but it still hasn't updated yet, so that wasn't the problem.

I can delete all the specials and then redo them, it works but seems like too much work and won't scale in the future.

 

So how do I renew my specials so they are active?

 

Also I'm assuming it is possible to find the right table and just use SQL to update all the expiration dates, not have to click, on all of the products.

Is this possible and what would be the best way to go about it?

I haven't played with SQL in about 10 years, but I'm sure if I try hard enough I can manage to come up with an select/set statment to do this, I just don't know which table to start looking in, or even where my SQL interface is (I know I need to look into it, along with everything else I need to do, but a pointer in the right direction would be greatly appreciated).

So would this be a viable way to do this? (Don't want to waste my time if it isn't)

And what table do I start with?

 

Thanks for the help, and get ready for more questions as I try to get this beast online.

 

Matt Wehland

littlegrassy.com

Link to comment
Share on other sites

When you were viewing the list of specials, after you changed the date, did you click the green light button?

It's probably still red (deactivated). Just click the green one.

 

You probably have access to phpmyadmin through your host's control panel. That's the easy way to access the database. I think that's what you meant on the second question. :)

 

Hello All,

My site is not quite operational yet, but hopefully it will be this weekend.

I set it up months ago (I've been busy with our first baby) and set up special pricing on some items.

Now those specials have expired.

So I go into specials (admin-catalog-specials) and change the expires date to 07/31/06 and update.

No change, the special is still expired.

I figured that maybe the date is only checked once a day so I let it go, but it still hasn't updated yet, so that wasn't the problem.

I can delete all the specials and then redo them, it works but seems like too much work and won't scale in the future.

 

So how do I renew my specials so they are active?

 

Also I'm assuming it is possible to find the right table and just use SQL to update all the expiration dates, not have to click, on all of the products.

Is this possible and what would be the best way to go about it?

I haven't played with SQL in about 10 years, but I'm sure if I try hard enough I can manage to come up with an select/set statment to do this, I just don't know which table to start looking in, or even where my SQL interface is (I know I need to look into it, along with everything else I need to do, but a pointer in the right direction would be greatly appreciated).

So would this be a viable way to do this? (Don't want to waste my time if it isn't)

And what table do I start with?

 

Thanks for the help, and get ready for more questions as I try to get this beast online.

 

Matt Wehland

littlegrassy.com

Link to comment
Share on other sites

When you were viewing the list of specials, after you changed the date, did you click the green light button?

It's probably still red (deactivated). Just click the green one.

 

You probably have access to phpmyadmin through your host's control panel. That's the easy way to access the database. I think that's what you meant on the second question. :)

 

 

Doh (slaps forehead). You hit it right on the head.

Unfortunately every time I tried searching for special pricing, everything else special or about pricing came up, but nothing about the 'special prices' page.

Boy this one was easy.

 

And phpmyadmin is what I was looking for (Ok so I hadn't looked that hard yet, was busy with other things :-)

Unfortunately I cannot get a simple select statement to work -

select * from 'specials' where 'expired_date' < '2006-07-31'

or any other variation.

I can select all the records in specials, but I cannot select anything by date yet.

 

Ok I just got it, I needed the time entry also. Although I'm not sure why, since it was 00:00:00 which should have been the same as just the date after conversion, but oh well, it works now.

 

Boy I need to brush up on my SQL. And learn PHP, which I have never played with before.

 

Thank you so much for the help, this was just the push I needed. My forehead thanks you, since I have been beating it off the monitor, because I knew this would be easy, I just wasn't looking in the right place.

Hopefully every thing else will be this easy.

This is one of the major reasons I choose OSCommerce, because of the SQL backend, I can do anything I want with the data.

 

In case anyone cares, here are the simple SQL statements I used to update the specials, instead of having to click through the web interface for ever

 

To update the expiration date on specials.

Updates all specials to 2007-07-31 00:00:00

---------- SQL query -----------

UPDATE specials

SET `expires_date` = '2006-07-31 00:00:00'

WHERE `expires_date` <> '2006-07-31 00:00:00'

----------- end SQL quesry -----------------

 

Make all specials active ( 1 = active, 0=inactive)

------------- SQL query -----------

UPDATE specials

SET `status` = '1'

WHERE 'status' <> '1'

-------------- End SQL Query ----------------------

 

The only thing is that the update to the expires_date did not update any records that had NULL fields.

I will have to look into this later, since I will want everything to expire at the same time for now.

Link to comment
Share on other sites

Archived

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

×
×
  • Create New...