SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Easy Question But Something Is Stumping Me

    I have a site with thousands of members that have entered cities. On the location browsing pages, I want it to list the top cities entered...

    I've tried doing it as count but something is messing me up so I'm going to strip the code down to just the query that pulls all cities and the loop that displays them instead of having someone look at my mistake.

    Code PHP:
    $querycit = mysql_query("SELECT DISTINCT `info12` FROM usertable WHERE `info13` = '$location' AND `info12` <> '' AND active = '1'");

    That is the query that pulls all cities listed from active members. info12 is the city, info13 is the state.

    Code PHP:
    <?
    																		  while($result5 = mysql_fetch_assoc($querycit)) { echo "<a href=\"/location.php?location=$location&searchcity=" . $result5["info12"] . "\" />" . $result5["info12"] . "</a>, "; }
    																		  ?>

    That's the loop that prints the cities and links them to the browsing page. What I was trying to do is limit the results to only display cities if they appear at least twice. That way if members enter something silly, it won't be seen.

    Thanks for your help!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      info12,
      COUNT(*) AS `count`
    FROM
      usertable
    WHERE
      info13 = '$location' AND active = '1'
    GROUP BY
      info12
    HAVING
      COUNT(*) > 1 AND LENGTH(info12) > 0
    ORDER BY
      COUNT(*) DESC

  3. #3
    SitePoint Enthusiast britton's Avatar
    Join Date
    Jul 2005
    Location
    Tennessee
    Posts
    83
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks!

    Works perfectly now.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •