Page 1 of 1

[Solved] How to Count Result of SQL Query

Posted: Tue Oct 10, 2006 12:21 pm
by Nicoza
The actual query looks like this;

Code: Select all

$SQLOND = "SELECT ID_FORUM, SUBKATEGORIEID, TITEL, GEBRUIKER, DATUM, TYD, GELEES FROM FORUM WHERE SUBKATEGORIEID = '$DB_SUBID' ORDER BY DATUM DESC, TYD DESC";
But I now need to count the results. I know I should use count, but have used it before to simply count the amount of records as in:

Code: Select all

$RESULT = mysql_query('select count(*) from AANHALINGS', $DB_CONNECTION);
But the query is more complicated and don't know how to bring the 2 together. I've tried on another occasion;

Code: Select all

$RESULTTEL = mysql_query('select count(*) from GESPREKKE WHERE FORUMID = '$DB_SUBID' ORDER BY DATUM DESC', $DB_CONNECTION);
But that for you guys obviously doesn't work. I'm only 3 months into this, so excuse my stupidity. Me dumb, You smart. You Teach, Me Learn.

Thanks.

Posted: Tue Oct 10, 2006 12:30 pm
by volka
$SQLOND = "SELECT ID_FORUM, SUBKATEGORIEID, TITEL, GEBRUIKER, DATUM, TYD, GELEES FROM FORUM WHERE SUBKATEGORIEID = '$DB_SUBID' ORDER BY DATUM DESC, TYD DESC";
the blue part is the data you don't need, you want Count(*) instead.
No matter how you sort the records the amount is always the same. Therefore the red part is superfluous.

Code: Select all

SELECT
	Count(*)
FROM
	FORUM
WHERE
	SUBKATEGORIEID = '$DB_SUBID'

Posted: Tue Oct 10, 2006 12:32 pm
by vuacorona
$query = "SELECT COUNT(*) FROM GESPREKKE WHERE ForumID = '" .$DB_SUBID "'"
$RESULTTEL = mysql_query($query, $DB_CONNECTION);

Hope it help.

Posted: Wed Oct 11, 2006 3:01 am
by CoderGoblin
when you get the result set, you can automatically get the count using mysql_num_rows

Code: Select all

$sqlond = "SELECT ID_FORUM, SUBKATEGORIEID, TITEL, GEBRUIKER, DATUM, TYD, GELEES FROM FORUM WHERE SUBKATEGORIEID = '{$DB_SUBID}' ORDER BY DATUM DESC, TYD DESC";
$result_tel = mysql_query($sqlond, $db_connection);
$num_results=mysql_num_rows($result_tel);
This means you don't need another select as num_results will contain the number of records found.

if you need to join in SQL use

Code: Select all

SELECT COUNT(ID_FORUM),ID_FORUM, SUBKATEGORIEID, TITEL, GEBRUIKER, DATUM, TYD, GELEES 
FROM FORUM 
WHERE SUBKATEGORIEID = '{$DB_SUBID}'  
GROUP BY ID_FORUM,SUBKATEGORIEID,TITEL,GEBRUIKER,DATUM,TYD,GELEES 
ORDER BY DATUM DESC, TYD DESC
or something similar

How to Count Result of SQL Query

Posted: Wed Oct 11, 2006 11:40 am
by Nicoza
Thanks a million.

Everytime I use this Forum I'm amased at how quickly responses are made. Hope to contribute in the same fashion some day.

Quit simple really. I looked at mysql_num_rows before but made te mistake as in:
$RESULTTEL = "SELECT * from GESPREKKE WHERE FORUMID = '$GES_ID'";
$NO_GESPREKKE = mysql_num_rows($RESULTTEL);


Which is clear to me now.

I opted for using mysql_num_rows because I reuse the queries a lot and do not want to disturb that which is working, but learned a great deal from all of you.

Thanks again.