1 Reply Latest reply: Feb 7, 2013 10:08 PM by bregent RSS

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

    matthew stuart Community Member

      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.

        • 1. Re: What's wrong with my SELECT/COUNT(*) statement?
          bregent CommunityMVP

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