Exit
  • Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
  • 한국 커뮤니티
0

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

Enthusiast ,
Feb 07, 2013 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.

TOPICS
Server side applications
549
Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Feb 07, 2013 Feb 07, 2013
LATEST

>"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.

Translate
Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines