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

Filed under:
|
|

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

Related posts about php

Related posts about mysql