Count then Sort Function in PHP and MySQL

Question

I have this database wherein it has columns named Mall (it contains different names of Malls), NameOfStore (it contains name of stores) and AMEX (it contains either YES or NO depending if the store accepts American Express or not).

What I want to do is to count the number of stores accepting AMEX for each mall and then sort the malls in a descending manner according to their number of stores accepting AMEX. I want to incorporate a "Sorting" feature in a dropdown list form where users of my program can choose if they want to sort the malls with the most AMEX, etc. I was able to make a dropdown list for my "filter according to some condition" feature but this Sorting feature is much different.

I'm not sure on how I'm going to merge and properly sequence the logic for these queries.

 function sortStore($conn, $sort)
    {
    $table = "table1";
    $column1 = "Mall";
    $column2 = "NameOfStore";
    $condition = "AMEX = 'YES'"

    $query = Select Count($column2) from $table where $condition AND $sort";
    ..other codes..
    //$sort would contain cases (one case for each mall)
    //Example: case "Mall1": $sort = "Mall = 'Mall1'; break;
    //I'm actually not sure if the logic on my query is correct based on what I want to happen
    }

Then I would want to ORDER the Malls by the Number of Stores they have that are accepting AMEX in a descending order. I need to use ORDER BY function but I'm not sure where to place it in this particular context.

SUMMARY:

Dropdown list

User chooses what category he wants the mall to be sorted (Example: Number of Stores with AMEX, Number of Restaurants, etc.)

Program displays ordered list of Malls for the chosen category

$sort would contain the different cases wherein each case would refer to a specific mall — lexus
– lexus, Commented Jul 19, 2016 at 4:24
If I figure out how to make it work, I'll definitely do ascending as well. I just used descending here as an example. — lexus
– lexus, Commented Jul 19, 2016 at 6:03

Amit Garg · Accepted Answer · 2016-07-19 04:20:44Z

1

This query should give you the desired result.

select mall, count(AMEX) AS AMXCNT 
from table1 
WHERE AMEX='yes' 
GROUP BY MALL ORDER BY AMXCNT

answered Jul 19, 2016 at 4:20

Amit Garg

3,9071 gold badge30 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lexus Over a year ago

Hi, thanks. I tried it in my database and the logic was right and it worked. The challenge now is integrating it to the "Sorting" feature of my program but this will be a big help.

lexus Over a year ago

what if there are multiple categories I want Malls to be sorted. For example, in the dropdown list there will be an option Sort Malls by Number of Restaurants. In this case I would need to use cases but I'm not sure what I'm going place in the condition per case because the initial $query we have is only intended for the AMEX counter.

Will · Accepted Answer · 2016-07-19 04:46:08Z

0

You'll want to use an if operator to make a nice number representing that a store meets the condition, and then sum those up. Then you can sort on that. Something like this:

select mall, sum(if($condition, 1, 0)) as numStoresMatch
from table1
group by mall
order by numStoresMatch desc

answered Jul 19, 2016 at 4:46

Will

2,1911 gold badge24 silver badges24 bronze badges

Collectives™ on Stack Overflow

Count then Sort Function in PHP and MySQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related