By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,695 Members | 1,338 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,695 IT Pros & Developers. It's quick & easy.

How paginate page can keep up after the table was sorted

osward
P: 38
I had made a table colum sortable and paging the table, following are the code

Expand|Select|Wrap|Line Numbers
  1. // Display Event List
  2.             echo "<center>"._EVENTLIST."</center><br>";
  3.  
  4.             $now = Date(Y-m-d);
  5.             // sort table colum
  6.             if(!isset($_GET['page'])){
  7.                 $page = 1;
  8.             } else {
  9.                 $page = $_GET['page'];
  10.             }
  11.  
  12.             $sort_order = "date desc";
  13.             if( isset( $_GET['order']) ){
  14.             $order = $_GET['order'];
  15.             switch ($order) {
  16.             case "A_date":
  17.             $sort_order = 'date asc';
  18.             break;            
  19.             case "D_date":
  20.             $sort_order = 'date desc';
  21.             break;
  22.             case "A_code":
  23.             $sort_order = 'code asc';
  24.             break;
  25.             case "D_code":
  26.             $sort_order = 'code desc';
  27.             break;
  28.             case "A_host":
  29.             $sort_order = 'host asc';
  30.             break;
  31.             case "D_host":
  32.             $sort_order = 'host desc';
  33.             case "A_place":
  34.             $sort_order = 'place asc';
  35.             break;
  36.             case "D_place":
  37.             $sort_order = 'place desc';
  38.             break;
  39.             default:
  40.             $sort_order = 'date desc'; // the default sort order
  41.             $short = 'Illegal GET Value in ' . __FILE__;
  42.             $long = $short . ". Cause is <b>$order</b> is invalid.";
  43.             LogMessage("Error", $short, $long);
  44.             break;
  45.             }
  46.             }        
  47.  
  48.  
  49.             $max_results = 5;
  50.             $from = (($page * $max_results) - $max_results);
  51.             $result = $db->sql_query("SELECT * FROM ".$prefix."_event_cat ORDER by $sort_order LIMIT $from, $max_results") or die("Error: " . mysql_error());
  52.  
  53.             if($db->sql_numrows($result) == 0){
  54.                 echo(""._NODATA."");
  55.             }
  56.  
  57.             $bgcolor = "$bgcolor3";
  58.             echo "<table width=\"100%\" border=\"1\">";
  59.             echo "<tr bgcolor=\"$bgcolor2\">"
  60.             ."<td align=\"center\"><a href=\"/modules.php?name=event&order=A_date\"><img src=\"images/asc.gif\" alt=\""._SORTASC."\" title=\""._SORTASC."\" border=\"0\" width=\"6\" height=\"6\"></a>&nbsp;"._EVENTDATE."&nbsp;<a href=\"/modules.php?name=event&order=D_date\"><img src=\"images/desc.gif\" alt=\""._SORTDESC."\" title=\""._SORTDESC."\" border=\"0\" width=\"6\" height=\"6\"></a></td>"
  61.             ."<td align=\"center\"><a href=\"/modules.php?name=event&order=A_code\"><img src=\"images/asc.gif\" alt=\""._SORTASC."\" title=\""._SORTASC."\" border=\"0\" width=\"6\" height=\"6\"></a>&nbsp;"._CODE."&nbsp;<a href=\"/modules.php?name=event&order=D_code\"><img src=\"images/desc.gif\" alt=\""._SORTDESC."\" title=\""._SORTDESC."\" border=\"0\" width=\"6\" height=\"6\"></a></td>"
  62.             ."<td align=\"center\">"._EVENTNAME."</td>"
  63.             ."<td align=\"center\"><a href=\"/modules.php?name=event&order=A_host\"><img src=\"images/asc.gif\" alt=\""._SORTASC."\" title=\""._SORTASC."\" border=\"0\" width=\"6\" height=\"6\"></a>&nbsp;"._HOST."&nbsp;<a href=\"/modules.php?name=event&order=D_host\"><img src=\"images/desc.gif\" alt=\""._SORTDESC."\" title=\""._SORTDESC."\" border=\"0\" width=\"6\" height=\"6\"></a></td>"
  64.             ."<td align=\"center\">"._EVENTTIME."</td>"
  65.             ."<td align=\"center\"><a href=\"/modules.php?name=event&order=A_place\"><img src=\"images/asc.gif\" alt=\""._SORTASC."\" title=\""._SORTASC."\" border=\"0\" width=\"6\" height=\"6\"></a>&nbsp;"._PLACE."&nbsp;<a href=\"/modules.php?name=event&order=D_place\"><img src=\"images/desc.gif\" alt=\""._SORTDESC."\" title=\""._SORTDESC."\" border=\"0\" width=\"6\" height=\"6\"></a></td>"
  66.             ."<td align=\"center\">"._COST."</td>"
  67.             ."<td align=\"center\">"._AVAIL."</td>"
  68.             ."<td align=\"center\">"._STATUS."</td>"
  69.               ."</tr>";
  70.             // show data in table with while loop
  71.             while ($row = $db->sql_fetchrow($result)) {
  72.             if ($bgcolor == "$bgcolor3"){ 
  73.                 $bgcolor = "$bgcolor1"; 
  74.             }else{ 
  75.             $bgcolor = "$bgcolor3"; 
  76.             } 
  77.             $eid = intval($row['eid']);
  78.             $code = filter($row['code'], "nohtml");
  79.             $name = filter($row['name'], "nohtml");
  80.             $date = filter($row['date'], "nohtml");
  81.             $time = filter($row['time'], "nohtml");
  82.             $place = filter($row['place'], "nohtml");
  83.             $cost = filter($row['cost'], "nohtml");
  84.             $host = filter($row['host'], "nohtml");
  85.             $vacancy = intval($row['vacancy']);
  86.             $active = intval($row['active']);
  87.             // Caculate actual available vacancy
  88.             $confirm = mysql_result($db->sql_query("SELECT COUNT(*) as eid FROM ".$prefix."_event_user where eid=$eid"),0);
  89.             $temp = mysql_result($db->sql_query("SELECT COUNT(*) as eid FROM ".$prefix."_event_user_temp where eid=$eid"),0);
  90.             $available = $vacancy-($confirm+$temp);
  91.  
  92.             echo "<tr bgcolor=\"".$bgcolor."\">"
  93.             ."<td align=\"center\">$date</td>"
  94.             ."<td align=\"center\">$code</td>"
  95.             ."<td align=\"center\">$name</td>"
  96.             ."<td align=\"center\">$host</td>"
  97.             ."<td align=\"center\">$time</td>"
  98.             ."<td align=\"center\">$place</td>"
  99.             ."<td align=\"center\">$cost</td>"
  100.             ."<td align=\"center\">$available</td>";
  101.             // display event status
  102.             $event_act = $active;
  103.             if ($active == 1) {
  104.                 $active = "<img src=\"images/active.gif\" alt=\""._EVENTACTIVE."\" title=\""._EVENTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  105.                 $change = "<img src=\"images/inactive.gif\" alt=\""._EVENTNOTACTIVE."\" title=\""._EVENTNOTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  106.             } elseif ($active == 0) {
  107.                 $active = "<img src=\"images/inactive.gif\" alt=\""._EVENTNOTACTIVE."\" title=\""._EVENTNOTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  108.                 $change = "<img src=\"images/active.gif\" alt=\""._EVENTACTIVE."\" title=\""._EVENTACTIVE."\" border=\"0\" width=\"16\" height=\"16\">";
  109.             }
  110.             echo "<td align=\"center\"><font class=\"content\">&nbsp;$active</a> </td></tr>";
  111.             }
  112.             echo "</table>"
  113.             ."<br><br>";
  114.             // Page Numbering
  115.             $total_results = mysql_result($db->sql_query("SELECT COUNT(*) as eid FROM ".$prefix."_event_cat"),0);
  116.             $total_pages = ceil($total_results / $max_results);
  117.             // Build Previous Link
  118.             if($page > 1){
  119.                 $prev = ($page - 1);
  120.                     echo "<a href=\"/modules.php?name=event&page=$prev\">&laquo;"._PREV."&nbsp;</a> ";
  121.             }
  122.             for($i = 1; $i <= $total_pages; $i++){
  123.                 if(($page) == $i){
  124.                     echo "&nbsp;[$i]&nbsp;";
  125.                 } else {
  126.                     echo "<a href=\"/modules.php?name=event&page=$i\">&nbsp;$i&nbsp;</a> ";
  127.                 }
  128.             }
  129.             // Build Next Link
  130.             if($page < $total_pages){
  131.                 $next = ($page + 1);
  132.                     echo "<a href=\"/modules.php?name=event&page=$next\">&nbsp;"._NEXT."&raquo;</a>";
  133.                     }
  134.             echo "</center>"; 
  135.             echo "<br>";
  136.             CloseTable();
  137.             include("footer.php");
  138.     }
