Jump to content

Special Block query


Recommended Posts

I have some more questions:

1) It is possible to make a random product "on sale" every hour,day or weekly.....automatically

Actually i have embroidery designs website and now i have a good amount of designs uploaded.I want to make one design as free every hour.So that every hour there would be one designs free of cost.

here is my website:

By the way thank you for your reply

Share this post

Link to post
Share on other sites

Hi Kkmixs, Vekia,


Vekia, is this field still used? The product(s) that pop up in my specials block are the one that have a reduced price. However, the on_sale field is still 0.


if I add (through sql) a new record to the table ps_specific_price, like this: (N.B. Table layout from PS


INSERT INTO `ps_specific_price`(`id_specific_price`, `id_specific_price_rule`, `id_cart`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `price`, `from_quantity`, `reduction`, `reduction_type`, `from`, `to`) VALUES (2,0,0,2,0,0,0,0,0,0,0,-1,1,7,`amount`,0,0) 


It is neatly added to the specials (actually the 'Price drop') list (when clicking 'All specials'). This specific record reduced the price of product with ID =2 with a fixed amount of 7.00.




So, if above adding of record is indeed enough, as it seems like, you could make some cron job out of this. In php script you have to open the database, find a (random) product-ID and then add the correct parameters in the sql statement (for 50% reduction, probably something like this:


Maybe first delete all old records, to remove any old 'specials' product:

TRUNCATE TABLE `ps_specific_price`;


We want to reduce the price by a certain percentage, like 50% reduction:

INSERT INTO `ps_specific_price`(`id_specific_price`, `id_specific_price_rule`, `id_cart`, `id_product`, `id_shop`, `id_shop_group`, `id_currency`, `id_country`, `id_group`, `id_customer`, `id_product_attribute`, `price`, `from_quantity`, `reduction`, `reduction_type`, `from`, `to`) VALUES (1,0,0,< your desired product-ID>,0,0,0,0,0,0,0,-1,1,0.5,`percentage`,0,0) 


ad 1: As we truncated the table, this can be set to be always 1
ad -1: We determine the price by reduction of original one, so we set this to -1.
ad 0.5: This is the percentage we want to reduce the price (0.5 x 100% = 50% reduction)


To make it a little bit more readable, You can minimize the values to add; the others will default to 0, or auto-increased. We also need to select ANY existing product ID, take a random one.


INSERT INTO `ps_specific_price`(`id_product`,`id_shop`,`price`, `from_quantity`, `reduction`, `reduction_type`)
SELECT a.id_product,0,-1,1,0.5,'percentage' FROM `ps_product` a WHERE a.`active` = 1 ORDER BY RAND() LIMIT 1


I will add a php file, you can put in your /config folder. Then run a cron job with it. (Put in /config for security reasons)



Add database, login name and password to the file. You can copy these values from 


to the file.



Hope this helps,



Edited by PascalVG (see edit history)
  • Like 1

Share this post

Link to post
Share on other sites

yess i just wondering, in my first post i wrote: special (on sale?) 

just wondering about what we are talking about, specials ? or products  "on sale" ?


it is little misunderstangind, "specials" feature isn't the same as products "on sale" feature


everything depends on it



Pascal, your whole post, im impressed, you're wizard ;)

Share this post

Link to post
Share on other sites

Thank you friends, you save my hours....One last question actually i have Product rating installed on my website.i want to make the top rated designs as price drop/special for 24 hours.
In simple words my customer will some and vote for my designs and the top most voted designs would be free, then next day again the customer will come to vote and the top most designs will be free again.

Some how i have managed to reset the IP address to allow the customer to vote every day but now question how to make top rated designs freebie for 24 hours?

You can check live website here:

There is the designs at the top with most rated designs, i want to make it special or price reduction to 100% for 24 hours.

By the way, once again thanks for the earlier reply.

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More