Pagination using PHP Mysqli MVC Stored Procedure
-Lekhulal
Email: lekhulal@gmail.com
------------------------------------------------------------------------------------------------------------------------
Database : db_state
Table name : states
Feild-name :
id
name
------------------------------------------------------------------------------------------------------------------------
Stored procedure :
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_statenames`()
begin
select name from states;
end //
DELIMITER ;
------------------------------------------------------------------------------------------------------------------------
DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_state_limit`(IN start int,
IN max int
)
begin
select * from states LIMIT start, max;end //
DELIMITER ;
------------------------------------------------------------------------------------------------------------------------
Pagination/model/database_connection.php :
<?php
class Database_connection
{
public function new_connection()
{
$mysqli = new mysqli('localhost','root','password','db_state');
return $mysqli;
}
}
?>
------------------------------------------------------------------------------------------------------------------------
Pagination/view/index.php :
<?php
require_once '../model/service_class.php';
$obj = new Service_class();
$res = $obj->select_names();
/* Set current, prev and next page */
$page = (!isset($_GET['page']))? 1 : $_GET['page'];
$prev = ($page - 1);
$next = ($page + 1); /* Max results per page */
$max_results = 3; /* Calculate the offset */
$start = (($page * $max_results) - $max_results);
if($res)
{
$result_user=$mysqli->store_result();
$total_results = mysqli_num_rows($result_user);
$total_pages = ceil($total_results / $max_results);
$result_user->free();
while($mysqli->next_result())
{
$result_user=$mysqli->use_result();
if($result_user instanceof mysqli_result)
{
$result_user->free();
}
}
$result=$obj->select_state_limit($start, $max_results);
}
if($page > 1)
{
?>
<a href="index.php?page=<?php echo $prev;?>">Previous</a>
<?php
}
for($i = max(1, $page - 3); $i <= min($page + 3, $total_pages); $i++) // ...print 5 links
{
?>
<a href="index.php?page=<?php echo $i;?>"><?php echo $i; ?></a>
<?php
}
if($page < $total_pages)
{
?>
<a href="index.php?page=<?php echo $next;?>">Next</a>
<?php
}
if($result)
{
$result_user=$mysqli->use_result();
while($row=$result_user->fetch_array())
{
echo '</br>'.$row['name'].'<br />';
}
$result_user->free();
}
?>
------------------------------------------------------------------------------------------------------------------------
Pagination/model/service_class.php :
<?php
require_once 'database_connection.php';
$con = new Database_connection();
$mysqli = $con->new_connection();
class Service_class
{
public function select_names()
{
global $mysqli;
$query = "call get_statenames()";
return $mysqli->real_query($query);
}
public function select_state_limit($start, $max_results)
{
global $mysqli;
$query = "call get_state_limit($start, $max_results)";
return $mysqli->real_query($query);
}
}
?>
-------------------------------------------Thank you-------------------------------------------------
