selecting most popular products

[Virangya]

Hello..
I need to query out most demanded products from my database... i have order table(order id, date, ...), orderitem table(order id, product id and quantity), and product table.. i don't understand how to write a query for this.. can someone kindly help me?

[pritaeas]

Wouldn't that be the orderitem table, where you sum the quantities for each distinct product, and order that descending ?

Something like this perhaps, I have no relevant table to test with:

SELECT productid,SUM(quantity) AS total FROM orderitemtable GROUP BY productid ORDER BY total DESC

[Virangya]

Thank you for the reply..
well, i tested your code. Im using php. i hope you know php. i printed the resulted array. his was the result.
Array ( [0] => 2 [product_id] => 2 [1] => 4 [total] => 4 )
but thats only the first most ordered product.. why aren't other items being printed?

[pritaeas]

What other items ? You mean the other in the result ? If you run the query in phpMyadmin you get more results I suppose. Probably an error in your php code. I'd need to see it to be sure.

[Virangya]

well im using xampp. this is my php code if you like to check it..(i just typed a simple code only to check it)

<?php

$connection=mysql_connect("localhost","root","")
or die("Server Error".mysql_error());

$db=mysql_select_db(mydb,$connection)
or die("Database Error".mysql_error);



$sql="SELECT product_id,SUM(quantity) AS total FROM tblorderitem GROUP BY product_id ORDER BY total DESC";

$query=mysql_query($sql,$connection)	
	or die("SQL Error".mysql_error());

$rec=mysql_fetch_array($query);

print_r($rec);
?>

[pritaeas]

mysql_fetch_array() only returns the first record in the result set.

$query = mysql_query($sql, $connection) or die("SQL Error: " . mysql_error());
if ($query) {
  while ($rec = mysql_fetch_array($query)) {
    #print_r($rec); // you probably don't want to output everything
  }
  mysql_free_result($query);
}

[Virangya]

Oooops.. yeah, I did that mistake even before.. using mysql_fetch_array();..
mmm... Thanx a lot pritaeas for your help..!!! :):):)