Simple design issue

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

User avatar
Da P3s7
Forum Commoner
Posts: 30
Joined: Wed Jul 19, 2006 3:25 pm
Location: /usr/src/kernels/ 2.6.15-1.2054_FC5-i686

Simple design issue

Post by Da P3s7 »

This is more of a design question but of the simple form....

I need to make 2 buttons (back and next) for displaying the mysql data that i have extracted and which i can easily manipulate.
The thing is:
I use a .html file to POST from a form to the .php file. The 2 buttons are supposedly displaying inside the page onto which i orderly displayed the mysql data wanted to be retrieved.
The back button isn't supposed to appear if it's displaying the first row in the table and the next isn't supposed to appear if it's the last....

I already tryied doing it but to no avail..... :?
Any ideas any1?
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Post by tecktalkcm0391 »

Do something like:

Code: Select all

$query = // ... you query...
$total = mysql_num_rows;
//display 
$row = 1;

// where you want the back button put this:

if($row != 1){
     // Show Back Button
}

// and then for the next put something like this:

if(($row)!=$total)){
    // Show Next Button
}
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

You may to search for pagination. I think there are some good things in the Code Snippets forum.
User avatar
Da P3s7
Forum Commoner
Posts: 30
Joined: Wed Jul 19, 2006 3:25 pm
Location: /usr/src/kernels/ 2.6.15-1.2054_FC5-i686

Post by Da P3s7 »

I saw the pagination in code snippets but it's way too complicated for what i need..... i just need a back and next buttons...;)
Ill try that thx tecktalk.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

So you need a previous page button or a next page button depending on which page you're on?
User avatar
Da P3s7
Forum Commoner
Posts: 30
Joined: Wed Jul 19, 2006 3:25 pm
Location: /usr/src/kernels/ 2.6.15-1.2054_FC5-i686

Post by Da P3s7 »

Yes.
If i'm on first page i don't get the back button and if i'm on last page i don't get next button
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Seriously, you are loking for a pagin routine. If you don't need the page numbers leave that potion of the routine out and only use the 'Next' and 'Previous' portions. But what you are looking for is paging.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

Code: Select all

public function getSearchResults($join, $where, $offset, $limit = 5)	
		{
			$ret = $this->findBySql('
			SELECT COUNT(DISTINCT(`accounts_details`.`id`)) as `count`
			FROM `accounts_details` '. $join . '
			WHERE '. $where);

			if ($ret[0][0]['count'] > 0)
			{
				$pagination = '';
				
				if($offset > 0)
				{ 
					$pagination .= '<a href="/index.php/search/results/?offset='.($offset - 1).'">PREV '.$limit. '</a>';  
				}
				else
				{
					$pagination .= ' PREV '. $limit. '  '; 	
				}
			
				if(($ret[0][0]['count'] - ($limit * $offset)) > 0)
				{
					$pagination .= ' <a href="/index.php/search/results/?offset='.($offset + 1).'">NEXT '.$limit.'</a>';
				}
				else
				{
					$pagination .= ' NEXT '. $limit; 
				}  	
				
				$ret = $this->findBySql('
				SELECT `accounts_details`.`id`, `age`, `firstname`, `photos`.`filename`, `accounts`.`status`
				FROM `accounts_details` '. $join . '
				INNER JOIN `accounts` ON `accounts`.`id` = `accounts_details`.`id`
				WHERE '. $where.'
				GROUP BY `accounts_details`.`id`
				ORDER BY `accounts`.`status` DESC
				LIMIT '.($offset * $limit).', '. $limit);
	
				return array('pagination' => $pagination, 'profiles' => $ret, 'count' => $ret[0][0]['count']);			
			}
			else
			{
				return array('pagination' => '', 'profiles' => array(), 'count' => 0);
			}
		}
I'm about to go to bed so here's a lazy reply for you.

1) Count the rows
2) Check for an offset (?page=1 for example)
--> if does not exist lets skip the previous button
--> if is greater than 0 lets include previous button
3) See if there are enough rows for a next button
--> if total rows minus the offset multiplied by the amount of rows per page is greater than 0 show a next button
4) Add the offset and limit to query
5) Perform query.

Simple.
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

My code came from oracle which doesn't have LIMIT, but I tried to modify it for mysql. Mine's a little simpler (basically the same minus the count and maintains the query string), and sounds like it'd suit your needs. Does "pagination" without the page numbers, just previous and next. When there are still more it shows next, when there are ones before it shows previous. It's also probably a little faster than the above because you don't have the count query (which you'd need to add page numbers, but since you don't there's no need for it).

Basically it adds the querystring prior to &haspages (which is just a place holder) to all of the next and previous links and tacs on limit and offset (offset and pagesize)...

function:

Code: Select all

<?php

function pages($offset, $pagesize, $hasnext) {
	global $_SERVER;
	global $PHP_SELF;
	
	list($querystring,) = explode("&haspages", $_SERVER["QUERY_STRING"]);
	
	if ($offset > 1) {
		echo "<a href=\"" . $PHP_SELF . "?" . $querystring . "&haspages=true&offset=" . ($offset - $pagesize) . "&pagesize=" . $pagesize . "\">< Previous</a>";
	}
	if ($hasnext) {
		echo "<a href=\"" . $PHP_SELF . "?" . $querystring . "&haspages=true&offset=" . ($offset + $pagesize) . "&pagesize=" . $pagesize . "\">Next ></a>";
	}
	
}

?>
use (ADOdb - mySQL (untested)):

Code: Select all

<?php

	$offset = (ctype_digit($_GET["offset"])) ? $_GET["offset"] : 1;
	$pagesize = (ctype_digit($_GET["pagesize"])) ? $_GET["pagesize"] : 25;

	$sql = "select * from table LIMIT ?,?";
			
	unset($binds);
	$binds["offset"] = $offset;
	$binds["limit"] = $pagesize;
	$rs = $db->execute($sql, $binds);

	while ($arr = $rs->FetchRow()){
		echo "<a href=\"index.php?todo=something&id=" . $arr["id"] . "\">" . $arr["name"] . "</a><br>";
	}

	if ($rs->RecordCount() - 1 >= $pagesize) {
		pages($offset, $pagesize, true);
	} else if ($offset > 1) {
		pages($offset, $pagesize, false);
	}

?>
use (ADOdb - Oracle):

Code: Select all

<?php

	$offset = (ctype_digit($_GET["offset"])) ? $_GET["offset"] : 1;
	$pagesize = (ctype_digit($_GET["pagesize"])) ? $_GET["pagesize"] : 25;

	$sql = "SELECT * FROM (SELECT ROWNUM as LIMIT, T.* FROM (select * from table) T) WHERE LIMIT BETWEEN :THEMIN AND :THEMAX";
			
	unset($binds);
	$binds["THEMIN"] = $offset;
	$binds["THEMAX"] = ($offset + $pagesize);
	$rs = $db->execute($sql, $binds);

	while ($arr = $rs->FetchRow()){
		echo "<a href=\"index.php?todo=something&id=" . $arr["ID"] . "\">" . $arr["NAME"] . "</a><br>";
	}

	if ($rs->RecordCount() - 1 >= $pagesize) {
		pages($offset, $pagesize, true);
	} else if ($offset > 1) {
		pages($offset, $pagesize, false);
	}

?>
Last edited by shoebappa on Mon Jul 24, 2006 2:37 am, edited 3 times in total.
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

I just noticed Jcart's code starts at offset 0, you may need to do that in mySQL, I'm fairly certain my Oracle code starts at 1 because it's using rownumbers which start with 1, I don't know where mySQL Limit offsets start (prolly 0). If that's the case, just replace all 1s in my code with 0s (at least where $offset is used, which I think is all occurances of 1)

Edit, which it does start at 0 so for mySQL:

function:

Code: Select all

<?php

function pages($offset, $pagesize, $hasnext) {
	global $_SERVER;
	global $PHP_SELF;
	
	list($querystring,) = explode("&haspages", $_SERVER["QUERY_STRING"]);
	
	if ($offset > 0) {
		echo "<a href=\"" . $PHP_SELF . "?" . $querystring . "&haspages=true&offset=" . ($offset - $pagesize) . "&pagesize=" . $pagesize . "\">< Previous</a>";
	}
	if ($hasnext) {
		echo "<a href=\"" . $PHP_SELF . "?" . $querystring . "&haspages=true&offset=" . ($offset + $pagesize) . "&pagesize=" . $pagesize . "\">Next ></a>";
	}
	
}

?>
use (ADOdb - mySQL (untested)):

Code: Select all

<?php 

        $offset = (ctype_digit($_GET["offset"])) ? $_GET["offset"] : 0; 
        $pagesize = (ctype_digit($_GET["pagesize"])) ? $_GET["pagesize"] : 25; 

        $sql = "select * from table LIMIT ?,?"; 
                        
        unset($binds); 
        $binds["offset"] = $offset; 
        $binds["limit"] = $pagesize; 
        $rs = $db->execute($sql, $binds); 

        while ($arr = $rs->FetchRow()){ 
                echo "<a href=\"index.php?todo=something&id=" . $arr["id"] . "\">" . $arr["name"] . "</a><br>"; 
        } 

        if ($rs->RecordCount() - 1 >= $pagesize) { 
                pages($offset, $pagesize, true); 
        } else if ($offset > 0) { 
                pages($offset, $pagesize, false); 
        } 

?>
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

Or even better... Moving the hasnext to the function as well. Don't ask me why I didn't do that in the first place, I prolly got that hasnext code in 10 places in my last project (stupid)...

function (untested):

Code: Select all

<?php

