need help with pagination

[ploppy]

hi. i am starting coding a new db and need to understand how pagination works from scratch. at the simplest level, i have php file that uses this command amongst others

 

$query = "select title, description, date from reslink where category_id=1 LIMIT 10";

 

bearing in mind that this entry would produce 1000 results, i need a way to break the entries down into 10 at a time. a friend of mine suggested i should look at pagination. i would be grateful if someone could give me a kickstart so i can start getting this to work. many thanks

[trq]

a friend of mine suggested i should look at pagination

 

Then you really ought find a tutorial on the subject. A google for php pagination tutorial results in over a million hits. Do we really need to write another?

[ploppy]

fair point thorpe. bearing in mind i was asking for a starting point. didn't want anyone to write code for me. thanks anyway, will check out google.

[kyleldi]

Here's a useful resource that helps eliminate the Google chaos. 

 

http://www.phpfreaks.com/tutorials/43/0.php

[JSHINER]

Be sure to also read the re-writes to some of the code I made to make it function correctly:

 

http://www.phpfreaks.com/forums/index.php/topic,136159.msg575069.html#msg575069

 

[ploppy]

jshiner. i need to incorporate that script into mine. but now sure how. here's mine.

 

<?php

function ShowMyFiles() {
  
    // vars global configuration
    global $dbConn, $theme_path;

    // vars messages
    global $msg;

    // vars template
    global $error_msg, $pagination, $username, $status, $files, $owner, $test;

    if ($err) {
      $error_msg = $msg['20191'];
    }
    
    //$links_obj                      = new clsLink;


    // get file listing
    //$query  = "select title, description, date from idx_reslink where category_id=1 LIMIT 10";
    //$links_obj->query               = $query;
    //$links_obj->table_name          = "idx_reslink";
    //$links_obj->date_format         = $msg["10151"];
    //$links_obj->max_rows            = 100;
    //$files                           = $links_obj->Display();

$query      = "select title, description from idx_reslink where category_id=1 LIMIT 10";
    $result     = $dbConn->Execute($query);
    //$files = $result->Fields("url");
// $owner = $result->Fields("owner");
// $date = $result->Fields("date");

    // $files = explode ( " ", $files );

$test = '';
   for($i = 0;$i < $result->RecordCount(); $i++)
   {
   $date = $result->Fields("date");
   $description = $result->Fields("description");
   $title = $result->Fields("title"); 
   
   $test .= '<table width="100%%"  border="0">
<tr>
    <td></td>
  </tr>
</table>

<table cellpadding="2" cellspacing="1" border="0" align="center" width="100%" class="tbl_border_mem">
<tr class="tbl_caption_mem">
  <td width="16%"><strong>Date</strong></td>
  <td width="84%"><strong>Description</strong></td>
  
</tr>
<tr class="tbl_caption_mem">
  <td valign="top">'.$title.'</td>
  <td valign="top">'.$description.'</td>
  
  </tr>
</table>';
$result->movenext();
}
$files = $test;
   DisplayTemplate($theme_path . "cp/pl37-39files.html", "\$files,\$pagination,\$error_msg");
  }

 

and the code from the link you posted with corrections:

 

<?php
    
# set up db or die trying
    $db = mysql_connect("localhost", "root", "") or die("Couldn't connect to db!");
    mysql_select_db("link2") or die("Couldn't select db!");

   	$limit            = 5;                
    $query    		  = "SELECT count(*) FROM idx_reslink";     
    $count        	  = mysql_query($query);
    $totalrows        = mysql_result($count,0,0);  

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

    $limitvalue = $page * $limit - ($limit);  
    $query  = "SELECT title, description FROM idx_reslink LIMIT $limitvalue, $limit";         
    $result = mysql_query($query) or die("Error: " . mysql_error());  

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

    $bgcolor = "#E0E0E0"; // light gray 
     
    while($row = mysql_fetch_array($result)){ 
    
        if ($bgcolor == "#E0E0E0"){ 
            $bgcolor = "#FFFFFF"; 
        }else{ 
            $bgcolor = "#E0E0E0"; 
        } 

        echo 'Title:', $row['title'], '';
    } 

    if($page != 1){  
        $pageprev = $page-1; 
         
        echo("<a class=\"nextprev\" href=\"index.php?page=$pageprev\">&#171; PREV</a>");  
    }else{ 
        echo("<span class=\"nextprev\">&#171; PREV</span>"); 
    } 

    $numofpages = $totalrows / $limit;  
     
    for($i = 1; $i <= $numofpages; $i++){ 
        if($i == $page){ 
            echo($i." "); 
        }else{ 
            echo("<a href=\"index.php?page=$i\">$i</a> "); 
        } 
    } 


    if(($totalrows - ($limit * $page)) > 0){ 
        $pagenext = $page+1; 
          
        echo("<a class=\"nextprev\" href=\"index.php?page=$pagenext\">NEXT &#187;</a>");  
    }else{ 
        echo("<span class=\"nextprev\">NEXT &#187;</span>");  
    } 

    if(($totalrows - ($limit * $page)) > 0){ 
        $pagenext = $page++; 
          
        echo("<a href=\"index.php?page=$pagenext\">NEXT".$limit."</a>");  
    }else{ 
        echo("NEXT".$limit);  
    } 
     
    mysql_free_result($result);
?>

 

you will see all output is to html file. need to keep it like this so it integrates with the rest of the site. perhaps it would be easier to amend the script i already have? comments welcome. many thanks

 

 

[Yesideez]

I think the best thing to do would be to read up on how to write one then start writing your own.

 

If you don't know how it's done and use someone else's script you'll be more stumped if you came across problems which you inevitably will do.

 

At least if you write your own you'll more much more of an insight into how it's running and be much more armed in fixing any problems that appear.

 

First choose a page size - how many items do you want to show on each page? This depends on how much information is being shown from the database for each entry returned.

 

Using LIMIT offset,pagesize in the end of a MySQL query will give you a limited number of entries from the database.

 

You can pass the page numbers in the "next" and "previous" links on your page. Just remember to disable the links if the user is on the first or last page.  A link would look something like:

www.mysite.com/showresults.php?page=3

 

Just $_GET the value of page from the URL at the start of your script and go from there.

[Yesideez]

Just noticed that if you're having 1000 results with 10 items per page then you might also want to add a "go to page" link - use either a drop-down selection form with a submit button or some Javascript to change page automatically.

 

I've written something similar on my website: www.pictureinthesky.net

 

If you want the code for my guestbook let me know and I'll PM it to you later when I get back from work.

[ploppy]

hi yesideez. wish i could write something myself :-) only been involved with php for 4 weeks. but, i have a good understanding of the language, it's just a case of fitting it all together. all components works seperately, but when you bring them together <CRUNCH> :-) i am also limited to using the above script to output results to html. if i come away from this, it goes offsite and cannot retrieve other information. member etc. can this script be modified to output to html? many thanks for your time.

[Yesideez]

I'm a bit lost - which script?

[ploppy]

if you look at the first script, that is mine that i have at the moment and the one i need to modify.thanks