Creating query results pages

dwilson

I have a table with about 180 records. I want to control the number of records displayed to the user, lets say about 15 records per page, how do I do this. Also I would like to create a next / previous link with also number of pages

Next 1 2 3 4 5 6 7 Previous

downsize

get the total number of records and divide by how many you want per page.

something like
$page_number = 1; //you'll have to use POST or GET to know which page you are on.
$perpage = 15;

then use the LIMIT $page_number, $perpage in you SQL query.

like: SELECT * FROM table LIMIT $page_number, $perpage;

Ravenous

Dude.. take a look on the internet first. Go to Google and search for "php pagination". That should give you loads of links.

Weedpacket

Who needs Google? Just search these forums for pagination.
Here; I'll even start you off because you caught me in an unusually good mood:
http://www.phpbuilder.com/board/showthread.php?s=&threadid=10235043&highlight=pagination

downsize

I tend to give direction over answers. however, I should have stuck with a 'try search' response, since there would undoubtably be some answers/direction within those results.

dwilson

The following code is what I'm using. The problem is that when you first bring up the page it works fine I get the first 10 records but when I click on say the 2 page then I get page cannot be displayed (HTTP 404 - File not found) does anybody know what I'm doing wrong. Thanks everybody for their input and help here is the code I'm using <?php
if ($cn) {
mysql_select_db("db");
$limit = 10;
$query_count = "SELECT * FROM table";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);

    if(empty($page)){    
            $page = 1;     
    } 
    $limitvalue = $page * $limit - ($limit); 

    $sql = "SELECT SUM(table.field),
                    table.field2
                    table2.field1 
            FROM table, table2
            WHERE table.field = table2.field1
            GROUP BY(table.field
	    LIMIT $limitvalue, $limit";

    $result = mysql_query($sql,$cn) or die($sql.mysql_error());

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

    print "<table width=\"100%\" cellpadding=\"4\">";

    $x = 2;
    while($row = mysql_fetch_array($result)) {
        $color = ($x++%2)?'ccccccc':'eeeeeee';
        print   "<tr bgcolor=$color>";
        print   "<td width=\"15%\" align=\"left\">"
                    . $row['field'] .
                "</td>";
        print   "<td width=\"40%\" align=\"left\">"
                    . $row['field2'] .
                "</td>";
        print   "<td width=\"15%\" align=\"center\"></td>";
        print   "<td width=\"15%\" align=\"center\">"
                    . $row[field1] .
                "</td>";
        print   "<td width=\"15%\" align=\"center\"></td>";
        print   "</tr>";
    }
    print "</table>";

    if($page != 1){ 
        $pageprev = $page--; 
        print "<a href=\"$PHP_SELF&page=$pageprev\">PREV"
                .$limit."</a>&nbsp";
    }
    else {
        print ("PREV&nbsp");
    }

    $numofpages = $totalrows / $limit;

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

    if(($totalrows % $limit) != 0){ 
        if($i == $page) {
            print($i."&nbsp;");
        }
        else {
            print("<a href=\"$PHP_SELF&page=$i\">$i</a>&nbsp;");
        }        
	}

	if(($totalrows - ($limit * $page)) > 0) { 
		$pagenext = $page++;
		print("<a href=\"$PHP_SELF?page=$pagenext\">NEXT</a>"); 
	}
	else {
		print("NEXT&nbsp"); 
	}

    mysql_close($cn); 
}

Weedpacket

Obvious question: what is the URL it's looking for (and not finding)?

dwilson

Obvious question: what is the URL it's looking for (and not finding)?

IF I understand your question right I have all my code in a page called test.php for right know If I get it working right I will move it to the right place and rename it to the appropriate name

IF I sound like a inexperienced PHP developer thats because I am

Weedpacket

When your browser displays the 404 page, there is presumably a URL in the address bar.

What is that URL?

dwilson

when I click on say 4 the url is http://acct/visa/test.php&page=4

Weedpacket

Shouldn't that be a ? and not a &? The absence of a domain name is odd, but I suspect that it's supposed to be a relative URL but the unexpected & caused the browser to go wonky.

dwilson

Ok I replaced the & with a ? and I no longer get the error(cannot find page) but when I click on another page the data does not refresh, same data on all pages, do you have any clue. Also thanks for all your help so far

Weedpacket

Ah, I think I can see why: repeating page 1 would be my guess.

This is actually question 1 in the Newbie Forum FAQ. data submitted from a URL querystring will be in the $GET[] array. For your purposes, replace

 if(empty($page)){
$page = 1;
}

with

 if(empty($_GET['page'])){
$page = 1;
} 
else{
$page=$_GET['page'];
}

But I should warn you that there are security holes in that large enough to drive a truck through.

 if(empty($_GET['page']) || (int)$_GET['page']<1){
$page = 1;
} 
else{
$page=(int)$_GET['page'];
}

And then later on you might want to check that someone hasn't tried asking for page 12352523. Figure out how many pages are possible from the count (and you should be using SELECT COUNT() instead of SELECT and counting rows), and if $page is larger than this, force it down to something sensible before using it.