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?

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

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?

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.

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);
?>

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);
}

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.