[Resolved] Memberlist Pagination

[klik]

Hi,

I have a script which creates a member list, but I am having some problems.

The script works until you click on '2' for second page OR 'next 25'

When this happens, the list just lists the ID's in order and not the second page list of what has been selected from the form.

Do I have to send it through a GET command?

Here's the code :

<?php	

/* PAGINATION */
	$limit = 25;     
	// Sets how many results shown per page

$sql_total_rows = mysql_query("SELECT * FROM cms_users");
$totalrows = mysql_num_rows($sql_total_rows);   

$page = $_GET['page'];


if(empty($page)){  // Checks if the $page variable is empty (not set)
	$page = 1;   // If it is empty, we're on page 1
}

    $limitvalue = $page * $limit - ($limit);


			if(($_POST['mode'] == 'join_date')  && ($_POST['order'] == 'ASC')){
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY reg_date LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'join_date')  && ($_POST['order'] == 'DESC')){
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY reg_date DESC LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'username')  && ($_POST['order'] == 'ASC')){
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY username LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'username')  && ($_POST['order'] == 'DESC')){

						 $result = mysql_query("SELECT * FROM cms_users ORDER BY username DESC LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'points')  && ($_POST['order'] == 'ASC')){

						 $result = mysql_query("SELECT * FROM cms_users ORDER BY user_points LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'points')  && ($_POST['order'] == 'DESC')){
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY user_points DESC LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'profile_views')  && ($_POST['order'] == 'ASC')){
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY profile_views LIMIT $limitvalue, $limit");
			}
			elseif(($_POST['mode'] == 'profile_views')  && ($_POST['order'] == 'DESC')){
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY profile_views DESC LIMIT $limitvalue, $limit");
			}
			else{
						 $result = mysql_query("SELECT * FROM cms_users ORDER BY user_id LIMIT $limitvalue, $limit"); 
			}


if(mysql_num_rows($result) == 0){
	echo("Nothing to Display!");
} 




/* Output our information! */

						$color1 = "#DADADA";
						$color2 = "#CCCCCC";
						$row_count = 0;

		while ($row = mysql_fetch_assoc($result)) {

			$row_color = ($row_count % 2) ? $color1 : $color2;

			echo '<tr><td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\"><span class=\"gen\">' . $row['user_id'] . '</span></td>
			<td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\"><a href="/profile.php?user_id=' .$row['user_id'] . '">' . $row['username'] . '</a></td>';

			$show_email = $row['show_email'];

				if ($show_email == 1){
					echo '<td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\"><a href="mailto:' . $row['email'] . '"><img src="/images/icon_email.gif" width="59" height="18" border = "0"></a></td>';
				}
				else{
					echo '<td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\"></td>';
				}

			echo '
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['location'] . '</td>
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['reg_date'] . '</td>
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['user_points'] . '</td>
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['profile_views'] . '</td>';

			$row_count ++;
		}

?>
<br /><br />
                          <?php 

/* Pagination Links */

if($page != 1){
	$pageprev = $page - 1;

	echo("<a href=\"/memberlist.php?page=$pageprev\">PREVIOUS".$limit."</a>&nbsp;&nbsp;&nbsp;");
}else{
	echo("PREVIOUS".$limit."&nbsp;&nbsp;&nbsp;");
}

$numofpages = ceil($totalrows / $limit);

for($i = 1; $i <= $numofpages; $i++){
	if($i == $page){
		echo($i."&nbsp;");
	}else{
		echo("<a href=\"$PHP_SELF?page=$i\">$i</a>&nbsp;");
	}
}

if(($totalrows - ($limit * $page)) > 0){
	$pagenext = $page + 1;

	echo("&nbsp;&nbsp;&nbsp;<a href=\"/memberlist.php?page=$pagenext\">NEXT".$limit."</a>&nbsp;");
}else{
	echo("&nbsp;&nbsp;&nbsp;NEXT".$limit."&nbsp;&nbsp;&nbsp;");
}


mysql_free_result($result);

?>

If someone can point me in the correct direction or can easily fix my code, that would be great!

Thanks

Edit : If you need to see this in practice, let me know and I'll supply the link.

[liquorvicar]

you've got both GET and POST data in your script. once you have selected the options from the form once, how does your script know which options to use for the second page?

you need to propagate the selections through to the second page via the querystring. you could then replace your $POST references with $REQUEST.

[klik]

Thanks for that.

Slightly further now, but I still have one small problem. The problem being, when i am on the second page for example and then i use the form to select a diffferent sorting mode. The page reloads but stays at the second page. I'd like the page to go back to '1' when sort is clicked. Which I tried to do but it doesn't work.

The code is as follows:

<?php	

/* PAGINATION */
	$limit = 25;     
	// Sets how many results shown per page

$sql_total_rows = mysql_query("SELECT * FROM cms_users");
$totalrows = mysql_num_rows($sql_total_rows);   

if(empty($page)){  // Checks if the $page variable is empty (not set)
	$page = 1;   // If it is empty, we're on page 1
}

    	$limitvalue = $page * $limit - ($limit);


		if(isset($_POST['sort']) && $_POST['sort'] == 'Sort'){
			$mode = $_POST['mode'];
			$order = $_POST['order'];
			$page = 1;
		}
		else{
			$mode = $_REQUEST['mode'];
			$order = $_REQUEST['order'];
			$page = $_REQUEST['page'];
		}


			if(($_REQUEST['mode'] == 'join_date') && ($_REQUEST['order'] == 'ASC')){
						$result = mysql_query("SELECT * FROM cms_users ORDER BY reg_date LIMIT $limitvalue, $limit");			
			}
			elseif(($_REQUEST['mode'] == 'join_date')  && ($_REQUEST['order'] == 'DESC')){
						$result = mysql_query("SELECT * FROM cms_users ORDER BY reg_date DESC LIMIT $limitvalue, $limit");
			}
			elseif(($_REQUEST['mode'] == 'username')  && ($_REQUEST['order'] == 'ASC')){
						$result = mysql_query("SELECT * FROM cms_users ORDER BY username LIMIT $limitvalue, $limit");
			}
			elseif(($_REQUEST['mode'] == 'username')  && ($_REQUEST['order'] == 'DESC')){

						$result = mysql_query("SELECT * FROM cms_users ORDER BY username DESC LIMIT $limitvalue, $limit");
			}
			elseif(($_REQUEST['mode'] == 'points')  && ($_REQUEST['order'] == 'ASC')){

						$result = mysql_query("SELECT * FROM cms_users ORDER BY user_points LIMIT $limitvalue, $limit");
			}
			elseif(($_REQUEST['mode'] == 'points')  && ($_REQUEST['order'] == 'DESC')){
						$result = mysql_query("SELECT * FROM cms_users ORDER BY user_points DESC LIMIT $limitvalue, $limit");
			}
			elseif(($_REQUEST['mode'] == 'profile_views')  && ($_REQUEST['order'] == 'ASC')){
						$result = mysql_query("SELECT * FROM cms_users ORDER BY profile_views LIMIT $limitvalue, $limit");
			}
			elseif(($_REQUEST['mode'] == 'profile_views')  && ($_REQUEST['order'] == 'DESC')){
						$result = mysql_query("SELECT * FROM cms_users ORDER BY profile_views DESC LIMIT $limitvalue, $limit");
			}
			else{
						$result = mysql_query("SELECT * FROM cms_users ORDER BY user_id LIMIT $limitvalue, $limit");
						$mode = '';
						$order = '';
			}


if(mysql_num_rows($result) == 0){
	echo("Nothing to Display!");
} 




/* Output our information! */

						$color1 = "#DADADA";
						$color2 = "#CCCCCC";
						$row_count = 0;

		while ($row = mysql_fetch_assoc($result)) {

			$row_color = ($row_count % 2) ? $color1 : $color2;

			echo '<tr><td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\"><span class=\"gen\">' . $row['user_id'] . '</span></td>
			<td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\"><a href="/profile.php?user_id=' .$row['user_id'] . '">' . $row['username'] . '</a></td>';

			$show_email = $row['show_email'];

				if ($show_email == 1){
					echo '<td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\"><a href="mailto:' . $row['email'] . '"><img src="/images/icon_email.gif" width="59" height="18" border = "0"></a></td>';
				}
				else{
					echo '<td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\"></td>';
				}

			echo '
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['location'] . '</td>
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['reg_date'] . '</td>
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['user_points'] . '</td>
                    <td class=\"row1\" bgcolor="' . $row_color . '" align=\"center\" valign=\"middle\">' . $row['profile_views'] . '</td>';

			$row_count ++;
		}

?>
<br /><br />
                          <?php 

/* Pagination Links */

if($page != 1){
	$pageprev = $page - 1;

	echo("<a href=\"/memberlist.php?page=$pageprev&mode=$mode&order=$order\">PREVIOUS".$limit."</a>&nbsp;&nbsp;&nbsp;");
}else{
	echo("PREVIOUS".$limit."&nbsp;&nbsp;&nbsp;");
}

$numofpages = ceil($totalrows / $limit);

for($i = 1; $i <= $numofpages; $i++){
	if($i == $page){
		echo($i."&nbsp;");
	}else{
		echo("<a href=\"$PHP_SELF?page=$i&mode=$mode&order=$order\">$i</a>&nbsp;");
	}
}

if(($totalrows - ($limit * $page)) > 0){
	$pagenext = $page + 1;

	echo("&nbsp;&nbsp;&nbsp;<a href=\"/memberlist.php?page=$pagenext&mode=$mode&order=$order\">NEXT".$limit."</a>&nbsp;");
}else{
	echo("&nbsp;&nbsp;&nbsp;NEXT".$limit."&nbsp;&nbsp;&nbsp;");
}


mysql_free_result($result);

?>

[liquorvicar]

why don't you make 'page' a hidden field in your form with a value of 1?
then change:

if(empty($page)){  // Checks if the $page variable is empty (not set)
        $page = 1;   // If it is empty, we're on page 1
       }

        $limitvalue = $page * $limit - ($limit);


        if(isset($_POST['sort']) && $_POST['sort'] == 'Sort'){
            $mode = $_POST['mode'];
            $order = $_POST['order'];
            $page = 1;
        }
        else{
            $mode = $_REQUEST['mode'];
            $order = $_REQUEST['order'];
            $page = $_REQUEST['page'];
        } 

to

$mode = $_REQUEST['mode'];
$order = $_REQUEST['order'];
$page = intval($_REQUEST['page']);
if($page==0){  

        $page = 1;   

}

$limitvalue = $page * $limit - ($limit);

[klik]

Thanks man! Working now.

If I wanted to go a little bit further and have the form displaying the current selections instead of always returning to the first menu items each time, how would I go about this?

Thanks a lot for your help.

[liquorvicar]

can you post your form code so i can see it?

[klik]

Oh, I thought it was included in the previous posts.

Here's the code

<form method="post" action="<?php $_SERVER['PHP_SELF'] ?>">
                    <br />
                    <table width="88%" cellspacing="2" cellpadding="2" border="0" align="center">
                      <tr> 
                        <td align="right" nowrap><span class="genmed">Select sort 
                          method:&nbsp; 
                          <select name="mode">
                            <option value="join_date">Join Date</option>
                            <option value="username">Username</option>
                            <option value="points">Points</option>
                            <option value="profile_views">Profile Views</option>
                          </select>
                          &nbsp;&nbsp;Order&nbsp; 
                          <select name="order">
                            <option value="DESC" selected>Descending</option>
                            <option value="ASC">Ascending</option>
                          </select>
                          &nbsp;&nbsp; 
                          	<input type="submit" name="sort" value="Sort" />
					<input type="hidden" name="page" value="1" />
                          </span></td>
                      </tr>
                    </table>
					</form>

[liquorvicar]

<form method="post" action="<?php $_SERVER['PHP_SELF'] ?>">
                    <br />
                    <table width="88%" cellspacing="2" cellpadding="2" border="0" align="center">
                      <tr>
                        <td align="right" nowrap><span class="genmed">Select sort
                          method:&nbsp;
                          <select name="mode">
                            <option value="join_date"<? if ($mode=='join_date') echo (' selected'); ?>>Join Date</option>
                            <option value="username" <? if ($mode=='username') echo (' selected'); ?>>Username</option>
                            <option value="points"<? if ($mode=='points') echo (' selected'); ?>>Points</option>
                            <option value="profile_views"<? if ($mode=='profile_views') echo (' selected'); ?>>Profile Views</option>
                          </select>
                          &nbsp;&nbsp;Order&nbsp;
                          <select name="order">
                            <option value="DESC"<? if ($order=='DESC') echo (' selected'); ?>>Descending</option>
                            <option value="ASC"<? if ($order=='ASC') echo (' selected'); ?>>Ascending</option>
                          </select>
                          &nbsp;&nbsp;
                              <input type="submit" name="sort" value="Sort" />
                    <input type="hidden" name="page" value="1" />
                          </span></td>
                      </tr>
                    </table>
                    </form>

[klik]

Thanks a lot! I appreciate all your help. Everything seems to be how I want it now. Thanks again!