分页MVC PHP

Question

I would like to make a pagination. Right now my model looks like that :

class Movies extends Connection{

public function getMovies($offset = null) {
$sql = 'SELECT * FROM `movies` ORDER BY `id` DESC';
if ($offset) $sql .= ' LIMIT '.$offset;
return $this->query($sql, null, 'all');
}

Here is my controller

class Controller {

public $movie;
$this->movie = new Movies();
  public function list(){
$movie = $this->movie;
$view = require 'Views/list.php';
}
}

Here is my view :

<div class="single mb-5 mt-5">
<div class="container">
<div class="scroll">
<table id="movie_list">
<thead>
<tr>
  <th data-sort="string">Titre <i class="fa fa-sort"></i></th>
  <th data-sort="string">Genre <i class="fa fa-sort"></i></th>
  <th data-sort="string">Date de sortie <i class="fa fa-sort"></i></th>
  <th data-sort="string">Poster <i class="fa fa-sort"></i></th>
  </tr>
</thead>
<tbody>
<?php foreach($movie->getMovies() as $m) { ?>

  <tr>
  <td id="a"><a href="?p=single&id=<?php echo $m['id']; ?>" class="card-link"><?php echo $m['title']; ?></a></td>
  <td id="b"><?php echo $m['genres']; ?></td>
  <td id="a"><?php echo $m['release_date']; ?></td>
  <td id="b"><img class="img-thumbnail img-fluid" src="<?php echo $movie->getPosterPath($m['poster_path'], false, 92, 138); ?>" alt="<?php echo $m['title']; ?>"></td>
  </tr>

 <?php } ?>
 </tbody>
 </table>
 <script>
  $(document).ready(function($) { 
  $("#movie_list").stupidtable();
  }); 
</script>
</div>
</div>
</div>

For the pagination, I have this code :

$page = (isset($_GET['page']) ? $_GET['page'] : 1);
$perPage = (isset($_GET['per-page']) && ($_GET['per-page']) <= 50 ? $_GET['per-page'] : 5);
$start = ($page > 1) ? ($page * $perPage) - $perPage : 0;


$sql = "select * from movies limit ".$start." , ".$perPage." ";
$total = $db->query("select * from tasks")->num_rows;
$pages = ceil($total / $perPage);

$rows = $db->query($sql);

and

        <ul class="pagination">
            <?php for($i = 1 ; $i <= $pages; $i++): ?>
            <li><a href="?page=<?php echo $i;?>&per-page=<?php echo $perPage;?>"><?php echo $i; ?></a></li>

        <?php endfor; ?>
        </ul>

I have tried to integrate the pagination code into mine but I got stuck. Could you tell me how to do it? Thanks alot

Answer 1

OMG. Please try to use some popular MVC frameworks.

So far just few ideas and comments I have about your code and pagination you want.

1st:

public function getMovies($offset = null) {

That means you was about to pass $offset to this method somewhere. At the moment I don't see where do you do that.

if ($offset) $sql .= ' LIMIT '.$offset;

This part a bit surprising, because usually we need to pass 2 variables OFFSET and LIMIT to LIMIT in query like LIMIT 101,20. But probably you will pass $offset = "101,20" that can be the option but bit weird to me.

2nd:

$total = $db->query("select * from tasks")->num_rows;
$pages = ceil($total / $perPage);

Please never do that again. You are running this query just to get $total number of records but you do SELECT * which means if we have 50 columns in this table and 1M records you receive huge portion of data from DB server which you absolutely not needed. So you can just "SELECT COUNT(*) AS total FROM tasks" which will return you just one row with 1 value!

3rd:

I don't know what is an order your code is executing but since you have $start, $perPage variables precalculated somewhere you can/should pass them to your model when getMovies() moethod is called. So you will get something like this in your view file:

<?php foreach($movie->getMovies($start, $limit) as $m) { ?>

and I would change method definition to:

public function getMovies($offset, $limit) {
...
   if ($offset) $sql .= ' LIMIT '.$offset.','$limit;

PS: This is not the real answer. You should improve your code and avoid sql injections vulnerability. But I hope that will give you some direction you can move.