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.