Record count help needed

[matthew stuart]

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.

[bregent · Accepted solution]

>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

[matthew stuart]

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

[matthew stuart]

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.