PHP MySLi pagination with items per page select option

[josephbupe]

Hi,

 

I am looking for a tutorial or code sample I can use to learn MySQLi pagination with an option to select items per page, like the attached screenshort.

 

Any help is grately appreciated.

 

Regards.

 

joseph

[mac_gyver]

the items per page is just a form with a select/option menu. mysqli is just a database library of functions. implementing either of those things in an existing pagination script just involves writing and testing the code that does what you want.

 

for the items per page. you would produce and output a get method form with the select/option menu, pre-selecting the existing/default choice. you would use an on-change event to submit the form if javascript is enabled, or display a submit button, if javascript is not enabled, using <noscript></noscript> tags. the submitted value would be validated and be used as the items per page value in the pagination code. the existing/default choice would be passed in any pagination links. if i have time, i will post an example.

 

as to using mysqli (or PDO) as the database library in any script, it doesn't matter which library of database functions any code uses (as long as they are not obsolete/depreciated.) the database specific statements in code are at a lower-level 'layer' and is (should be) separated, and abstracted, from the application code. once you learn how to use the database library functions you have chosen, you simply use them at the point they are needed by the application code.

 

if you need to learn how to use the mysqli or PDO database library functions, start with the php.net documentation. you will first need to know how to make a connection to the database server, how to run queries (and test for errors), and how to retrieve the data from the query.

[mac_gyver]

here is the phpfreaks main site pagination script, modified to show dynamic items per page and showing the original mysql and equivalent mysqli database library functions -   

 

<?php
define('SOURCE','mysqli'); // the method/type of data source - mysql, mysqli

switch(SOURCE){
    case 'mysql':
        // database connection info
        $conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error("SQL", E_USER_ERROR);
        $db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);

        // find out how many rows are in the table 
        $sql = "SELECT COUNT(*) FROM numbers";
        $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
        $r = mysql_fetch_row($result);
        $numrows = $r[0];
    break;

    case 'mysqli':
        // database connection info
        $conn = mysqli_connect('localhost','dbusername','dbpass','dbname') or trigger_error("SQL", E_USER_ERROR);

        // find out how many rows are in the table 
        $sql = "SELECT COUNT(*) FROM numbers";
        $result = mysqli_query($conn,$sql) or trigger_error("SQL", E_USER_ERROR);
        $r = mysqli_fetch_row($result);
        $numrows = $r[0];
    break;
}


// number of rows to show per page
$rowsperpage = 10; // (default value when using dynamic rows per page)


// dynamic rows per page, handling and form
$per_page = array(1,5,10,25,50); // choices for select/option menu. also used to limit (min, max) the submitted value

$rowsperpage = isset($_GET['perpage']) ? (int)$_GET['perpage'] : $rowsperpage; // get submitted value or the default

$rowsperpage = max(min($per_page),$rowsperpage); // limit to the minimum value
$rowsperpage = min(max($per_page),$rowsperpage); // limit to the maximum value

// produce rows per page form
$rpp_form = "<form method='get' action=''>\n<select name='perpage' onchange='this.form.submit();'>\n";
foreach($per_page as $item){
    $sel = $rowsperpage == $item ? 'selected' : '';
    $rpp_form .= "<option value='$item' $sel>$item</option>\n";
}
$rpp_form .= "</select>\n<noscript><input type='submit'></noscript>\n</form>\n";


// find out total pages
$totalpages = ceil($numrows / $rowsperpage);

// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
   // cast var as int
   $currentpage = (int) $_GET['currentpage'];
} else {
   // default page num
   $currentpage = 1;
} // end if

// if current page is greater than total pages...
if ($currentpage > $totalpages) {
   // set current page to last page
   $currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
   // set current page to first page
   $currentpage = 1;
} // end if

// the offset of the list, based on current page 
$offset = ($currentpage - 1) * $rowsperpage;

switch(SOURCE){
    case 'mysql':
        // get the info from the db 
        $sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
        $result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
        $rows = array();
        while ($row = mysql_fetch_assoc($result)) {
            $rows[] = $row;
        } // end while
    break;

    case 'mysqli':
        // get the info from the db 
        $sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
        $result = mysqli_query($conn,$sql) or trigger_error("SQL", E_USER_ERROR);
        $rows = array();
        while ($row = mysqli_fetch_assoc($result)) {
            $rows[] = $row;
        } // end while
    break;
}

// while there are rows to be fetched...
foreach($rows as $row) {
   // echo data
   echo $row['id'] . " : " . $row['number'] . "<br />";
} // end foreach


// display dynamic rows per page form
echo $rpp_form;


/******  build the pagination links ******/
// range of num links to show
$range = 3;

// if not on page 1, don't show back links
if ($currentpage > 1) {
   // show << link to go back to page 1
   $_GET['currentpage'] = 1;
   $qs = http_build_query($_GET, '', '&');
   echo " <a href='{$_SERVER['PHP_SELF']}?$qs'><<</a> ";
   // get previous page num
   $prevpage = $currentpage - 1;
   // show < link to go back to 1 page
   $_GET['currentpage'] = $prevpage;
   $qs = http_build_query($_GET, '', '&');  
   echo " <a href='{$_SERVER['PHP_SELF']}?$qs'><</a> ";
} // end if 

// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
   // if it's a valid page number...
   if (($x > 0) && ($x <= $totalpages)) {
      // if we're on current page...
      if ($x == $currentpage) {
         // 'highlight' it but don't make a link
         echo " [<b>$x</b>] ";
      // if not current page...
      } else {
         // make it a link
        $_GET['currentpage'] = $x;
        $qs = http_build_query($_GET, '', '&');
         echo " <a href='{$_SERVER['PHP_SELF']}?$qs'>$x</a> ";
      } // end else
   } // end if 
} // end for
                 
// if not on last page, show forward and last page links        
if ($currentpage != $totalpages) {
   // get next page
   $nextpage = $currentpage + 1;
    // echo forward link for next page
   $_GET['currentpage'] = $nextpage;
   $qs = http_build_query($_GET, '', '&');
   echo " <a href='{$_SERVER['PHP_SELF']}?$qs'>></a> ";
   // echo forward link for lastpage
   $_GET['currentpage'] = $totalpages;
   $qs = http_build_query($_GET, '', '&');
   echo " <a href='{$_SERVER['PHP_SELF']}?$qs'>>></a> ";
} // end if
/****** end build pagination links ******/
?>

[QuickOldCar]

That should be added to the 6 year old article somehow.

http://www.phpfreaks.com/tutorial/basic-pagination