...

View Full Version : Sqlite Help and total_count



byrondallas
07-29-2012, 11:30 PM
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).


$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).


$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
07-30-2012, 08:45 AM
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?


SELECT count(*) AS 'blahblahblah' FROM table


SELECT column FROM table ORDER BY somethingElse LIMIT 10

hope that helps

byrondallas
07-30-2012, 01:03 PM
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.


$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_
07-30-2012, 01:11 PM
I would change

$totalnum = $row[total_count];
to

$totalnum = $row['total_count'];
since 'total_count' is a string literal key in your results.

byrondallas
07-31-2012, 01:25 AM
Done. Thank you!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum