PHP and PDO: How to perform paging queries and display data

PHP and PDO: How to query and display data in pages

When developing web applications, querying and displaying data in pages is a very common requirement. Through paging, we can display a certain amount of data at a time, improving page loading speed and user experience. In PHP, the functions of paging query and display of data can be easily realized using the PHP Data Object (PDO) library. This article will introduce how to use PDO in PHP to query and display data by page, and provide corresponding code examples.

1. Create a database and data table

First, we need to create a database and data table to store our data. In this example, we take student information as an example and create a data table named "students". The data table contains the following fields: id (primary key, auto-increment), name, gender, and age.

CREATE DATABASE IF NOT EXISTS mydb;
USE mydb;

CREATE TABLE IF NOT EXISTS students (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(255) NOT NULL,
gender varchar(10) NOT NULL,
age int(11) NOT NULL,
PRIMARY KEY (id)
);

2. Database connection and query

In PHP, we first need to establish a connection with the database and use PDO to perform database query operations. The following is a sample code for a simple database connection and query:

$host = "localhost";
$dbName = "mydb";
$user = " root";
$pass = "password";

try {
$db = new PDO("mysql:host=$host;dbname=$dbName", $user, $pass) ;
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
echo "Connection failed: " . $e->getMessage( );
}

$query = "SELECT * FROM students";
$stmt = $db->prepare($query);
$stmt->execute() ;
$students = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

In the above code, we first define the connection parameters of the database (host name, database name , username and password), and then use the constructor of the PDO class to establish a connection to the database. Next, we set the error mode of PDO to exception mode so that we can catch and handle exceptions when errors occur. Then, we executed a simple query statement "SELECT * FROM students" and saved the query results into the $students array.

3. Display data in paging

When performing paging query, we need to know how many pieces of data there are in total, how many pieces of data are displayed on each page, and the current page number. The following is a simple code example for displaying data in pages:

$perPage = 10; // Display 10 pieces of data per page
$total = count($students); //Total number of data items
$pages = ceil($total / $perPage); //Total number of pages

$page = isset($_GET['page']) ? $_GET ['page'] : 1; // The current page number, the default is 1

$start = ($page - 1) * $perPage; // The starting position of the current page
$query = " SELECT * FROM students LIMIT $start, $perPage";
$stmt = $db->prepare($query);
$stmt->execute();
$students = $stmt- >fetchAll(PDO::FETCH_ASSOC);

foreach($students as $student) {
echo $student['name'] . ", " . $student['gender'] . " , " . $student['age'] . "
";
}

//Paging link
for($i = 1; $i <= $pages; $ i) {
echo "$i ";
}
?>

In the above code, we first define the number of data items displayed on each page as 10. Then, we calculate the total number of data items and the total number of pages (through the rounding up function ceil()). Next, we get the current page number (via the $_GET['page'] parameter) and calculate the starting position of the current page. Use the LIMIT clause to limit the starting position and display quantity of query results to implement paging queries. Finally, we use a foreach loop to iterate through the query results and display the data. Outside the loop, we use a for loop to generate pagination links and switch to different page numbers by clicking on the link.

Conclusion

Through PDO, we can easily implement the functions of paging query and display data. We can use PDO to establish a connection with the database and execute query statements to obtain the result set. We can then paginate the display and generate paginated links as needed. This article provides a simple sample code, hoping to help readers understand how to use PHP and PDO to implement paging query and display data functions.

The above is the detailed content of PHP and PDO: How to perform paging queries and display data. For more information, please follow other related articles on the PHP Chinese website!