Hi there, What I am trying to do is basically order my data in descending order, however I want the data to be selected in reverse from the DB? How do you do this? Any help will be much appreciated
If a list is: (1,2,3,4,5). You can select the appropriate record and order it by desc, thus the query would read if it selected all the records like: (5,4,3,2,1). But im not sure what exactly your after. Kind Regards
OK I will tell you what I am doing. I have a DB with a list of items. I want it to so that the page URL that contains a limited amount of items is always static. Eg, on page=1 there is a list of the first 100 items recorded into the DB. page=2 contains the next 100 items and so on etc. However, to do that I am using $page_num --; PHP: ORDER BY id ASC LIMIT '.$page_num.'00, 100 PHP: , which all works very well. However on say page=1 the results are ordered from oldest to newest, where I want it displayed in reverse in DESC order.. I hope that helps
I would then suggst using the date field and order that be desc in conjunction with the id or just or its own. Example: ORDER BY id ASC LIMIT '.$page_num.'00, 100 could become: ORDER BY date DESC,id ASC LIMIT '.$page_num.'00, 100 I think your after something similar dependent on what your date field is called. In sql you can order your sql statement by having two columns for the order by keyword: http://www.w3schools.com/PHP/php_mysql_order_by.asp Kind Regards
mrhrk01, the second field you use for order is only taken into consideration for records that have their first order field equal.. so it will not work in this case.. gbh, what you should do is to use a subquery.. something like SELECT * FROM (SELECT <whatever you need, including the ID> FROM <your table> ORDER BY id ASC LIMIT '.$page_num.'00, 100) as tbl ORDER BY ID DESC PHP: i do not use mysql, so not sure if this will work out of the box, but give it a try ..
not tested but I think this shoul work: SELECT * FROM {table} WHERE id IN (SELECT I FROM {table} ORDER BY id ASC LIMIT '.$page_num.'00, 100) ORDER BY id DESC
it turns out MySQL has a bug and this SQL should work but it doesn't another idea is simply use SELECT * FROM {table} ORDER BY id ASC LIMIT '.$page_num.'00, 100 and then in your PHP code go from last record to first in array and you'll have it DESC
@ red_mamba, my post above works correctly ... no need to go to arrays and php loops to get the data..
@mrhrk01 thanks for your help but that won't work @gnp sorry for long delay, I've tried using your code in so many ways, but keep getting an error Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in ...profile.php on line 40 All I have on line 40 is "if (mysql_num_rows($result) > 0) {" I'm really trying to get your way to work, but it just won't. @red_mamaba I tried something like that but I'm trying to go for an efficient way, however your way is plan B.
@red_mamba can you please show me an example? @gnp are you sure your way works? Must I use "tbl" too or name it something else?
sure, form top of my head: $result = mysql_query("SELECT * FROM {table} ORDER BY id ASC LIMIT ".$page_num."00, 100"); $rows = array(); $cnt = 0; while ($row = mysql_fetch_assoc($result)) { $rows[$cnt++] = $row; } for ($i=sizeof($rows)-1; $i>-1; $i--) { //reverse order $row = $rows[$i]; } not the optimal way, but it shoul do the trick p.s. You're from NZ?
The tbl i use is just random .. the point is that any subquery in mysql has to be named with an alias (could be anything that is not being currently used) could you post your line where you assign a value to the $result var (with my code in) ? take care
Here you go gnp $result = mysql_query("SELECT * FROM (SELECT * WHERE `user_id` = '".$userI."' FROM `matrix` ORDER BY matrix_id ASC LIMIT ".$subaction."00, 100) as tbl ORDER BY matrix_id DESC"); PHP: Have also tried $result = mysql_query("SELECT * FROM `matrix` WHERE `user_id` = '".$userI."' ORDER BY matrix_id ASC LIMIT ".$subaction."00, 100 as tbl ORDER BY matrix_id DESC"); PHP: Thanks
Thnaks red_mamba, but with your code it seemed to keep repeating until it said "Fatal error: Maximum execution time of 60 seconds exceeded". It must be my fault But all my code works, I just want it displayed in reverse order. Yes, I live in NZ how come?
Maybe I am taking the wrong approach to what I am doing. What I am trying to do is say my site stores short notes. Ok now pretend there are 700 notes stored. You go to say "notes.php" and the last 100 notes are displayed from the most recent to older notes. Then at the bottom there will b a link to the next page. However that page will not be page 2, I want it to be page 6, because I want the pages to be static. If someone bookmarks it, it must always show the same notes. Therefore when going to "notes.php" you are actually on page 7. To achieve that, I used "ORDER BY id ASC LIMIT '.$page_num.'00, 100", therefore when on page=1 you would see the first 100 results stored into the DB, but of course it shows them from oldest to newest, but I want it in reverse order, still using the same 100 notes it fetched. I hope that helped, if it didn't then ignore what I said