Please help before my head explodes.

[DarkDelight.net]

I am doing my nutt over a query which is very much like one found in admincp/adminlog.php
It is supposed to return the number of rows in the query.

This one is slightly different in that it has a WHERE clause involving a second table.

MySQL reports that if I want to mix COUNT() and WHERE then I also need GROUP BY.
This is causing a major headache.
No matter what field I try grouping by I get varying odd results.


$counter = $DB_site->query_first("
SELECT COUNT(*) AS total
FROM " . TABLE_PREFIX . "********log AS ********log
LEFT JOIN " . TABLE_PREFIX . "user AS user USING(userid)
WHERE CONCAT(',',user.membergroupids,',') LIKE '%" . $usergroup . "%' OR user.usergroupid = " . $usergroup . "
//This bit is causing me trouble!
GROUP BY ?????
");


Am I going about this totally wrong?
Is there another way to count the number of rowsreturned by the query which generates the logs?


$logs = $DB_site->query("
SELECT ********log.*,user.usergroupid,user.username,user. membergroupids
FROM " . TABLE_PREFIX . "********log AS ********log
LEFT JOIN " . TABLE_PREFIX . "user AS user USING(userid)
LEFT JOIN " . TABLE_PREFIX . "usergroup AS usergroup USING(usergroupid)
WHERE CONCAT(',',user.membergroupids,',') LIKE '%" . $usergroup . "%' OR user.usergroupid = " . $usergroup . "
ORDER BY $order
LIMIT $startat, $perpage
");


I've tried
$counter = COUNT($logs); and $counter = mysql_num_rows($logs); to no avail.

:(

Any assistance would be most appreciated.

Thank you.

[Scott MacVicar]

if you remove the LIMIT you'll find

$DB_site->num_rows($query);

will return this value.

[DarkDelight.net]

if you remove the LIMIT you'll find

$DB_site->num_rows($query);

will return this value.


Does this mean I have to run the query twice, once with and once without the LIMIT, to get the page numbrs to display correctly?