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 9 of 9

Thread: Using COUNT

  1. #1
    New Coder
    Join Date
    Feb 2007
    Posts
    86
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Using COUNT

    This is my first project using sqlite so please forgive my terminology if I get something wrong.

    This is what I have:

    PHP Code:
    $q "SELECT *
          FROM serverload
          WHERE year='$yearall' AND month='$monthall'
          ORDER BY ID DESC
          LIMIT $offset,$pagelim"

    And I only want to count the rows here:

    WHERE year='$yearall' AND month='$monthall'

    and not the entire database. how do I accomplish this?

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Code:
    $q = "SELECT count(*) as total_count
          FROM serverload
          WHERE year='$yearall' AND month='$monthall' ";
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New Coder
    Join Date
    Feb 2007
    Posts
    86
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Ok I noticed you left out the ORDER and LIMIT. Are you saying I need to make a totally seprate query for the count or add it like this?

    PHP Code:
    $q "SELECT COUNT(*) as total_count
          FROM serverload
          WHERE year='$year' AND month='$month'
          ORDER BY ID DESC
          LIMIT $offset,$pagelim"

    In which case when I add it like that I lose my data.

    This is what I have now and my data isn't returend:

    PHP Code:
    $q "SELECT COUNT(*) as total_count
          FROM serverload
          WHERE year='$year' AND month='$month'
          ORDER BY ID DESC
          LIMIT $offset,$pagelim"
    ;

    $all sqlite_query$db$q );
    while ( 
    $row sqlite_fetch_array($all) )
    {
    $date_time date("D dS M, Y h:i:s a"$row[time]);
    echo 
    "<li><b>Time:</b> $date_time - <b>Load:</b> $row[load]</li>\n";


  • #4
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Are you saying I need to make a totally seprate query for the count
    Yes !
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #5
    New Coder
    Join Date
    Feb 2007
    Posts
    86
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Okay thanks a million abduraooft!

  • #6
    New Coder
    Join Date
    Feb 2007
    Posts
    86
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by abduraooft View Post
    Code:
    $q = "SELECT count(*) as total_count
          FROM serverload
          WHERE year='$yearall' AND month='$monthall' ";
    Sorry to bring this back up again, but I can't figure out how to access the total_count from this. How do I derive the total_count?

  • #7
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,863
    Thanks
    160
    Thanked 2,224 Times in 2,211 Posts
    Just echo the query string (like, echo $q;) and paste the output into phpmyadmin/sql console and check the output, to make it clear. You don't have to derive anything in the above query!
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #8
    New Coder
    Join Date
    Feb 2007
    Posts
    86
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by abduraooft View Post
    Just echo the query string (like, echo $q;) and paste the output into phpmyadmin/sql console and check the output, to make it clear. You don't have to derive anything in the above query!
    I'm using sqlite and don't have a phpmyadmin/sql console. What I'm needing the count for is to show my pagenation links. I want to know if the count is over 100 to show the NEXT page link. Something like:

    PHP Code:
    if ($total_count 100)
    {
    echo 
    "<a href=\"$self?page2\">NEXT</a>";

    Last edited by Jinxy; 06-30-2011 at 05:13 PM.

  • #9
    New Coder
    Join Date
    Feb 2007
    Posts
    86
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Nevermind, I got it.

    PHP Code:
    <?php
    $year 
    "2011";
    $month "Jun";
    $db sqlite_open("server_load2_db");
    $sql "SELECT count(*) as total_count 
            FROM serverload 
            WHERE year='$year'
            AND month='$month'"
    ;

    $results sqlite_query($db$sql);
    $row sqlite_fetch_array($results);
    echo 
    "<pre>"print_r($row); echo "</pre>";
    ?>

    Code:
    Array 
    ( 
        [0] => 105 
        [total_count] => 105 
    ) 
    
    $row[total_count];
    Last edited by Jinxy; 06-30-2011 at 10:32 PM.


  •  

    Posting Permissions

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