[Solved] How to Count Result of SQL Query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Nicoza
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 11:43 am

[Solved] How to Count Result of SQL Query

Post 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.
Last edited by Nicoza on Wed Oct 11, 2006 11:41 am, edited 1 time in total.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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'
vuacorona
Forum Newbie
Posts: 3
Joined: Tue Oct 10, 2006 12:06 pm

Post by vuacorona »

$query = "SELECT COUNT(*) FROM GESPREKKE WHERE ForumID = '" .$DB_SUBID "'"
$RESULTTEL = mysql_query($query, $DB_CONNECTION);

Hope it help.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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
Nicoza
Forum Newbie
Posts: 11
Joined: Mon Oct 02, 2006 11:43 am

How to Count Result of SQL Query

Post 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.
Post Reply