What's wrong with my SELECT/COUNT(*) statement?

Report · Feb 07, 2013

I am trying to get a total count of records related to a single master record within one table, and if I test this in the DW recordset wizard, I get the results I need. By testing it, I mean if I enter something other than the default -1, where there are results related to another 'master record', then I end up with the correct amount of rows, but when I test it in a browser, all I am getting is '0' for all records.

This bit of code is setting the 'master record ID' to which others will relate to:

<?php $_GET['trcount'] = $row_rs_replycount['fld_fID'] ?>

This is my recordset which is inline with the code which is inside a repeat region:

<?php

$threadreplys_rs_replycount = "3";

if (isset($_GET['trcount'])) {

$threadreplys_rs_replycount = $_GET['trcount'];

}

mysql_select_db($database_conn_mrs, $conn_mrs);

$query_rs_replycount = sprintf("SELECT *, COUNT(*) AS countTOT FROM tbl_forumPOSTS WHERE fld_fTHREADID = %s ORDER BY fld_fID DESC", GetSQLValueString($threadreplys_rs_replycount, "int"));

$rs_replycount = mysql_query($query_rs_replycount, $conn_mrs) or die(mysql_error());

$row_rs_replycount = mysql_fetch_assoc($rs_replycount);

$totalRows_rs_replycount = mysql_num_rows($rs_replycount);

?>

And this is the code to display the total number of records that are related

<?php echo $row_rs_replycount['countTOT']; ?>

As I say, I have it working when testing different default values in the recordset wizard, but not live on a page.

Thanks.

Report · Feb 07, 2013

>"SELECT *, COUNT(*) AS countTOT FROM tbl_forumPOSTS WHERE....

That SQL is not valid - at least not by SQL standards. If your query contains an aggregate, then all columns that are not aggregated must be in a group by clause. It's possible that MySQL implicitly adds all non-aggreated columns to a group by, but you should probably fix it anyway. If you don't need to use the other columns from that table in the recordset, then just remove the * from the select list.

"SELECT COUNT(*) AS countTOT FROM tbl_forumPOSTS WHERE....

If you still have a problem, then output the value in $totalRows_rs_replycount to see if you are getting any rows returned from your query.

Next, comment out these lines:

if (isset($_GET['trcount'])) {

$threadreplys_rs_replycount = $_GET['trcount'];

to make sure any value in trcount is not interfering with the default value of '3' and see if that returns any results.