untung99.homes: How to Create Pagination with PHP and MySQL

falma.u99 Oktober 14, 2023Juli 25, 2023 create, mysql, pagination, untung99homes

Untung99 menawarkan beragam permainan yang menarik, termasuk slot online, poker, roulette, blackjack, dan taruhan olahraga langsung. Dengan koleksi permainan yang lengkap dan terus diperbarui, pemain memiliki banyak pilihan untuk menjaga kegembiraan mereka. Selain itu, Untung99 juga menyediakan bonus dan promosi menarik yang meningkatkan peluang kemenangan dan memberikan nilai tambah kepada pemain.

Berikut adalah artikel atau berita tentang Harian untung99.homes dengan judul untung99.homes: How to Create Pagination with PHP and MySQL yang telah tayang di untung99.homes terimakasih telah menyimak. Bila ada masukan atau komplain mengenai artikel berikut silahkan hubungi email kami di koresponden@untung99.homes, Terimakasih.

For this tutorial we will be creating pagination with PHP and MySQL, pagination is how users will navigate certain parts of your website that contains large amounts of data, we will be using MySQL in this tutorial, we will select records from a database and display them in a table using HTML and PHP with pagination.

We will display the next and previous buttons in this pagination, as well as the first and last pages, and the pages in between with the current page being highlighted.

We need to do a bit more work than just showing the next and previous buttons, but this is because we can change the code, change it to how we want it, if we want to remove the pagination pages we can do so.

Pagination.php

Let’s go ahead and create the pagination file, we will call this pagination.php.

We now need to connect to our database, we can do so with the following line of code:

$mysqli = mysqli_connect('localhost', 'root', '', 'pagination');

You can change the connection information, replace the text with your MySQL details: mysqli_connect(YOUR_HOST, YOUR_USERNAME, YOUR_PASSWORD, YOUR_DATABASE)

For the purpose of this tutorial I have created the database pagination, below is the SQL statement to this database, run the SQL statement with phpMyAdmin, or your preferred database management tool.

CREATE DATABASE IF NOT EXISTS `pagination` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `pagination`;

CREATE TABLE IF NOT EXISTS `students` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `age` tinyint(1) NOT NULL,
  `joined` varchar(10) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8;

INSERT INTO `students` (`id`, `name`, `age`, `joined`) VALUES
(1, 'David Deacon', 23, '17/08/2018'),
(2, 'Sheri Warner', 19, '03/05/2018'),
(3, 'Sean Glover', 24, '24/07/2018'),
(4, 'John West', 17, '13/08/2018'),
(5, 'Rufus Clarke', 20, '28/07/2018'),
(6, 'Roosevelt Myers', 20, '25/07/2018'),
(7, 'Elvira Andrews', 22, '02/07/2018'),
(8, 'Richard	Cook', 26, '19/07/2018'),
(9, 'Lorenzo	Harris', 23, '01/07/2018'),
(10, 'Eduardo	Hoffman', 17, '03/07/2018'),
(11, 'Jeanne Fisher', 20, '13/08/2018'),
(12, 'Tracy Bowers', 30, '07/07/2018'),
(13, 'Heidi Lawrence', 18, '04/06/2018'),
(14, 'Tara Holland', 25, '01/07/1991'),
(15, 'Grant Edwards', 22, '22/06/2018'),
(16, 'Bradford Green', 29, '02/05/2018'),
(17, 'Gwen Schultz', 20, '02/05/2018'),
(18, 'Hope Dawson', 28, '21/08/2018'),
(19, 'Florence Osborne', 19, '17/05/2018'),
(20, 'Rickey Poole', 26, '28/06/2018'),
(21, 'Casey Sutton', 28, '06/07/2018'),
(22, 'Willie Lowe', 23, '11/05/2018'),
(23, 'Stephen	Schultz', 28, '15/07/2018'),
(24, 'Eileen Lynch', 18, '12/06/2018'),
(25, 'Aaron Ruiz', 29, '02/05/2018'),
(26, 'Mae Murray', 30, '24/06/2018'),
(27, 'Regina Hanson', 21, '26/07/2018'),
(28, 'Cameron	Mclaughlin', 20, '29/07/2018'),
(29, 'Earl Hale', 17, '30/06/2018'),
(30, 'Marta Blair', 24, '10/06/2018'),
(31, 'Alberta	Silva', 22, '05/06/2018'),
(32, 'Joanna Holmes', 20, '20/05/2018'),
(33, 'Alex Brock', 30, '12/05/2018'),
(34, 'Colin Wright', 19, '28/05/2018'),
(35, 'Peter Schmidt', 25, '10/07/2018'),
(36, 'Joshua Price', 27, '13/07/2018'),
(37, 'Elias Chandler', 22, '19/07/2018'),
(38, 'Stanley	Ross', 21, '02/06/2018'),
(39, 'Vera Cole', 26, '02/05/2018'),
(40, 'Johnny Daniels', 29, '19/07/2018'),
(41, 'Yvonne Hopkins', 21, '16/07/2018');

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

ALTER TABLE `students` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=42;

Let’s get back to the pagination PHP file, what we need to do now is to create some important variables that we will use for the pagination.

// Get the total number of records from our table "students".
$total_pages = $mysqli->query('SELECT COUNT(*) FROM students')->fetch_row()[0];

// Check if the page number is specified and check if it's a number, if not return the default page number which is 1.
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1;

// Number of results to show on each page.
$num_results_on_page = 5;

With the first variable we are getting the total number of rows from the students table in our database, this will be used to calculate how many pages there will be. The second variable is used to determine which page the user is on, for example, if the URL is pagination.php?page=3 then the page number will be 3, if the page doesn’t exist in the URL it will default to 1. The third variable determines how many results will be shown on each page, for example, if we wanted to show 10 results (list of 10 students) we will change the number to 10, the default is set to 5.

So now we need to get the records from the database for the current page, the following code below will do just that.

if ($stmt = $mysqli->prepare('SELECT * FROM students ORDER BY name LIMIT ?,?')) {
	// Calculate the page to get the results we need from our table.
	$calc_page = ($page - 1) * $num_results_on_page;
	$stmt->bind_param('ii', $calc_page, $num_results_on_page);
	$stmt->execute(); 
	// Get the results...
	$result = $stmt->get_result();
	$stmt->close();
}

There are a few things that are going on in the above code, the first thing we do is prepare the statement, this prevents SQL injection, if you do not do this you may be at risk of being hacked, the $calc_page variable basically determines the start index in our table, after, we bind the variables (i is for integer), and then execute the SQL statement. The result will be stored in the $result variable.

Now we need to display these results, the results will be displayed in a table, you can display the results how you want but for this tutorial, we will use a table.


	fetch_assoc()): ?>


	
		Name
		Age
		Join Date

The above code will basically display each student in the table with their name, age, and joined date.

Now we need to display the pagination.

 0): ?>

The first thing we do here is to check if there are more than 1 page, no need to display the pagination if there is only 1 page. We then check if the current page is greater than 1, if it is, show the previous button, and then if the page is greater than 3 we can show the first page, which is 1.

After that we display the 2 pages before and after the current page, so if the page is 5 it will be displayed like this 1 … 3 4 5 6 7 … 14, it’s basically the same as we have done after that but in reverse.

Now we need to add some style to our pagination and table, the following CSS code will do just that.

table {
	border-collapse: collapse;
	width: 500px;
}
td, th {
	padding: 10px;
}
th {
	background-color: #54585d;
	color: #ffffff;
	font-weight: bold;
	font-size: 13px;
	border: 1px solid #54585d;
}
td {
	color: #636363;
	border: 1px solid #dddfe1;
}
tr {
	background-color: #f9fafb;
}
tr:nth-child(odd) {
	background-color: #ffffff;
}
.pagination {
	list-style-type: none;
	padding: 10px 0;
	display: inline-flex;
	justify-content: space-between;
	box-sizing: border-box;
}
.pagination li {
	box-sizing: border-box;
	padding-right: 10px;
}
.pagination li a {
	box-sizing: border-box;
	background-color: #e2e6e6;
	padding: 8px;
	text-decoration: none;
	font-size: 12px;
	font-weight: bold;
	color: #616872;
	border-radius: 4px;
}
.pagination li a:hover {
	background-color: #d4dada;
}
.pagination .next a, .pagination .prev a {
	text-transform: uppercase;
	font-size: 12px;
}
.pagination .currentpage a {
	background-color: #518acb;
	color: #fff;
}
.pagination .currentpage a:hover {
	background-color: #518acb;
}

You should now have something that will look like this:

Source

The full pagination.php source:

query('SELECT * FROM students')->num_rows;

// Check if the page number is specified and check if it's a number, if not return the default page number which is 1.
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1;

// Number of results to show on each page.
$num_results_on_page = 5;

if ($stmt = $mysqli->prepare('SELECT * FROM students ORDER BY name LIMIT ?,?')) {
	// Calculate the page to get the results we need from our table.
	$calc_page = ($page - 1) * $num_results_on_page;
	$stmt->bind_param('ii', $calc_page, $num_results_on_page);
	$stmt->execute(); 
	// Get the results...
	$result = $stmt->get_result();
	?>

				fetch_assoc()): ?>

			
				
					Name
					Age
					Join Date
				
			 0): ?>

	close();
}
?>

Conclusion

Congratulations, you have successfully created a fully working pagination for your website, there are many different ways you can create pagination, this way is probably one of the best and it’s secure! Remember to subscribe and share, like, follow our social media accounts if you like this tutorial, help our site grow and we will provide lots of more useful tutorials.