SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Jun 2007
    Location
    Plymouth uk
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    how do i count records in database

    ok i have this code which counts total records in database

    PHP Code:
    $sql "select count(*) from members";
    $st mysql_query($sql) or die(mysql_error());
    echo 
    "<p>The forum has " mysql_result($st00) . " members. Put that in your pipe and smoke it.</p>"
    but what i would like it do do is count lost cats found cats lost dogs found dogs
    so i would end up with some thing like this

    there are ? lost and ? found cats and ? lost and ? found dogs
    hope you can understand
    cheers
    Doug
    an old man of 60 trying to keep up with the youngsters he he
    http://lostpetsplymouth.net16.net

  2. #2
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How about something like this subquery:
    PHP Code:
    $sql "select 
        (select count(id) from members where lost='1') as lost_dogs,
        (select count(id) from members where found='1') as found_dogs"
    ;
    $result mysql_query($sql) or die(mysql_error());
    $data mysql_fetch_assoc($result);
    echo 
    'lost dogs: ' $data['lost_dogs'];
    echo 
    '<br />found dogs: ' $data['found_dogs']; 
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  3. #3
    SitePoint Addict
    Join Date
    Jun 2007
    Location
    Plymouth uk
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok this is what i have and get an error i know its me i am hopeless
    PHP Code:
    $sql "select  
        (select count(id) from register where type='dog' and state ='lost' lost='1') as lost_dogs,    
        (select count(id) from register where type='dog' and state ='found' found='1') as found_dogs"
    ;
        
    $result mysql_query($sql) or die(mysql_error());
        
    $data mysql_fetch_assoc($result);
        echo 
    'lost dogs: ' $data['lost_dogs'];
        echo 
    '<br />found dogs: ' $data['found_dogs']; 
    Last edited by dougvcd; Jun 3, 2009 at 05:15. Reason: updated
    an old man of 60 trying to keep up with the youngsters he he
    http://lostpetsplymouth.net16.net

  4. #4
    SitePoint Addict
    Join Date
    Jun 2007
    Location
    Plymouth uk
    Posts
    313
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok
    thanks to all
    now have it sorted
    Doug
    an old man of 60 trying to keep up with the youngsters he he
    http://lostpetsplymouth.net16.net

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select count(id) from register where type='dog' and state ='lost' lost='1'
    You don't need the "lost='1'" bit as you already have state='lost'. But if you did you would need to use something like AND or OR to join it to the rest of the query.

    Code:
    SELECT * FROM table WHERE type='dog' AND (state='lost' OR lost=1)


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
  •