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 5 of 5
  1. #1
    New Coder
    Join Date
    Jul 2011
    Location
    USA
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post

    Sqlite Help and total_count

    I'm really a beginner when it comes to sqlite. I've only accomplished what I have now with allot of help from a friend. Let me first show you how I query my database and then I'll explain what I want to do. On my page it has the option to show a date selected and return the results (below).

    PHP Code:
    $db sqlite_open("load_db");
    $q "SELECT *
          FROM serverload
          WHERE year='$year'
          AND month='$month'
          AND date='$date' "

    And another option to show the entire month with a total count (below).

    PHP Code:
    $q "SELECT COUNT(*) as total_count 
          FROM serverload 
          WHERE year='$year'
          AND month='$month'"

    What I'm wanting to do is return the most recent 7 days and get the total count. No matter what I've tried works. Can somebody show me how to do this?

    Thank you!

  • #2
    New to the CF scene
    Join Date
    Jul 2012
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by byrondallas View Post
    What I'm wanting to do is return the most recent 7 days and get the total count. No matter what I've tried works. Can somebody show me how to do this?
    PHP Code:
    SELECT count(*) AS 'blahblahblah'  FROM table 
    PHP Code:
    SELECT column FROM table ORDER BY somethingElse LIMIT 10 
    hope that helps
    Last edited by WA; 07-30-2012 at 06:43 PM.

  • #3
    New Coder
    Join Date
    Jul 2011
    Location
    USA
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks! This is what I ended up using and it seems to work pretty good. If anybody sees any syntax errors please let me know.

    PHP Code:
    $year date("Y");
    $month date("M");
    $now date("d");
    $yesterday date("d"time() - 60 60 24);
    $lastweek date("d"time() - 24 60 60);

    $db sqlite_open("load_db");
    $sql "SELECT count(*) as total_count 
          FROM  serverload
          WHERE year='$year'
          AND month='$month'
          AND date BETWEEN $lastweek AND $yesterday"
    ;

    $results sqlite_query($db$sql);
    $row sqlite_fetch_array($results);
    $totalnum $row[total_count];
    echo 
    "$totalnum"

  • #4
    Supreme Master coder! _Aerospace_Eng_'s Avatar
    Join Date
    Dec 2004
    Location
    In a place far, far away...
    Posts
    19,291
    Thanks
    2
    Thanked 1,043 Times in 1,019 Posts
    I would change
    PHP Code:
    $totalnum $row[total_count]; 
    to
    PHP Code:
    $totalnum $row['total_count']; 
    since 'total_count' is a string literal key in your results.
    ||||If you are getting paid to do a job, don't ask for help on it!||||

  • #5
    New Coder
    Join Date
    Jul 2011
    Location
    USA
    Posts
    39
    Thanks
    0
    Thanked 1 Time in 1 Post
    Done. Thank you!


  •  

    Posting Permissions

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