...

View Full Version : Using COUNT



Jinxy
06-25-2011, 03:05 PM
This is my first project using sqlite so please forgive my terminology if I get something wrong.

This is what I have:



$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?

abduraooft
06-25-2011, 03:13 PM
$q = "SELECT count(*) as total_count
FROM serverload
WHERE year='$yearall' AND month='$monthall' ";

Jinxy
06-25-2011, 04:46 PM
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?



$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:



$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";
}

abduraooft
06-25-2011, 05:08 PM
Are you saying I need to make a totally seprate query for the count
Yes !

Jinxy
06-25-2011, 05:26 PM
Okay thanks a million abduraooft!

Jinxy
06-30-2011, 04:00 PM
$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?

abduraooft
06-30-2011, 05:52 PM
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!

Jinxy
06-30-2011, 06:10 PM
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:



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

Jinxy
06-30-2011, 11:21 PM
Nevermind, I got it.



<?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>";
?>





Array
(
[0] => 105
[total_count] => 105
)

$row[total_count];



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum