Pagination Using PHP and MySQL

CREATE TABLE `news` (
  `id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `publish_date` date NOT NULL DEFAULT current_timestamp(),
  `user` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `news` (`id`, `title`, `content`, `publish_date`, `user`) VALUES
(1, '1-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(2, '2-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(3, '3-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(4, '4-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(5, '5-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(6, '6-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(7, '7-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(8, '8-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(9, '9-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(10, '10-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(11, '11-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),
(12, '12-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag');

ALTER TABLE `news`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `news`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
COMMIT;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

CREATE TABLE `news` (

`id` int(11) NOT NULL,

`title` varchar(255) NOT NULL,

`content` text NOT NULL,

`publish_date` date NOT NULL DEFAULT current_timestamp(),

`user` varchar(255) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `news` (`id`, `title`, `content`, `publish_date`, `user`) VALUES

(1, '1-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(2, '2-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(3, '3-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(4, '4-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(5, '5-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(6, '6-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(7, '7-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(8, '8-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(9, '9-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(10, '10-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(11, '11-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag'),

(12, '12-The standard Lorem Ipsum passage, used since the 1500s', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.', '2023-01-08', 'Team CodersMag');

ALTER TABLE `news`

ADD PRIMARY KEY (`id`);

ALTER TABLE `news`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

COMMIT;

<?php

/**
 * @package Database Config File
 *
 * @author WebHaunt Team
 *
 * @email  info@webhaunt.com
 *   
 */

class DbConnection
{
    // Connect to an ODBC database using an alias
    private $_dbHostName = "localhost";
    private $_dbName = "demo_DB";
    private $_dbUserName = "root";
    private $_dbPassword = "";
    private $_conn;
    // __construct
    public function __construct()
    {
        try {
            $this->_conn = new PDO("mysql:host=$this->_dbHostName;dbname=$this->_dbName", $this->_dbUserName, $this->_dbPassword, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));
            // set the PDO error mode to exception
            $this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            echo "Error connecting to MySQL server: " . $e->getMessage();
        }
    }
    // return Connection
    public function returnConnection()
    {
        return $this->_conn;
    }
}


?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

<?php

/**

* @package Database Config File

*

* @author WebHaunt Team

*

* @email info@webhaunt.com

*

*/

class DbConnection

{

// Connect to an ODBC database using an alias

private $_dbHostName = "localhost";

private $_dbName = "demo_DB";

private $_dbUserName = "root";

private $_dbPassword = "";

private $_conn;

// __construct

public function __construct()

{

try {

$this->_conn = new PDO("mysql:host=$this->_dbHostName;dbname=$this->_dbName", $this->_dbUserName, $this->_dbPassword, array(PDO::MYSQL_ATTR_FOUND_ROWS => true));

// set the PDO error mode to exception

$this->_conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

} catch (PDOException $e) {

echo "Error connecting to MySQL server: " . $e->getMessage();

}

// return Connection

public function returnConnection()

{

return $this->_conn;

}

?>

<?php

/**
 * @package News Paginationm Module
 *
 * @author WebHaunt Team
 *
 * @email  info@webhaunt.com
 *   
 */

// include Database Config file
include("DBConnection.php");

// Create News Class
class News
{
    protected $_db;
    private $_page;
    private $_rowPerPage;
    private $_start;

    public function setPage($page)
    {
        $this->_page = $page;
    }

    public function setRowPerPage($rowPerPage)
    {
        $this->_rowPerPage = $rowPerPage;
    }
    public function setStart($start)
    {
        $this->_start = $start;
    }

    // __construct - Database Config File
    public function __construct()
    {
        $this->_db = new DBConnection();
        $this->_db = $this->_db->returnConnection();
    }

    // get News list from mysql databse
    public function getNewsList()
    {
        try {
            $sql = "SELECT * FROM news ";
            $limit = " limit " . $this->_start . "," . $this->_rowPerPage;
            $query = $sql . $limit;
            $stmt = $this->_db->prepare($query);

            $stmt->execute();
            $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            return $result;
        } catch (Exception $error) {
            die("Exception Caught!: " . $error->getMessage());
        }
    }

    // count Total News form database
    public function countTotalNews()
    {
        try {
            $sql = "SELECT * FROM news";
            $stmt = $this->_db->prepare($sql);
            $stmt->execute();
            $count = $stmt->rowCount();
            return $count;
        } catch (Exception $error) {
            die("Exception Caught!: " . $error->getMessage());
        }
    }
}
?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

<?php

/**

* @package News Paginationm Module

*

* @author WebHaunt Team

*

* @email info@webhaunt.com

*

*/

// include Database Config file

include("DBConnection.php");

// Create News Class

class News

{

protected $_db;

private $_page;

private $_rowPerPage;

private $_start;

public function setPage($page)

{

$this->_page = $page;

}

public function setRowPerPage($rowPerPage)

{

$this->_rowPerPage = $rowPerPage;

}

public function setStart($start)

{

$this->_start = $start;

}

// __construct - Database Config File

public function __construct()

{

$this->_db = new DBConnection();

$this->_db = $this->_db->returnConnection();

}

// get News list from mysql databse

public function getNewsList()

{

try {

$sql = "SELECT * FROM news ";

$limit = " limit " . $this->_start . "," . $this->_rowPerPage;

$query = $sql . $limit;

$stmt = $this->_db->prepare($query);

$stmt->execute();

$result = $stmt->fetchAll(\PDO::FETCH_ASSOC);

return $result;

} catch (Exception $error) {

die("Exception Caught!: " . $error->getMessage());

}

// count Total News form database

public function countTotalNews()

{

try {

$sql = "SELECT * FROM news";

$stmt = $this->_db->prepare($sql);

$stmt->execute();

$count = $stmt->rowCount();

return $count;

} catch (Exception $error) {

die("Exception Caught!: " . $error->getMessage());

}

?>

<?php
// include News Class
include_once 'class/News.php';

// row per page
$rowperpage = 5;
$page = 1;
$start = 0;

// check page 
if (!empty($_GET["page"])) {
    $page = $_GET["page"];
    $start = ($page - 1) * $rowperpage;
}

$newsInfo = new News();
$newsInfo->setRowPerPage($rowperpage);
$newsInfo->setStart($start);
$newsInfo->setPage($page);
$countTotalNews = $newsInfo->countTotalNews();
$newsList = $newsInfo->getNewsList();

include('templates/header.php');
$per_page_html = '';
?>
<div class="container">
  <h2 class="mt-4 text-center">Create Pagination Using PHP and MySQL</h2>
  <br><br>
  <div class="row">
    <div class="col-lg-10 offset-1">
      <div class="card">
        <div class="card-body">
          <table class="table table-striped">
            <thead>
              <tr>
                <th style="width: 30%;">Title</th>
                <th style="width: 45%;">Content</th>
                <th style="width: 15%;">Publish Date</th>
                <th style="width: 10%;">User</th>
              </tr>
            </thead>
            <tbody>
              <?php
                foreach ($newsList as $element) { ?>
                <tr>
                  <td><?php echo $element['title']; ?></td>
                  <td><?php echo $element['content']; ?></td>
                  <td><?php echo $element['publish_date']; ?></td>
                  <td><?php echo $element['user']; ?></td>
                </tr>
              <?php } ?>
            </tbody>
          </table>
          <?php
            //Page Navigation process
            if (!empty($countTotalNews)) {
                $per_page_html .= '<ul class="pagination float-right">';
                $pageCount = ceil($countTotalNews / $rowperpage);
                if ($pageCount > 1) {
                    for ($i = 1; $i <= $pageCount; $i++) {
                        if ($i == $page) {
                            $per_page_html .= '<li class="page-item active"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';
                        } else {
                            $per_page_html .= '<li class="page-item"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';
                        }
                    }
                }
                $per_page_html .= "</ul>";
            }
            echo $per_page_html;
            ?>
          <br><br><br><br>
        </div>
      </div>
    </div>
  </div>
</div>
<?php include('templates/footer.php'); ?>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

<?php

// include News Class

include_once 'class/News.php';

// row per page

$rowperpage = 5;

$page = 1;

$start = 0;

// check page

if (!empty($_GET["page"])) {

$page = $_GET["page"];

$start = ($page - 1) * $rowperpage;

}

$newsInfo = new News();

$newsInfo->setRowPerPage($rowperpage);

$newsInfo->setStart($start);

$newsInfo->setPage($page);

$countTotalNews = $newsInfo->countTotalNews();

$newsList = $newsInfo->getNewsList();

include('templates/header.php');

$per_page_html = '';

?>

<h2 class="mt-4 text-center">Create Pagination Using PHP and MySQL</h2>

<thead>

<tr>

<th style="width: 30%;">Title</th>

<th style="width: 45%;">Content</th>

<th style="width: 15%;">Publish Date</th>

</tr>

</thead>

<tbody>

<?php

foreach ($newsList as $element) { ?>

<tr>

</tr>

<?php } ?>

</tbody>

</table>

<?php

//Page Navigation process

if (!empty($countTotalNews)) {

$per_page_html .= '<ul class="pagination float-right">';

$pageCount = ceil($countTotalNews / $rowperpage);

if ($pageCount > 1) {

for ($i = 1; $i <= $pageCount; $i++) {

if ($i == $page) {

$per_page_html .= '<li class="page-item active"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';

} else {

$per_page_html .= '<li class="page-item"><a class="page-link" href="?page=' . $i . '">' . $i . '</a></li>';

}

$per_page_html .= "</ul>";

}

echo $per_page_html;

?>

</div>

<?php include('templates/footer.php'); ?>

<!DOCTYPE html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="WebHaunt">
  <meta name="author" content="WebHaunt">
  <title>Create Pagination Using PHP and MySQL | WebHaunt</title>
  <link rel="icon" href="https://webhaunt.com/wp-content/uploads/2016/11/webhaunt-150x142.png" sizes="32x32" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/css/bootstrap.min.css" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/5.7.2/css/all.min.css" />
  <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/simple-line-icons/2.4.1/css/simple-line-icons.css" />
  <link href="https://fonts.googleapis.com/css?family=Lato:300,400,700,300italic,400italic,700italic" rel="stylesheet" type="text/css">
  <link href="assets/css/style.css" rel="stylesheet">
</head>

<body>
  <nav class="navbar navbar-expand-lg navbar-dark static-top" style="background: linear-gradient(120deg, rgba(11,82,119,1) 0%, rgba(66,103,178,1) 100%);">
    <div class="container">
      <a class="navbar-brand font-weight-bold" href="https://webhaunt.com">
        <h1>Web Haunt</h1>
      </a>
      <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarResponsive" aria-controls="navbarResponsive" aria-expanded="false" aria-label="Toggle navigation">
        <span class="navbar-toggler-icon"></span>
      </button>
      <div class="collapse navbar-collapse" id="navbarResponsive">
        <ul class="navbar-nav ml-auto">
          <li class="nav-item active">
            <a class="nav-link" href="https://webhaunt.com">Home
              <span class="sr-only">(current)</span>
            </a>
          </li>
          <li class="nav-item">
            <a class="nav-link" href="https://webhaunt.com/php-script-free-demo">Live Demos</a>
          </li>
        </ul>
      </div>
    </div>
  </nav>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

<!DOCTYPE html>

<head>

<title>Create Pagination Using PHP and MySQL | WebHaunt</title>

</head>

<body>

<h1>Web Haunt</h1>

</a>

</button>

<a class="nav-link" href="https://webhaunt.com">Home

<span class="sr-only">(current)</span>

</a>

</li>

<a class="nav-link" href="https://webhaunt.com/php-script-free-demo">Live Demos</a>

</li>

</ul>

</div>

</nav>

<footer id="colophon" class="site-footer" role="contentinfo">
		
<div class="site-info">
	<div class="site-info-inside">

		<div class="container">

			<div class="row">
				<div class="col">
					<div class="credits-wrapper">
						<div class="credits credits-blog">Copyright © 2016 - 2023  <a href="https://webhaunt.com" title="webhaunt.com">WEBHAUNT.COM</a>  All rights reserved.</div>
					</div><!-- .credits -->
				</div><!-- .col -->
			</div><!-- .row -->

		</div><!-- .container -->

	</div><!-- .site-info-inside -->
</div><!-- .site-info -->
	</footer>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/4.3.1/js/bootstrap.bundle.min.js"></script>
<script src="assets/js/todo.js"></script>
</body>

</html>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

</div>

</div>

</div>

</div>

</div>

</div>

</footer>

</body>

</html>