0

Hi guys i have a problem regarding cakephp pagination and sorting

I have setup a paginated view as flows

SR.NO | name | address| phone|...

SR.NO is a simple counter with-in the foreach loop for displaying data instead of database ID

The below link shows how my SR.NO is created

CakePHP: Numbered Paginated Results

All is fine till now...

Now i have a requirement to sort the result based on SR.NO but have no idea on how to achieve this...

Pour some light into my head thanks...

Community
  • 1
  • 1
Scrappy Cocco
  • 1,192
  • 3
  • 21
  • 38
  • If you're really only talking about max ~100 rows, you could try a javascript table sorter. – Dave Mar 14 '13 at 04:49
  • @Dave hmmm....that can do it...but maybe i will have to follow the same pattern for bigger tables also , so i need a cakey way to do this... – Scrappy Cocco Mar 14 '13 at 05:09
  • u fill do this by simple find query like that $query = mysql_query("SELECT * FROM student"); $number=mysql_num_rows($query); echo "Total records in Student table= ". $number; and pass in serion no an id, which u can make CRUD, if u want. – usii Mar 14 '13 at 05:33
  • http://stackoverflow.com/questions/1366315/cakephp-numbered-paginated-results look also there – usii Mar 14 '13 at 05:34
  • @usii thats exactly what i did...but my problem is to sort it by the SR.NO....display works fine.... – Scrappy Cocco Mar 14 '13 at 07:28
  • u can sort the data with id, but if u do with the serial no u can send hidden id for example if student is 16 and serial no is 1 so in behalf of 1 u can sort 16. – usii Mar 14 '13 at 08:00

1 Answers1

0

If I understand correctly, S.NO is just the row-number in the results, and added with PHP.

If the query does not sort the result and S.NO has no relation with the data from the database, you cannot sort on this column, and it would not make any sense to do so.

Explanation

SQL queries without an 'ORDER BY xxx' will return the results in a random order; SQL Server UNION - What is the default ORDER BY Behaviour

Basically, this means that every time you run the query, the results may come back in a different order.

The S.NO you're adding is based on the order in which the results came back from your query, but because this order is random, each time you view the same page, the same record may get another position, thus another S.NO

Options

Specify a column to sort the results on so that the order of the results is consistent each time. You'll be able to reverse the sort-order as well if desired. S.NO can still be used, but purely for presentation

Alternatively, calculate the row position using SQL (Get row position in MYSQL query). However, even in this case row number is based on the position of each row in the results. If no ORDER BY is present, row number is still 'random' and the same row may get a different S.NO / row number each time the query is executed.

The final option is to sort using JavaScript (as others suggested). however, also because of the 'random' order, this can only be done for the records that are visible on the page. If you're using pagination (page x of y), sorting the entire database table will require a new query on the database (which cannot be ordered because of the whole 'random' issue)

Community
  • 1
  • 1
thaJeztah
  • 27,738
  • 9
  • 73
  • 92
  • so i guess there's not much choice i will follow option 1. thankz for this neat explanation... – Scrappy Cocco Mar 18 '13 at 11:56
  • You're welcome, sorry that I could offer no alternative, but this *is* the situation. Unless there's a 'real' S.NO in the database, the order is just random. You might consider to store a calculated 'S.NO' field in the database. However, for that you'll also have to consider "what is the S.NO order based on?" – thaJeztah Mar 18 '13 at 13:45