Mysql and PHP trying to get the rank of items based on the qunatity of items sold

Question

I'm trying to get the rank from the total quantity an item has sold so far I can get the total quantity an item has sold. But I dont no how to get the rank of the items ranking number 1 to the item that sold the most, Can some one please help me figure this out below is the working code that I have so far that gets the total amount of each item. Below is my PHP & MySQL code.

PHP & MySQL code

function rank(){
    $query = "SELECT SUM(`orders`.`quantity`) AS `total`, `orders`.`product_id`, `data`.`member_id`
              FROM `orders`
              INNER JOIN `data` ON `orders`.`product_id` = `data`.`product_id`
              GROUP BY `orders`.`product_id`
              ORDER BY `total` DESC";
    //short hand if statement ? if-true : if-false to determine the number of results returned by the query                                          
    $count = ($query = mysqli_query(database(), $query)) ? mysqli_num_rows($query) : 0; 

    if($count === 0){
        return false;       
    } else {
        while($row = mysqli_fetch_assoc($query)){
            $results[] = array(
                         'total' => $row['total'],
                         'member_id' => $row['member_id'],
                         'product_id' => $row['product_id']
            );
        }

        return $results;
    }   
}

Not sure I understand the ranking logic, can you please explain more clearly based on the structure of your tables? — Alon Eitan, Dec 31 '17 at 15:21
I want the item that sold the most quantity to be number 1 and the one with the least to be like 276 or what ever the last rank would be — juice.gin, Dec 31 '17 at 15:23
If your query works, and returns the rows ordered by `total DESC`, then you can use a basic counter variable -> `$i=1;` ... `'rank'=>$i'` ... `$i++;`. — Sean, Dec 31 '17 at 16:28
The `$i=1;` would go inside your `else {` before your `while(...`. The `'rank'=>$i` would go inside your `$reults[] = ...`. And the `$i++;` would go inside your `while(...` after your `$results[] = ...` — Sean, Dec 31 '17 at 16:56
@Strawberry I am not comparing a string to a numeric value. I was suggesting how to add another `key=>value` pairing for the `rank` to the OPs `$results` array. — Sean, Dec 31 '17 at 19:06

Mysql and PHP trying to get the rank of items based on the qunatity of items sold

0 Answers0