www.webdeveloper.com
Results 1 to 3 of 3

Thread: [RESOLVED] Show most popular product

  1. #1
    Join Date
    Nov 2009
    Location
    Netherlands
    Posts
    21

    resolved [RESOLVED] Show most popular product

    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:

    Code:
    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!

    Code:
    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?

  2. #2
    Join Date
    Sep 2012
    Posts
    5
    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:
    PHP Code:
    $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:
    PHP Code:
    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.

  3. #3
    Join Date
    Nov 2009
    Location
    Netherlands
    Posts
    21
    Thanks! You're input helped me to get the right query with left join.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles