Pagination using PHP Mysqli MVC Stored procedure

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-------------------------------------------------