SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Displaying MySQL results in multiple pages

    I found a guide for doing so here: http://www.evolt.org/article/Multipl...comments=false

    I haven't been able to get it to work.

    I have 11 items in this particular table the default limit of items returned is set at 10. The script recognizes that there should be 2 pages. But when I click on the link to go to the next page, I see that it's tring to go to page 10 in the address bar. Naturally, I tried going straight to page 2 through the address bar, but that didn't work either.

    After 3 hours, I'm stumped.

    A running copy of my script can be found at aguynamedspaz [dot] info/browse.php

    My code looks like this:

    Code:
    	if (!($limit)){
    		$limit = 10;
    	}
    
    	if (!($page)){
    		$page = 0;
    	} // Default page value.
    
    	$numresults = mysql_query("select * from quotes"); // the query.
    //used to be: $numresults = mysql_query("select * from quotes WHERE text LIKE '%". $query ."%'"); // the query.
    
    	$numrows = mysql_num_rows($numresults); // Number of rows returned from above query.
    
    	if ($numrows == 0){
    		echo("No results found matching your query - $query"); // bah, modify the "Not Found" error for your needs.
    		exit();
    	}
    
    
    
    
    	// math
    
    	$pages = intval($numrows/$limit); // Number of results pages.
    
    	// $pages now contains int of pages, unless there is a remainder from division.
    
    	if ($numrows % $limit){
    		$pages++;
    	} // has remainder so add one page
    
    	$current = ($page/$limit) + 1; // Current page number.
    
    	if (($pages < 1) || ($pages == 0)){
    		$total = 1;
    	} // If $pages is less than one or equal to 0, total pages is 1.
    
    	else{
    		$total = $pages;
    	} // Else total pages is $pages value.
    
    	$first = $page + 1; // The first result.
    
    	if (!((($page + $limit) / $limit) >= $pages) && $pages != 1){
    		$last = $page + $limit;
    	} //If not last results page, last result equals $page plus $limit.
     
    	else{
    		$last = $numrows;
    	} // If last results page, last result equals total number of results.
    
    
    
    
    
    
    	//top info, on the bottom for now...
    
    	//left
    
    	echo("<table border=1 width=\"100%\"><tr><td width=\"33%\" align=\"left\">");
    
    	echo('Results <b>' .  $first . '</b> - <b>' . $last  . '</b> of <b>' . $numrows . '</b>');
    
    	//center
    
    	echo("</td><td width=\"33%\" align=\"center\">");
    
    	if ($page != 0) { // only show back if it isn't the first page
    		$back_page = $page - $limit;
    		echo("<a href=\"$PHP_SELF?page=$back_page&limit=$limit\">back</a>\n");
    // removed ?query=$query from here
    	}
    
    	for ($i=1; $i <= $pages; $i++){ // loop through each page and give link to it.
    		$ppage = $limit*($i - 1);
    		if ($ppage == $page){ // If current page don't give link, just text.
    			echo("<b>$i</b> \n");
    		}
    		else{
    			echo("<a href=\"$PHP_SELF?page=$ppage&limit=$limit\">$i</a>\n");}
    		}
    
    		if (!((($page+$limit) / $limit) >= $pages) && $pages != 1){ // If last page don't give next link.
    			$next_page = $page + $limit;
    			echo("<a href=\"$PHP_SELF?page=$next_page&limit=$limit\">next</a>\n");
    		}
    
    	//right
    
    	echo("</td><td width=\"33%\" align=\"right\">");
    
    	echo('Page <b>' . $current  . '</b> of <b>' . $total . '</b></td>');
    
    
    
    
    
    
    	// data
    
    	echo("<pre>");
    
    	$results = mysql_query("select * from quotes order by id asc limit $page, $limit");
    	while ($data = mysql_fetch_array($results)){
    		echo('<p>#' . $data['id'] . ': ' .  $data['date'] . '<br />' . $data["text"] . '</p>');
    	}
    
    	echo("</pre>");
    I'm already connect to the database--through a template--when this script starts.

    Ideas?

  2. #2
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I haven't picked apart the whole script, but I did note a few problems with it. First off, instead of this:
    Code:
    if (!($limit)){
    	$limit = 10;
    }
    You might want to code it this way:
    Code:
    if (!isset($limit)){
    	$limit = 10;
    }
    Secondly, to get the total number of pages you can simplify your code:
    Code:
    $pages = intval($numrows/$limit); // Number of results pages.
    
    	// $pages now contains int of pages, unless there is a remainder from division.
    
    	if ($numrows % $limit){
    		$pages++;
    	} // has remainder so add one page
    and have this instead:
    Code:
    $pages = ceil($numrows/$limit);
    Also, change this:
    Code:
    if ($page != 0) { // only show back if it isn't the first page
    		$back_page = $page - $limit;
    to this:
    Code:
    if ($page > 1) { // only show back if it isn't the first page
    		$back_page = $page - 1;
    There are probably a few other things I'd fix too, but this is a start. Hope it helps.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  3. #3
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the ridiculously quick response, but unfortunately that didn't work, I'm still getting the same problem...

  4. #4
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    One of the things I do when I have a problem like this is put a die() statement right after the place where a value is being calculated so I can see if that is the expected value.

    For example, where you're calculating the value for $back_page. If that is correct, remove that die() statement, go ot the next variable and repeat that process. That always seems to work for me.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  5. #5
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's obvious that I have some sort of math error in there, but why can't I access a page when I type it in manually?

  6. #6
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I fixed the math error.

    The new math section:

    Code:
    	$pages = ceil($numrows/$limit); //next highest int
    
    	$current = ($page/$limit) + 1; // Current page number.
    
    	if (($pages < 1) || ($pages == 0)){
    		$total = 1;
    	} // if $pages is less than one or equal to 0, total pages is 1.
    
    	else{
    		$total = $pages;
    	} // else total pages is $pages value.
    
    	$first = $page + 1; // the first result.
    
    	if (!((($page + $limit) / $limit) >= $pages) && $pages != 1){
    		$last = $page + $limit;
    	} //if not last results page, last result equals $page plus $limit.
     
    	else{
    		$last = $numrows;
    	} // if last results page, last result equals total number of results
    The new display section:

    Code:
    	echo("</td><td width=\"33%\" align=\"center\">");
    
    	if ($page > 1) { // only show back if it isn't the first page
    		$back_page = $page - 1;
    		echo("<a href=\"$PHP_SELF?page=$back_page\">back</a>\n");
    
    	}
    
    	for ($i=1; $i <= $pages; $i++){ // loop through each page and give link to it.
    		$ipage = $i;
    		//$ppage = $limit*($i - 1);
    		if ($ipage == $page + 1){ // if current page don't give link, just text
    			echo("<b>$i</b> \n");
    		}
    		else{
    			echo("<a href=\"$PHP_SELF?page=$ipage\">$i</a>\n");
    		}
    	}
    
    	if (!((($page+$limit) / $limit) >= $pages) && $pages != 1){ // if last page don't give next link
    		$next_page = $i - 1;
    		//$next_page = $page + $limit;
    		echo("<a href=\"$PHP_SELF?page=$next_page\">next</a>\n");
    	}

  7. #7
    SitePoint Wizard silver trophybronze trophy asp_funda's Avatar
    Join Date
    Jun 2003
    Location
    ether
    Posts
    4,497
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    If you are learning how to do paging, then its better that you do the way you are doing, otherwise if you are looking for a solution for some project or so, then you might consider using eZ Results.
    Our lives teach us who we are.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Me - Photo Blog - Personal Blog - Dev Blog
    iG:Syntax Hiliter -- Colourize your code in WordPress!!

  8. #8
    SitePoint Member
    Join Date
    Jul 2004
    Location
    My Desk
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well it works now.

    Code:
    	if (!isset($limit)){
    		$limit = 10;
    	}
    
    	$page = $_GET['page'];
    
    
    	if (!($page)){
    		$page = 0;
    	}
    
    	$numresults = mysql_query("select * from quotes");
    
    	$numrows = mysql_num_rows($numresults); // number of rows returned
    
    	if ($numrows == 0){
    		echo("No results found matching your query - $query");
    		exit();
    	}
    
    	// math
    
    	$pages = ceil($numrows/$limit); //next highest int
    
    	$current = ceil($page/$limit) + 1; // current page number
    
    	if (($pages < 1) || ($pages == 0)){ // if $pages is less than one or equal to 0, total pages is 1
    		$total = 1;
    	} 
    
    	else{ // else total pages is $pages value
    		$total = $pages;
    	}
    
    	$first = $page * 10 + 1; // the first result
    
    	if ($page + 1  == $total){ // if last results page, last result equals total number of results
    		$last = $numrows;
    	}
     
    	else{ // if not last results page
    		$last = $page *10 + $limit;
    	}
    
    	if ($page >= $pages){ // invlaid page number
    		echo('<p><b>Browse Quotes</b><br />Page: ');
    	}
    
    	else{
    		echo('<p><b>Browse Quotes:</b> ' .  $first .  ' - ' . $last  . ' of ' . $numrows . '<br />Page: ');
    	}
    
    	for ($i=1; $i <= $pages; $i++){ // loop through each page and give link to it.
    		$ipage = $i;
    		if ($ipage == $page + 1){ // if current page, don't give link, just text
    			echo("$i \n");
    		}
    		else{
    			$ipage = $ipage - 1;
    			echo("<a href=\"$PHP_SELF?page=$ipage\">$i</a>\n");
    		}
    	}
    
    	echo("</p>");
    
    	if ($page >= $pages){ 
    		echo("<p>Invalid page number.</p>");
    	}
    
    	// display data
    
    	echo("<pre>");
    
    	$tmp = $page * $limit;
    
    	$results = mysql_query("select * from quotes order by id asc limit $tmp, $limit");
    	while ($data = mysql_fetch_array($results)){
    		echo('<p><a href="quote.php?id=' . $data['id'] . '">#' . $data['id'] . '</a>: ' . $data['date'] . ' | ' . $data['rank'] .  '<br />' . $data["text"] . '</p>');
    	}
    
    	echo("</pre>");

  9. #9
    SitePoint Evangelist Jhorra's Avatar
    Join Date
    Sep 2004
    Location
    Phoenix, Az
    Posts
    551
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I found a very good tutorial for this over at www.phpfreaks.com

  10. #10
    $this->toCD-R(LP); vinyl-junkie's Avatar
    Join Date
    Dec 2003
    Location
    Federal Way, Washington (USA)
    Posts
    1,524
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you want to get into object oriented PHP, here is a great paging class.
    Music Around The World - Collecting tips, trade
    and want lists, album reviews, & more
    Showcase your music collection on the Web

  11. #11
    SitePoint Enthusiast
    Join Date
    Oct 2004
    Location
    Australia
    Posts
    84
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by vinyl-junkie
    If you want to get into object oriented PHP, here is a great paging class.
    I've used that class a number of times. It's wonderfully simple and very very flexible. It can be used with all types of data not just database rows.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •