select best selling items

bampot

i'm hoping to put on a best selling list on to an ecommerce site, so i am wondering what would be the best way to select from the following table so that the most popular items shall come first?.

CREATE TABLE order_items (
orderid int(10) unsigned NOT NULL default '0',
code char(13) NOT NULL default '',
item_price float(4,2) NOT NULL default '0.00',
quantity tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (orderid,code)
) TYPE=MyISAM

thanks in advance

bubblenut

To do exactly what you asked you could use

select orderid, count(*) from order_items group by orderif order by `count(*)`

but think how nice it would be to have hitlists controlled by you. You can see what's selling well or not with the above query but ultimately you have the final decision as to which products go where in the list. This would require another table (a couple more if you want multiple different hitlists, maybe bestsellers, bargain basement, boy's toys, girl's toys etc)

HTH
Bubble

bampot

thanks for that

and yeah a controlled hitlist would be better, is this generally done with a manually inputed table?

bubblenut

Yea, that's how it's done in the company I work for, we've got a nice little front end for it though which makes it easy. I've just had a nice idea though. What if you had a script running on a cron job which updates your bestsellers hitlist with the bestsellers every day, or every week. However there is a collumn which you can set (like a sticky) which would say "this item cannot be bumped down"? Pretty nifty huh? You'd have a semi-automatic hitlist which you still have complete (be that partial or more, whatever you want) controll over.
I can't really work on it at the moment as I'm at work but I'm going to have a bit of a play with this tonight.
Bubble

bampot

i like it, let me know how you get on with that.

i've set up a dynamic list which i could integrate with sticky items from a table, although i wouldnt be sure how to go about ordering the sticky n dynamic items automatically

cheers again