Sqlite Help and total_count

[byrondallas]

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!

[redcrusher]

Originally Posted by byrondallas

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

[byrondallas]

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() - 7 * 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"; 


[_Aerospace_Eng_]

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.

[byrondallas]

Done. Thank you!