[RESOLVED] Show most popular product

[mjstam1989]

Hi there,

I'm struggling with some MYSQL and PHP code... I want to display the 5 most popular products so i wrote this sql query:

SELECT products_product_id, COUNT(*) AS mostPopular FROM order_details GROUP BY products_product_id ORDER BY mostPopular DESC

That's working great, now the second part. My product names are in a different table (products) so i want to display the 5 most popular products from the table products.

So this is where it goes wrong, the code below isn't working and probably it's something very stupid... but i can't find it, aaah!

function getMostPopular(){
	$result=mysql_query("SELECT products_product_id, COUNT(*) AS mostPopular FROM order_details GROUP BY products_product_id ORDER BY mostPopular DESC");
	$row=mysql_fetch_array($result);
	$max_count = $row['products_product_id'];
			
	$sql=mysql_query("SELECT * FROM products WHERE product_id=$max_count");
			
	while($rij = mysql_fetch_array($sql)){
		echo '<a href="details/'.$rij['product_id'].'"><li>'.$rij['name'].'</li></a>';
	}
						
}

Can anybody help me?

[Cap]

Hi, the first query seemes to be incorrect. If you want to display only 5 product you need to limit your query adding at the end the instraction LIMIT 0,5 ( where 5 is the product number you want dislay ). Howhever, the better way is to create only one query using LEFT JOIN. Sonthing like:

$result=mysql_query("SELECT product.name AS name, order.products_product_id AS product_id, COUNT(order.*) AS mostPopular FROM products AS product LEFT JOIN order_details ON product.products_product_id = order.products_product_id GROUP BY order.products_product_id ORDER BY mostPopular DESC LIMIT 0,5"); 

Check php manual, mysql_fecth_array returns a mysql table. If you want to display all rows about your query you need to use that instruction in a while as:

while( $row = mysql_fetch_array( $result ) )
{
  echo '<a href="details/'.$row['product_id'].'"><li>'.$row['name'].'</li></a>';
} 

But attenction, you use product_product_id in the query, and product_id in the while.

[mjstam1989]

Thanks! You're input helped me to get the right query with left join.