Using PHP to find out the most popular category - MYSQL

[DIM3NSION]

I have a database table called interets with 5 columns

interestID
name
interest_desc
categoryID
date

Each interestID has a categoryID

I was just wondering how i would use maybe a count statement to retrieve the most popular category

[Weedpacket]

At a first guess

Select
    categoryID,
    count(*) as population
From interets
Group by categoryID
Order by population desc
Limit 1

[socipoet]

The previous answer was in SQL code. If it helps, I have PHP code. It's simple but simplicity always worked. Basically, it queries from your database on each particular category, counts the elements in each query, and then gives you the count with the highest:

$result_first = mysql_query('SELECT FROM interests WHERE categoryID="firstsomething"');
$count_first = count (result_first);
$result_second = mysql_query('SELECT FROM interests WHERE categoryID="secondsomething"');
$count_second = count (result_second);
$result_third = mysql_query('SELECT * FROM interests WHERE categoryID="thirdsomething"');
$count_third = count (result_third);

$max_result = max($count_first, $count_second, $count_third);

If you have many categories, an iteration is best to use.

[bradgrafelman]

socipoet;10993612 wrote:

If it helps, I have PHP code.

...which is merely a bunch of SQL queries wrapped in the usual PHP syntax. So rather than one simple SQL query, you've now got... many simple SQL queries. Oh, and several PHP syntax errors as well as some conceptual errors.

socipoet;10993612 wrote:

It's simple but simplicity always worked.

As long as you ignore ease of use/maintenance, efficiency, and readability... 😉