PHP Mysql Query Question

[notset4life]

Can someone help me with a PHP mysql query?
I have a database which basically has fields such as

transaction_id and product name

I would like to show on a certain page "Best Sellers", so I need a sql query (in PHP) to produce this. It would have to separate the product names, then add up the total of each so best sellers would be shown.

Example Database:
-------------------
trans_id Product Name
1 Widgets
2 Things
3 Widgets
4 Otherthings
-----------------------
and now on a page show:

BEST SELLERS
Widgets 2
Things 1
Other Things 1

Any help would be appreciated.

Thanks

[JGross]

are you adding up the number associated with the best sellers? If so where are you getting this number? Is it another field in the table or are you adding it in manually?

i need more information to be able to help you.

[notset4life]

Thanks for responding. I don't know the answer, that's what I'm trying to find out the best way to do this.

I am wondering is there is a command that can count how many entries in the product field there are, so if "widgets" came up 12 times, then that would be used to compare the other products to see which are the best sellers.

[mitchind]

You can do it in one query:

PHP Code:

SELECT count(`transaction_id`)  AS popularity, `product name`
FROM  `products` 
GROUP  BY transaction_id
ORDER  BY popularity DESC 


and if you want the Top <n> bestsellers just add the LIMIT clause to the end

PHP Code:

SELECT count(`transaction_id`)  AS popularity, `product name`
FROM  `products` 
GROUP  BY transaction_id
ORDER  BY popularity DESC 
LIMIT 5 


[notset4life]

Originally Posted by mitchind

and if you want the Top <n> bestsellers just add the LIMIT clause to the end

PHP Code:

SELECT count(`transaction_id`)  AS popularity, `product name`
FROM  `products` 
GROUP  BY transaction_id
ORDER  BY popularity DESC 
LIMIT 5 


Thanks a lot, the query works perfectly in phpmyadmin. Can you tell me how you would place the output on a web page? The code came out to:

$sql = 'SELECT count(`title`) AS popularity, `title`'
. ' FROM `stats` '
. ' GROUP BY title'
. ' ORDER BY popularity DESC '
. ' LIMIT 20';

but I don't know how to show the results. Sorry for my lack of knowledge, but I do appreciate any help.

[notset4life]

Never mind, I figured it out, thanks again.