3 Replies Latest reply: Jun 21, 2013 4:50 AM by matthew stuart RSS

    Record count help needed

    matthew stuart Community Member

      I am trying to wirte a MySQL query where I can display the record number returned in a repeat region... eg:

       

      Record 1

      Record 2

      Record 3

      Record 4

      Record 5 etc...

       

      I keep getting a stupid number or a '0' repeated  in place of the incrementing number.

       

      I have:

       

      SELECT COUNT(*) AS Count, fld_fID, fld_fTHREADID FROM tbl_forumPOSTS WHERE fld_fTHREADID = %s

       

      What am I doing wrong?

       

      Thanks.

        • 1. Re: Record count help needed
          bregent CommunityMVP

          >What am I doing wrong?

           

          There is no row number in your SQL. COUNT() is an aggreate function that's used to count the number of records in a group - not to return a row number. In a SQL database, there really is no such thing as a row number as rows are returned in arbitrary order unless specified. If you just want to assign an incremental number to a row, either do it with a loop counter in your script, or try this method:

           

          http://blog.gomilko.com/2007/04/28/mysql-rownum-imitation

          • 2. Re: Record count help needed
            matthew stuart Community Member

            Thanks Bregent... my lack of terminology knowledge hindered me as I didn't know what I was looking for when searching how to implement it

            • 3. Re: Record count help needed
              matthew stuart Community Member

              Here's how I imnplemented it for those who require it:

               

              Basically, to achieve an automated row count for the amount of records returned from a repeat region, one needs to take the original query and wrap it into a subquery with the rownum as the outside query... like so:

               

               

              SELECT table1.* xxx, @rownum:=@rownum+1 AS rownum

                        FROM

                        (

                                  SELECT table1.*, `table2`.`field1` AS table2_field1, table3.* etc etc

                                  FROM tbl_table1

                                  JOIN table2 ON table2.field1 = table1.field1

                        )

                table1,

              (SELECT @rownum:=0) r

               

               

              The SELECT bit between the first two brackets is my original query that I needed to get a count on of 1 to 10 or however records are returned.

               

              On the outer query, you also need to make an alias of the table you're drawing records from otherwise you'll get an error saying the table doesn't exist:

               

              table1.* xxx

               

              or for your clarity you could say:

               

              table1.* AS xxx

               

              Now all is in place, within your bindings panel (if you still have it ), simply locate the rownum and insert it into your repeat region:

               

              <?php echo($row_recordsetname['rownum']); ?>

               

              By wrapping the original query in brackets and making it a subquery, you stop the rownum returning strange results. So for 10 results, I was getting it start from 1 to 10, then the same results would start from 217 to 227, then 55 to 65 and so on... everything I did was not consistent or even accurate most of the time.

               

              Hope this helps.