Both sorting of coloum and paging table works, however, after sorted the table and when I moved to the next page, the sort order reversed back to the default sort order by date field. How can I make the page change keep up after I do the sort?

One additional question is, I want to display the first row of the table to be today's date events no matter they are in ascending or descending order and that means the initial open page will not be the 1st page, Can I do that and how?

Thanks in advance
Oct 22 '07 #1
Share this Question
Share on Google+
4 Replies


code green
Expert 100+
P: 1,726
You need to pass the ORDER BY variable to the next page so the SELECT statement will use it [HTML]<name=event&page=$next&orderby=$sort_order>[/HTML]You will have to $_GET this URL variable and copy the value back in to $sort_order [PHP]$sort_order = $_GET['orderby'];[/PHP]
Oct 24 '07 #2

osward
P: 38
You need to pass the ORDER BY variable to the next page so the SELECT statement will use it [HTML]<name=event&page=$next&orderby=$sort_order>[/HTML]You will have to $_GET this URL variable and copy the value back in to $sort_order [PHP]$sort_order = $_GET['orderby'];[/PHP]
Many thanks for the help. Because I am not a programer and couldn't understand fully to your reply.

I. I couldn't find a variable orderby in my code
2. I had tried manually add &page=2 after sorted the date colum at the address bar and it display correctly.
3. In my code, I had something
[PHP]$sort_order = "date asc";
if( isset( $_GET['order']) ){
$order = $_GET['order'];[/PHP]
If I replace with $sort_order = $_GET['orderby']; the script returns an error
Expand|Select|Wrap|Line Numbers
  1. Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 3' at line 1
Could I ask for a little bit more of your time and please specifyicly instruct me where to make the changes. My guess is
1. In the Pervious and Next link, I need to add &order=$sort_order, which is from my experiment stated above.
2. I know I need to get the url of the client's browser by using the $_GET but I don't know how and where to put the code

Another interesting thing is, In my SELECT statment I put a condition WHERE date >= CURDATE() , This is exactly what I want the table to display, return the rows of event starting from today's date. However, because there are rows of data that is earlier than today's date, the page numbering code still consider it as valid rows data and where a page (often is the last page) is empty.

I think I could remedy this by altering the page numbering code to go along my orginal SELECT statment
[PHP]$total_results = mysql_result($db->sql_query("SELECT COUNT(*) as eid FROM ".$prefix."_event_cat"),0);[/PHP]
to
[PHP]$total_results = $db->sql_numrows($db->sql_query("SELECT date FROM ".$prefix."_event_cat WHERE date >= CURDATE()"));[/PHP]

Is there a way that the return starts off at page 2, for example because there are another page of record that that date is pior of today's date.
Thanks in advance
Oct 25 '07 #3

code green
Expert 100+
P: 1,726
I couldn't find a variable orderby in my code
Of course, that is the problem. It is a URL variable I created that needs to added to the URL as in [HTML]<name=event&page=$next&orderby=$sort_order>[/HTML] and given the value of $sort_order.
This way the value of $sort_order is passed to the next page.
Your query is failing
Expand|Select|Wrap|Line Numbers
  1. You have an error in your SQL syntax; 
  2. check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 3' at line 1
because $sort_order is empty. The query is someting like
Expand|Select|Wrap|Line Numbers
  1. ORDER BY LIMIT 0, 3' 
instead of
Expand|Select|Wrap|Line Numbers
  1. ORDER BY {value of $sort_order} LIMIT 0, 3' 
Otherwise everything seems fine, but echo out variables and queries to see exactly what is in there.
Oct 26 '07 #4

osward
P: 38
Of course, that is the problem. It is a URL variable I created that needs to added to the URL as in [HTML]<name=event&page=$next&orderby=$sort_order>[/HTML] and given the value of $sort_order.
This way the value of $sort_order is passed to the next page.
Your query is failing
Expand|Select|Wrap|Line Numbers
  1. You have an error in your SQL syntax; 
  2. check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 3' at line 1
because $sort_order is empty. The query is someting like
Expand|Select|Wrap|Line Numbers
  1. ORDER BY LIMIT 0, 3' 
instead of
Expand|Select|Wrap|Line Numbers
  1. ORDER BY {value of $sort_order} LIMIT 0, 3' 
Otherwise everything seems fine, but echo out variables and queries to see exactly what is in there.
Hi code geen,

Thanks for the explaination.

I found the following works for me
1. add &order=$sort_order at the links
2 add $sort_order = $_GET['order']; before the code block of making links

Regards,
osward
Oct 28 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.