GALAXY TECH
Chuyên gia Công nghệ
Thứ tư, 29/01/2020 | 00:00 GMT+7

Cách triển khai phân trang trong MySQL với PHP trên Ubuntu 18.04

Phân trang là khái niệm giới hạn số lượng hàng được trả về trong một tập bản ghi thành các trang riêng biệt, có trật tự để cho phép chuyển dễ dàng giữa chúng, vì vậy khi có một tập dữ liệu lớn, bạn có thể cấu hình phân trang của bạn để chỉ trả về một số hàng cụ thể trên mỗi trang.

Ví dụ: phân trang có thể giúp tránh quá tải user khi một cửa hàng trực tuyến chứa hàng nghìn sản phẩm bằng cách giảm số lượng mặt hàng được liệt kê trên một trang, vì user thường không cần xem mọi sản phẩm. Một ví dụ khác là một ứng dụng hiển thị các bản ghi trên thiết bị di động; cho phép phân trang trong trường hợp như vậy sẽ chia các bản ghi thành nhiều trang có thể phù hợp hơn trên màn hình.

Bên cạnh những lợi ích trực quan cho user cuối, phân trang làm cho các ứng dụng nhanh hơn vì nó làm giảm số lượng bản ghi được trả về cùng một lúc. Điều này hạn chế dữ liệu cần được truyền giữa client và server , giúp bảo toàn tài nguyên server như RAM.

Trong hướng dẫn này, bạn sẽ xây dựng một tập lệnh PHP để kết nối với database của bạn và triển khai phân trang cho tập lệnh của bạn bằng cách sử dụng mệnh đề MySQL LIMIT .

Yêu cầu

Trước khi bắt đầu, bạn cần các thành phần sau :

Bước 1 - Tạo user database và database thử nghiệm

Trong hướng dẫn này, bạn sẽ tạo một tập lệnh PHP sẽ kết nối với database MySQL, tìm nạp các bản ghi và hiển thị chúng trong một trang HTML bên trong một bảng. Bạn sẽ kiểm tra tập lệnh PHP theo hai cách khác nhau từ trình duyệt web . Đầu tiên, tạo một tập lệnh mà không có bất kỳ mã phân trang nào để xem các bản ghi được hiển thị như thế nào. Thứ hai, thêm mã chuyển trang trong file PHP để hiểu cách phân trang hoạt động thực tế.

Mã PHP yêu cầu user MySQL cho mục đích xác thực và database mẫu để kết nối. Trong bước này, bạn sẽ tạo một user không phải root cho database MySQL của bạn , một database mẫu và một bảng để kiểm tra tập lệnh PHP.

Để bắt đầu đăng nhập vào server . Sau đó đăng nhập vào server MySQL của bạn bằng lệnh sau:

  • sudo mysql -u root -p

Nhập password gốc của server MySQL của bạn và nhấn ENTER để tiếp tục. Sau đó, bạn sẽ thấy dấu nhắc MySQL. Để tạo database mẫu, ta sẽ gọi test_db trong hướng dẫn này, hãy chạy lệnh sau:

  • Create database test_db;

Bạn sẽ thấy kết quả sau:

Output
Query OK, 1 row affected (0.00 sec)

Sau đó, tạo test_user và cấp cho user tất cả các quyền đối với test_db . Thay thế PASSWORD bằng một giá trị mạnh:

  • GRANT ALL PRIVILEGES ON test_db.* TO 'test_user'@'localhost' IDENTIFIED BY 'PASSWORD';
Output
Query OK, 1 row affected (0.00 sec)

Reload các quyền MySQL với:

  • FLUSH PRIVILEGES;
Output
Query OK, 1 row affected (0.00 sec)

Tiếp theo, chuyển sang database test_db để bắt đầu làm việc trực tiếp trên database test_db :

  • Use test_db;
Output
Database changed

Bây giờ hãy tạo một bảng products . Bảng sẽ chứa các sản phẩm mẫu của bạn — đối với hướng dẫn này, bạn sẽ chỉ yêu cầu hai cột cho dữ liệu. Cột product_id sẽ đóng role là khóa chính để xác định duy nhất từng bản ghi. Cột này sẽ được đặt thành AUTO_INCREMENT để tạo product_id mới cho mỗi mục được chèn vào. Bạn sẽ sử dụng trường product_name để phân biệt từng mặt hàng theo tên:

  • Create table products (product_id BIGINT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(50) NOT NULL ) Engine = InnoDB;
Output
Query OK, 0 rows affected (0.02 sec)

Để thêm mười sản phẩm thử nghiệm vào bảng products chạy các câu lệnh SQL sau:

  • Insert into products(product_name) values ('WIRELESS MOUSE');
  • Insert into products(product_name) values ('BLUETOOTH SPEAKER');
  • Insert into products(product_name) values ('GAMING KEYBOARD');
  • Insert into products(product_name) values ('320GB FAST SSD');
  • Insert into products(product_name) values ('17 INCHES TFT');
  • Insert into products(product_name) values ('SPECIAL HEADPHONES');
  • Insert into products(product_name) values ('HD GRAPHIC CARD');
  • Insert into products(product_name) values ('80MM THERMAL PRINTER');
  • Insert into products(product_name) values ('HDMI TO VGA CONVERTER');
  • Insert into products(product_name) values ('FINGERPRINT SCANNER');

Bạn sẽ thấy kết quả này:

Output
Query OK, 1 row affected (0.02 sec)

Xác minh các sản phẩm đã được đưa vào bảng bằng lệnh:

  • select * from products;

Bạn sẽ thấy các sản phẩm trong kết quả của bạn trong hai cột:

Output
+------------+-----------------------+ | product_id | product_name          | +------------+-----------------------+ |          1 | WIRELESS MOUSE        | |          2 | BLUETOOTH SPEAKER     | |          3 | GAMING KEYBOARD       | |          4 | 320GB FAST SSD        | |          5 | 17 INCHES TFT         | |          6 | SPECIAL HEADPHONES    | |          7 | HD GRAPHIC CARD       | |          8 | 80MM THERMAL PRINTER  | |          9 | HDMI TO VGA CONVERTER | |         10 | FINGERPRINT SCANNER   | +------------+-----------------------+ 10 rows in set (0.00 sec)

Thoát MySQL:

  • quit;

Với database mẫu, bảng và dữ liệu thử nghiệm đã có sẵn, bây giờ bạn có thể tạo một tập lệnh PHP để hiển thị dữ liệu trên một trang web.

Bước 2 - Hiển thị các bản ghi MySQL mà không cần phân trang

Đến đây bạn sẽ tạo một tập lệnh PHP kết nối với database MySQL mà bạn đã tạo ở bước trước và liệt kê các sản phẩm trong trình duyệt web. Trong bước này, mã PHP của bạn sẽ chạy mà không có bất kỳ hình thức phân trang nào để chứng minh cách các bản ghi không phân tách hiển thị trên một trang. Mặc dù bạn chỉ có mười bản ghi cho mục đích thử nghiệm trong hướng dẫn này, nhưng việc xem các bản ghi không có phân trang sẽ chứng minh tại sao việc phân đoạn dữ liệu cuối cùng sẽ tạo ra trải nghiệm user tốt hơn và giảm gánh nặng cho server .

Tạo file kịch bản PHP trong folder root của trang web bằng lệnh sau:

  • sudo nano /var/www/html/pagination_test.php

Sau đó thêm nội dung sau vào file . Hãy nhớ thay thế PASSWORD bằng giá trị chính xác của password mà bạn đã gán cho test_user ở bước trước:

/var/www/html/pagination_test.php
<?php  try {   $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);    $sql = "select * from products";    $stmt = $pdo->prepare($sql);    $stmt -> execute();    echo "<table border='1' align='center'>";    while (($row = $stmt -> fetch(PDO::FETCH_ASSOC)) !== false) {     echo "<tr>";     echo "<td>".$row['product_id']."</td>";     echo "<td>".$row['product_name']."</td>";     echo "</tr>";   }    echo "</table>";  } catch(PDOException $e) {   echo $e->getMessage(); }  ?>

Lưu file bằng cách nhấn CTRL+X , Y và ENTER .

Trong tập lệnh này, bạn đang kết nối với database MySQL bằng thư viện PDO (Đối tượng dữ liệu PHP) với thông tin đăng nhập database mà bạn đã tạo ở Bước 1.

PDO là một giao diện nhẹ để kết nối với database . Lớp truy cập dữ liệu có tính di động cao hơn và có thể hoạt động trên các database khác nhau mà chỉ cần viết lại mã nhỏ. PDO có tính bảo mật cao hơn vì nó hỗ trợ các câu lệnh đã chuẩn bị —một tính năng để làm cho các truy vấn chạy nhanh hơn một cách an toàn.

Sau đó, bạn hướng dẫn API PDO thực thi câu lệnh select * from products và liệt kê các sản phẩm trong bảng HTML mà không cần phân trang. Dòng $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false); đảm bảo các kiểu dữ liệu được trả về khi chúng xuất hiện trong database . Điều này nghĩa là PDO sẽ trả về product_id dưới dạng số nguyên và product_name dưới dạng chuỗi. $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); hướng dẫn PDO ném một ngoại lệ nếu gặp lỗi. Để gỡ lỗi dễ dàng hơn, bạn đang bắt lỗi bên trong PHP, try{}...catch{} .

Để thực thi file tập lệnh PHP /var/www/html/pagination_test.php mà bạn đã tạo, hãy truy cập URL sau thay thế your-server-IP bằng địa chỉ IP công cộng của server :

http://your-server-IP/pagination_test.php

Bạn sẽ thấy một trang có bảng các sản phẩm của bạn .

Bản ghi MySQL được hiển thị bằng tập lệnh PHP - Không phân trang

Tập lệnh PHP của bạn đang hoạt động như mong đợi; liệt kê tất cả các sản phẩm trên một trang. Nếu bạn có hàng nghìn sản phẩm, điều này sẽ dẫn đến một vòng lặp dài khi các sản phẩm được tìm nạp từ database và hiển thị trên trang PHP.

Để khắc phục hạn chế này, bạn sẽ sửa đổi tập lệnh PHP và bao gồm mệnh đề MySQL LIMIT và một số liên kết chuyển ở cuối bảng để thêm chức năng phân trang.

Bước 3 - Triển khai phân trang bằng PHP

Trong bước này, mục tiêu của bạn là chia dữ liệu thử nghiệm thành nhiều trang và có thể quản lý được. Điều này sẽ không chỉ nâng cao khả năng đọc mà còn sử dụng tài nguyên của server hiệu quả hơn. Bạn sẽ sửa đổi tập lệnh PHP mà bạn đã tạo ở bước trước để phù hợp với việc phân trang.

Để làm điều này, bạn sẽ triển khai điều khoản MySQL LIMIT . Trước khi thêm nó vào script, hãy xem ví dụ về cú pháp MySQL LIMIT :

  • Select [column1, column2, column n...] from [table name] LIMIT offset, records;

Mệnh đề LIMIT có hai đối số như được hiển thị ở cuối câu lệnh này. Giá trị offset là số bản ghi cần bỏ qua trước hàng đầu tiên. records đặt số lượng bản ghi tối đa để hiển thị trên mỗi trang.

Để kiểm tra phân trang, bạn sẽ hiển thị ba bản ghi trên mỗi trang. Để có tổng số trang, bạn phải chia tổng số bản ghi từ bảng của bạn với các hàng mà bạn muốn hiển thị trên mỗi trang. Sau đó, bạn làm tròn giá trị kết quả thành số nguyên gần nhất bằng cách sử dụng hàm PHP Ceil như trong ví dụ đoạn mã PHP sau:

$total_pages=ceil($total_records/$per_page);

Sau đây là version sửa đổi của tập lệnh PHP với mã phân trang đầy đủ. Để bao gồm mã phân trang và chuyển , hãy mở file /var/www/html/pagination_test.php :

  • sudo nano /var/www/html/pagination_test.php

Sau đó, thêm mã được đánh dấu sau vào file :

/var/www/html/pagination_test.php
<?php  try {   $pdo = new PDO("mysql:host=localhost;dbname=test_db", "test_user", "PASSWORD");   $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);   $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);    /* Begin Paging Info */    $page = 1;    if (isset($_GET['page'])) {     $page = filter_var($_GET['page'], FILTER_SANITIZE_NUMBER_INT);   }    $per_page = 3;    $sqlcount = "select count(*) as total_records from products";   $stmt = $pdo->prepare($sqlcount);   $stmt->execute();   $row = $stmt->fetch();   $total_records = $row['total_records'];    $total_pages = ceil($total_records / $per_page);    $offset = ($page-1) * $per_page;    /* End Paging Info */    $sql = "select * from products limit :offset, :per_page";   $stmt = $pdo->prepare($sql);   $stmt->execute(['offset'=>$offset, 'per_page'=>$per_page]);    echo "<table border='1' align='center'>";    while ( ($row = $stmt->fetch(PDO::FETCH_ASSOC) ) !== false) {     echo "<tr>";     echo "<td>".$row['product_id']."</td>";     echo "<td>".$row['product_name']."</td>";     echo "</tr>";   }    echo "</table>";    /* Begin Navigation */    echo "<table border='1' align='center'>";    echo "<tr>";    if ($page-1 >= 1) {     echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page - 1).">Previous</a></td>";   }    if ($page+1 <= $total_pages) {     echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page + 1).">Next</a></td>";   }    echo "</tr>";    echo "</table>";    /* End Navigation */  } catch(PDOException $e) {   echo $e->getMessage(); }  ?>

Trong file , bạn đã sử dụng các tham số bổ sung để thực thi phân trang:

  • $page : Biến này giữ trang hiện tại trong tập lệnh của bạn. Khi di chuyển giữa các trang, tập lệnh của bạn truy xuất tham số URL có tên page bằng cách sử dụng biến $_GET['page'] .
  • $per_page : Biến này chứa các bản ghi tối đa mà bạn muốn được hiển thị trên mỗi trang. Trong trường hợp của bạn, bạn muốn liệt kê ba sản phẩm trên mỗi trang.
  • $total_records : Trước khi liệt kê các sản phẩm, bạn đang thực thi một câu lệnh SQL để nhận tổng số bản ghi trong bảng mục tiêu của bạn và gán nó cho biến $total_records .
  • $offset : Biến này đại diện cho tổng số bản ghi cần bỏ qua trước hàng đầu tiên. Giá trị này được tính toán động bởi tập lệnh PHP của bạn bằng công thức $offset=($page-1)*$per_page . Bạn có thể điều chỉnh công thức này cho các dự án phân trang PHP của bạn . Lưu ý bạn có thể thay đổi biến $per_page để phù hợp với nhu cầu của bạn . Ví dụ: bạn có thể thay đổi nó thành giá trị 50 để hiển thị năm mươi mục trên mỗi trang nếu bạn đang chạy một trang web hoặc một số tiền khác cho thiết bị di động.

, hãy truy cập địa chỉ IP của bạn trong trình duyệt và thay thế your_server_ip bằng địa chỉ IP công cộng của server :

http://your_server_ip/pagination_test.php

Đến đây bạn sẽ thấy một số lựa chọn ở cuối trang. Trên trang đầu tiên, bạn sẽ không nhận được nút Trước . Trường hợp tương tự cũng xảy ra ở trang cuối cùng, nơi bạn sẽ không nhận được nút Trang tiếp theo . Ngoài ra, hãy lưu ý cách thông số URL page thay đổi khi bạn truy cập từng trang.

Bản ghi MySQL được hiển thị bằng tập lệnh PHP có phân trang - Trang 1

Bản ghi MySQL được hiển thị bằng một tập lệnh PHP với Phân trang - Trang 2

Trang cuối cùng của Bản ghi MySQL Được hiển thị bằng tập lệnh PHP với Phân trang - Trang 4

Các liên kết chuyển ở cuối trang có được bằng cách sử dụng đoạn mã PHP sau từ file :

/var/www/html/pagination_test.php
. . . if( $page-1>=1) {   echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page-1).">Previous</a></td>"; }  if( $page+1<=$total_pages) {   echo "<td><a href=".$_SERVER['PHP_SELF']."?page=".($page+1).">Next</a></td>"; } . . .

Ở đây, biến $page đại diện cho số trang hiện tại. Sau đó, để lấy trang trước đó, mã sẽ trừ đi 1 từ biến. Vì vậy, nếu bạn đang ở trang 2, công thức (2-1) sẽ cho bạn kết quả là 1 và đây sẽ là trang trước đó xuất hiện trong liên kết. Tuy nhiên, hãy nhớ rằng nó sẽ chỉ hiển thị trang trước nếu có kết quả lớn hơn hoặc bằng 1 .

Tương tự, để đến trang tiếp theo, bạn thêm một vào biến $page và bạn cũng phải đảm bảo kết quả $page mà ta thêm vào tham số URL page không lớn hơn tổng số trang mà bạn đã tính toán trong Mã PHP.

Đến đây, tập lệnh PHP của bạn đang hoạt động với phân trang và bạn có thể triển khai mệnh đề MySQL LIMIT để chuyển bản ghi tốt hơn.

Kết luận

Trong hướng dẫn này, bạn đã triển khai phân trang trong MySQL với PHP trên server Ubuntu 18.04. Bạn có thể sử dụng các bước này với tập bản ghi lớn hơn bằng cách sử dụng tập lệnh PHP để bao gồm phân trang. Bằng cách sử dụng phân trang trên trang web hoặc ứng dụng của bạn , bạn có thể tạo chuyển user tốt hơn và sử dụng tài nguyên tối ưu trên server .

Từ đây, bạn có thể xem xét tối ưu hóa thêm cho database của bạn và các việc database khác với các hướng dẫn sau:

Tags:

Các tin liên quan

Cách tối ưu hóa truy vấn MySQL với bộ đệm ProxySQL trên Ubuntu 16.04
2019-12-30
Cách cấu hình Cụm Galera với MySQL trên server Ubuntu 18.04
2019-12-16
Cách quản lý và sử dụng trình kích hoạt database MySQL trên Ubuntu 18.04
2019-12-10
Cách tạo một ứng dụng trích dẫn đầy cảm hứng bằng AdonisJs và MySQL
2019-11-22
Cách cài đặt MySQL mới nhất trên Debian 10
2019-07-25
Cách triển khai ứng dụng cốt lõi ASP.NET với server MySQL bằng Nginx trên Ubuntu 18.04
2019-07-23
Cách tối ưu hóa MySQL với Bộ đệm truy vấn trên Ubuntu 18.04
2019-06-12
Cách di chuyển database MySQL sang PostgreSQL bằng pgLoader
2019-05-28
Cách cấu hình SSL / TLS cho MySQL trên Ubuntu 18.04
2019-05-17
Cách thiết lập WordPress với MySQL trên Kubernetes bằng Helm
2019-05-07