Mysql select most frequent and sort alphabetically
- by user2605793
I am trying to select the most common 100 names from a table then display the list showing the names and count. I want the user to be able to re-sort the list alphabetically rather than based on count.
I thought the following code would do it. It works for the default sort by count but fails on the sort alphabetically. The line "$count = mysql_num_rows($table);" gives an error: mysql_num_rows() expects parameter 1 to be resource, boolean given. Any help would be greatly appreciated.
// Get most popular surnames
echo '<h2>Most Common Surnames</h2>';
if ($sort == "") { // default sort by count
echo '<a href="http://mysite/names.php?id='.$id.'&sort=name">Sort by name</a><br>';
$query = "SELECT family_name, COUNT(*) as count FROM namefile
WHERE record_key = $id
GROUP BY family_name
ORDER BY count DESC LIMIT 100";
}
else { // sort alphabetically
echo '<a href="http://mysite/names.php?id='.$id.'">Sort by count</a><br>';
$query = "SELECT * FROM (
SELECT family_name, COUNT(*) as count FROM namefile
WHERE record_key = $id
GROUP BY family_name
ORDER BY count DESC LIMIT 100)
AS alpha ORDER BY family_name";
}
$table = mysql_query($query);
$count = mysql_num_rows($table);
$tot = 0;
$i = 0;
echo '<table><tr>';
while ($tot < $count2) {
$rec2 = mysql_fetch_array($table2);
echo '<td>'.$rec2[0].'</td><td>'.$rec2[1].'</td><td width="40"> </td><td>';
if ($i++ == 6) {
echo '</tr><tr>';
$i = 0;
}
$tot++;
}
echo '</tr></table><br>';
UPDATE: I needed to add "AS alpha" to give the outer select a unique name. (alpha is just a random name I made up.) It now works perfectly. Code updated for the benefit of any others who need something similar.
