This table is generated by a different query than our mapping function. Here's how the search query looks. We'll explain what's going on in each numbered step.
SELECT DISTINCT fb_city, COUNT(*)
WHERE fb_date = curdate()
AND fb_city != ''
GROUP BY fb_city
ORDER BY COUNT(*) DESC
Select the distinct values from the fb_city column and count them.
- The table where the data is located.
- You can't see it here but we set up a database trigger and named it "set_date". Before any insert statement is executed the trigger sets the value in the fb_date column to the current date using the bulit in MySQL function curdate(). The trigger code looks like this
CREATE TRIGGER set_date BEFORE INSERT ON hm_search_city FOR EACH ROW SET new.fb_date=curdate();
- Exclude any empty results.
- Here we're using the MySQL GROUP BY statement to aggregate all the individual city instances into a single value.
- Now we're going to use the MySQL ORDER BY statement to order our results by the COUNT value and list them is descending order.
- Finally we use the LIMIT statement to control how many results we show. We made this a dynamic value by setting the value in a the