MySQL query to find the most popular value in a column joined by another value in a second table
Posted
by
Budove
on Stack Overflow
See other posts from Stack Overflow
or by Budove
Published on 2012-11-18T04:36:51Z
Indexed on
2012/11/18
5:00 UTC
Read the original article
Hit count: 87
I have two tables:
- users: user_id, user_zip
- settings: user_id, pref_ex_loc
I need to find the single most popular 'pref_ex_loc' from the settings table based on a particular user_zip, which will be specified as the variable $userzip.
Here is the query that I have now and obviously it doesn't work.
$popularexloc = "SELECT pref_ex_loc, user_id COUNT(pref_ex_loc) AS countloc
FROM settings FULL OUTER JOIN users ON settings.user_id = users.user_id
WHERE users.user_zip='$userzip' GROUP BY settings.pref_ex_loc
ORDER BY countloc LIMIT 1";
$popexloc = mysql_query($popularexloc) or die('SQL Error :: '.mysql_error());
$exlocrow = mysql_fetch_array($popexloc);
$mostpopexloc=$exlocrow[0];
echo '<option value="'.$mostpopexloc.'">'.$mostpopexloc.'</option>';
What am I doing wrong here? I'm not getting any kind of error from this either.
© Stack Overflow or respective owner