SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Thread: combining query

  1. #1
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    combining query

    I'm currently running two queries - one to select distinct name from the database, and then one to select the count of how many times it appears in the database. I was wondering if there was anyway to combine these queries into one. I've tried a few joins, but i'm not that familiar with them so nothing I did worked. Here is my code:

    PHP Code:
    function showField($x$col$howDBHandler $sql)
    {
        if(
    $how == 'random')
            
    $extra 'ORDER BY RAND()';
        else
            
    $extra '';

        
    $result $sql->query("SELECT DISTINCT($col) AS data FROM lifethemesong $extra LIMIT $x");
        while(
    $row mysql_fetch_array($result))
        {
            
    $data stripslashes($row['data']);

            
    $result2 $sql->query("SELECT COUNT(id) AS total FROM lifethemesong WHERE $col = '$data'");
            
    $row2 mysql_fetch_row($result2);
            
    $total $row2[0];

            [
    snip just printing stuff out now]
        }

    Any help is much appreciated. Is it possible to do this when using distinct()?
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    SELECT $col AS data,
    count(id) AS total
    FROM lifethemesong
    GROUP BY $col
    $extra
    LIMIT $x

  3. #3
    masquerading Nick's Avatar
    Join Date
    Jun 2003
    Location
    East Coast
    Posts
    2,215
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guido, works like a charm.
    Nick . all that we see or seem, is but a dream within a dream
    Show someone you care, send them a virtual flower.
    Good deals on men's watches


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
  •