function pages($defaultoffset, $defaultpagesize, $recordcount) {
	global $_SERVER;
	global $PHP_SELF;
	
	if ($recordcount - 1 >= $pagesize) {
		$hasnext = true;
	} else if ($offset > 1) {
		$hasnext = false;
	} else {
		return true;	
	}
	
	list($querystring,) = explode("&haspages", $_SERVER["QUERY_STRING"]);
	
	if ($offset > 1) {
		echo "<a href=\"" . $PHP_SELF . "?" . $querystring . "&haspages=true&offset=" . ($offset - $pagesize) . "&pagesize=" . $pagesize . "\">< Previous</a>";
	}
	if ($hasnext) {
		echo "<a href=\"" . $PHP_SELF . "?" . $querystring . "&haspages=true&offset=" . ($offset + $pagesize) . "&pagesize=" . $pagesize . "\">Next ></a>";
	}
	
}

?>
use (ADOdb - mySQL - untested):

Code: Select all

<?php

	$offset = (ctype_digit($_GET["offset"])) ? $_GET["offset"] : 1;
	$pagesize = (ctype_digit($_GET["pagesize"])) ? $_GET["pagesize"] : 25;

	$sql = "select * from table LIMIT ?,?";
			
	unset($binds);
	$binds["offset"] = $offset;
	$binds["limit"] = $pagesize;
	$rs = $db->execute($sql, $binds);

	while ($arr = $rs->FetchRow()){
		echo "<a href=\"index.php?todo=something&id=" . $arr["id"] . "\">" . $arr["name"] . "</a><br>";
	}

	pages($offset, $pagesize, $rs->RecordCount());

?>
Thanks for making me look at that code again, I'm happier with this than what I started out with, now I gotta go back and change my old crap :roll:
Last edited by shoebappa on Mon Jul 24, 2006 3:18 am, edited 4 times in total.
User avatar
xpgeek
Forum Contributor
Posts: 146
Joined: Mon May 22, 2006 1:45 am
Location: Kyiv, Ukraine
Contact:

Post by xpgeek »

Simple pagination with next and prev button.

Code: Select all

$start = intval($_GET['start']);
if (empty($start)) $start = 0;
$sql = "select *   from table";
$res = mysql_query($sql);
$total = mysql_num_rows($res);

$sql = "select *  from table  limit $start, 20";
$res = mysql_query($sql);
if ($start > 1)
$smarty->assign('prev_page', $start-20);
if ($start+20 < $total)
$smarty->assign('next_page', $start+20);

$smarty->display('page.tpl');
page.tpl

Code: Select all

{if $prev_page !== ''}
    <a href="index.php?start={$prev_page}">prev</a> &nbsp;&nbsp;&nbsp;&nbsp;
    {/if}
    {if $next_page !== ''}
    <a href="index.php?start={$next_page}">next</a>
    {/if}
I hope it help you!

P.S. I am using a smarty template engine.
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

I'm missing how xpgeek and jcart maintain their query strings... Say I'm passing other values via get to the page that's doing the pagination... Don't those get lost when they click next in each of their code samples?
User avatar
Da P3s7
Forum Commoner
Posts: 30
Joined: Wed Jul 19, 2006 3:25 pm
Location: /usr/src/kernels/ 2.6.15-1.2054_FC5-i686

Post by Da P3s7 »

ty everybody for replying.
BUT your codes are too complex in the sense that in my case it can be done simpler (not that for others it wouldn't work):

I got an indexing system which actualy shows which row i'm supposed to be on.
If i'm on row 0 (i decided to add 0 becouse of a function that returns every row from the table which assigns an array also to key 0) the back button won't show as there's no point to show it.
If i'm on the row on which i have the maximum ( in this case last) value of index the next button won't show as there's no point for it again.

I was thinking of a combination of these:

Code: Select all

function mysql_fetch_all($result) {
   $i = 0;
   for ($i=0; $i<mysql_num_rows($result); $i++) {
       $return[$i] = mysql_fetch_array($result);
   }
   return $return;
   mysql_data_seek($result, 0);
}
// the function which returns all the rows in the table
$array = mysql_query("select myindex from main", $con);
//the query for the index
$mysql_fetch_all = mysql_fetch_all($array);
/*
introduce the data in an array
and last
*/
$indexingarray = end($mysql_fetch_all);
$index = end($indexingarray);
// i did it this way as i tryind end(end()) but it didnt work?!?
But i still haven't figured how to combine them.
As for the back i was going to use the input type hidden and pass h=1
So if $_POST['h'] or $_GET['h'] == 1 it wouldn't show the back.

Thx in advance.
User avatar
shoebappa
Forum Contributor
Posts: 158
Joined: Mon Jul 11, 2005 9:14 pm
Location: Norfolk, VA

Post by shoebappa »

I think minamally you would need to use one of our examples. If you aren't passing anything in the query string you can likely use any of the examples, if you are I don't know that any but mine would work without tweaking.

The issue at hand is that you're returning the full result set, which isn't what you want in a paginated situation. You want only the number of results for that page. In mySQL this can be done with the LIMIT offset,limit SQL. If you don't use the limit you're essentially returning every result for every page, which is obviously not what you want if there is a need for pagination...
Post Reply