-
1. Re: Record count help needed
bregent Jun 20, 2013 12:56 PM (in response to matthew stuart)>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:
-
2. Re: Record count help needed
matthew stuart Jun 20, 2013 3:10 PM (in response to bregent)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 Jun 21, 2013 4:50 AM (in response to bregent)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.



