Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Senior Coder
    Join Date
    May 2006
    Posts
    1,680
    Thanks
    28
    Thanked 4 Times in 4 Posts

    Query within query ! How do I do this ?

    Hi
    I am using PHP and MySQL, I am building a coupon database and want to display
    the coupons that are help by location.

    So I have a table called "coupons" which contains rows with many fields
    but only four are relevant:
    Company, Town, State, Country.

    What I want to do is build a clickable list by Country of the number of companies in each town
    and in each state and in each country but only if the count is over zero.

    So, it would look like this:

    France
    Micip - Alsese (23)

    Turkey
    Mugla - Fethiye (17)
    İzmir - Alsancak (9)

    USA
    LA - CreekVille (2)
    Mass - Boston (5)

    My starting point is:

    PHP Code:
    $sql "SELECT * from coupons WHERE ...  ORDER BY create_date";
    $result mysql_query($sql) or die("Could not select coupons."mysql_error()); 
    But I have never done a query like this so I could really do with some help.

    Thanks.
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Code:
    $sql = "SELECT country, state, city, COUNT(*) as cnt from coupons GROUP BY country, state, city ORDER BY country, state, city"; 
    $result = mysql_query($sql) or die("Could not select coupons.". mysql_error());  
    $prior = "";
    while ( $row = mysql_fetch_assoc($result) )
    {
        $country = $row["country"];
        $state = $row["state"];
        $city = $row["city"];
        $count = $row["cnt"];
        if ( $prior != $country )
        {
            echo "<br/>$country<br/>\n";
            $prior = $country;
        }
        echo "$state - $city ($count)<br/>\n";
    }
    But the mysql_xxx library is now obsolete. You really should AT LEAST be using mysqli_xxx calls.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,965
    Thanks
    79
    Thanked 4,429 Times in 4,394 Posts
    Oh, sorry. You said "clickable" list.

    So the line
    Code:
        echo "$state - $city ($count)<br/>\n";
    becomes
    Code:
        echo "<a href=\"showcity.php?c=$country&s=$state&t=$city\">$state - $city ($count)</a><br/>\n";
    Or similar.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder
    Join Date
    May 2006
    Posts
    1,680
    Thanks
    28
    Thanked 4 Times in 4 Posts
    Thanks very much.

    I will test it out.

    I take your point about the mysqli.

    I have to change a lot of scripts. I have been waiting to see
    if a useful and reliable "updater" script would come out that would
    do the job automatically of semi-automatically for

    Thanks
    If you want to attract and keep more clients, then offer great customer support.

    Support-Focus.com. automates the process and gives you a trust seal to place on your website.
    I recommend that you at least take the 30 day free trial.


  •  

    Posting Permissions

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