[SOLVED] Creating popular items from orders in database.

[norbie]

Hi,

 

I am setting up an e-commerce website where orders are stored in a mysql database after processing.

 

A table called `itemorder` contains a record for each item that has been bought, the quantity purchased, and the order that it belongs to.

 

I'd like to create a list of popular items based on this table. I've tried putting the itemids into an array but can't work out how to do the maths in php to count up how many of each value there are. The quantity field would make things even harder.

 

Any suggestions how I can do this?

 

Many thanks.

 

[attachment deleted by admin]

[jackpf]

Is quantity the quantity sold, or the quantity you have in stock?

 

If it's the former, you can just do something like this:

 

$sql = mysql_query("SELECT * FROM table ORDER BY quantity DESC LIMIT 5");

[tang]

Something like:

 

SELECT `itemid` FROM `table` ORDER BY SUM(`quantity`) GROUP BY `itemid`

 

Should do it.

 

It's not very efficient though so will get slower the more items you have in your table. A better way would be to maintain a separate table with just itemid and quantity.

[taith]

another way...

 

$q=mysql_query("SELECT * FROM `yourtable`");
$i=array();
while($r=mysql_fetch_assoc($q)) $i[$r[itemid]]+=$r[quantity];

 

there you then have an array with all the itemids and their respective quantitys... sort it and yer ready to go...

 

take yer pick...

[norbie]

FYI, it's the quantity that was bought on that order.

 

For instance, order 1 could contain:

item2 quantity3

item5 quantity5

 

I'll have a look through your suggestions now. Cheers.

[norbie]

another way...

 

$q=mysql_query("SELECT * FROM `yourtable`");
$i=array();
while($r=mysql_fetch_assoc($q)) $i[$r[itemid]]+=$r[quantity];

 

there you then have an array with all the itemids and their respective quantitys... sort it and yer ready to go...

 

take yer pick...

 

That's perfect, just what I was looking for. Thanks!

 

Using the usort() function I can sort the array, but it sorts the wrong way down.

i.e.

item 21 = 1

item 18 = 5

item 1 = 9

 

How can I get it to sort the quantity values on the right the other way round?

[norbie]

All sorted, used arsort to reverse it.

 

Thanks for everyone's help.