Pagination with Javascript, Php & MySQL

Question

I'm trying to build a simple news page based on this tutorial

http://www.prashantblog.com/use-ajax-filter-mysql-results-set/

It gets data from the database and creates divs for each section, and will filter the results depending on which checkbox is ticked.

I need to limit the results showed to 10 maxiumum, and then add pagination so they user can go to the next page of results, but still the results filtered if they have clicked on a checkbox.

I'm a bit confused on how the logic will work and what I need to do to get the pagination working.

Javascript:

var base_href = '/go/media~';

function makeTable(data) {
  var tbl_body = "";
  $.each(data, function (index, row) {
    var tbl_row = "";
    $.each(row, function (k, v) {
      if (k == 'heading' && 'id' in row) {
        v = '<h2><a class="news" href="' + base_href + row.id + '">' + v + '</a></h2>';
      }
      if (k == 'id' in row) {
        v = '<div style="display:none">' + v + '</div>';
      }
      tbl_row += "<div class='row'><div class='col-md-8 col-md-offset-2'>"
        + v + "   </div></div>";

    });
    tbl_footer = '<div class="row read-more"><div class="col-md-8 col-md-offset-2"><a href="' + base_href + row.id + '">Read more</a></div></div>';
    tbl_body += "<div class='non-white-media'>" + tbl_row + tbl_footer + "</div>";

  });

  return tbl_body;
}

function getEmployeeFilterOptions() {
  var opts = [];
  $checkboxes.each(function () {
    if (this.checked) {
      opts.push(this.name);
    }
  });

  return opts;
}

function updateEmployees(opts) {
  $.ajax({
    type: "POST",
    url: "filter3.php",
    dataType: 'json',
    cache: false,
    data: {filterOpts: opts},
    success: function (records) {
      $('#employees div').html(makeTable(records));
    }
  });
}

var $checkboxes = $("input:checkbox");

$checkboxes.on("change", function () {
  var opts = getEmployeeFilterOptions();
  updateEmployees(opts);
});

updateEmployees();

Filter3.php

<?php

$pdo = new PDO('mysql:host=localhost;dbname=xxx', 'xxx', 'xxx');
$select = 'SELECT id, heading, summary, created_date';
$from = ' FROM media';
$where = ' WHERE TRUE';
$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : array('');
if (in_array("article", $opts)) {
    $where .= " AND article = 1";
}
if (in_array("press_release", $opts)) {
    $where .= " AND press_release = 1";
}
if (in_array("video", $opts)) {
    $where .= " AND video = 1";
}
$sql = $select . $from . $where;
$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);

Victor Carvalho · Accepted Answer · 2015-09-15 14:39:46Z

3

Create a variable to results limit ($limit = 10)
Create a "page" GET parameter
Use mysql "LIMIT [offset],[count]" clause to filter the rows. Offset is the number of rows skiped: $offset = $limit * $page;

Filter3.php will be something like:

$limit = 10;
$page = intval($_GET["page"]);

$pdo = new PDO('mysql:host=localhost;dbname=xxx', 'xxx', 'xxx');
$select = 'SELECT id, heading, summary, created_date';
$from = ' FROM media';
$where = ' WHERE TRUE';
$opts = isset($_POST['filterOpts']) ? $_POST['filterOpts'] : array('');
if (in_array("article", $opts)) {
    $where .= " AND article = 1";
}
if (in_array("press_release", $opts)) {
    $where .= " AND press_release = 1";
}
if (in_array("video", $opts)) {
    $where .= " AND video = 1";
}

$offset = $page * $limit; //skip rows from previous pages
$limit = " LIMIT $offset,$limit";

$sql = $select . $from . $where;

$sql .= $limit;

$statement = $pdo->prepare($sql);
$statement->execute();
$results = $statement->fetchAll(PDO::FETCH_ASSOC);
$json = json_encode($results);
echo($json);

Then you can use the "page" parameter to put links in HMTL/javascript to navigate between pages.

Attention: Consider refactoring the way you do database connection (not hardcode credentials in code) and fill the parameters (the correct way is to parametrize your query and parse/check all parameters received from client).

edited Sep 15, 2015 at 14:39

answered Sep 15, 2015 at 14:20

Victor Carvalho

463 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

joaonlima Over a year ago

Just be aware off possible SQLInjection attacks

Rob88991 Over a year ago

Thank you, how would I create get parameter? I understand what they do but I'm not to sure how to make one

Rob88991 Over a year ago

Yes I will refactor the way the DB connection works, but first I would like to get it all working :) The page is limiting to 10 items now with your code, but I'm not to sure how to add the pagination links with the "page" parameter. If I add ?page=2 after the URL, I still get the first 10 results from the DB. Can you point me in the right direction please?

Victor Carvalho Over a year ago

It's right, "?page=2" should work. Try to insert echo "page=$page offset=$offset" and see if the values are correct, please

Collectives™ on Stack Overflow

Pagination with Javascript, Php & MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related