Data and Split on Select Page with PHP/MySQL_PHP Tutorial-PHP Tutorial-php.cn

Data and Split on Select Page with PHP/MySQL_PHP Tutorial

WBOY

Release： 2016-07-13 17:53:25

Original

821 people have browsed it

This tutorial will show you how to select data from a MySQL database, split it across multiple pages, and display it using page numbers. We have 90 records in a MySQL table named "Students" with the following fields: Name - data type varchar(250) ****** - varchar(250) Instead do a single SELECT query and display all We can have 5 pages with 20 records each containing max of 90 records on one page. To do this, we will need to use the LIMIT clause of the SELECT command, so we can limit the query to only display 20 records. The LIMIT clause also allows you to specify which record to start with. For example, this query

$sql = "SELECT * FROM students ORDER BY name ASC LIMIT 0, 20";
Returns 20 records, sorted by name starting with the first record. Inquire now
$sql = "SELECT * FROM students ORDER BY name ASC LIMIT 50, 20";
Sort the names again, but this time, it will have 20 records starting from the 50th record.
So basically in this clause (limit start, count) "start" specifies the starting record and "count" specifies how many records to display. The next thing to do is make a PHP file called pagination.php that will display the 20th record from our table. The code below selects and then prints the data in the table.

if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * 20;
$sql = "SELECT * FROM students ORDER BY name ASC LIMIT $start_from, 20";
$rs_result = mysql_query ($sql, $connection);
?>

while ($row = mysql_fetch_assoc($rs_result)) {
?>
out out out
};
?>

Name	Phone

Now, when you open pagination.php in your web browser, you will see the code above line 2 of the table showing the 20th record from your "Students" table
if (isset($_GET["page"])) { $page = $_GET["page"]; } else { $page=1; };
$start_from = ($page-1) * 20;
is used to create a $start_from variable, depending on the page we want to view. Later, you will see that we will pass the "pages" value, using the URL to a different page (e.g. pagination.php? = 2 pages). Next we need to find out the total number of records in our table and page that we need. To do this, we run another query using the COUNT() function. Webmaster Encyclopedia

$sql = "SELECT COUNT(Name) FROM students";
$rs_result = mysql_query($sql,$connection);

$row = mysql_fetch_row($rs_result);

$total_records = $row[0];
$total_records is now equal to the number of records we have in our database, 90 in our case. We have 20 records per page so the total number of pages that will be needed is 5 (4 pages with 20 records and the last page will have 10 records). Counting the number of web pages that require PHP can use the CEIL() function.

$total_pages = ceil($total_records / 20);
We divide the total number of records by the records per page, and then the CEIL() function rounds the result up. Now we have 2 new variables - $total_records equals $90 total_pages equals 5

To print the page numbers and affiliate URLs, for each number we will use a () loop.

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

echo "".$i." ";

};
?>
The above code will print numbers from 1 to 5, each number will be created differently. You can see that each link is passed using a different page value in the SELECT query above. Finally, you should have a file like this (remember to add the MySQL connection string):

while ($row = mysql_fetch_assoc($rs_result)) {
?>




};
?>

Name	Phone

$sql = "SELECT COUNT(Name) FROM students";
$rs_result = mysql_query($sql,$connection);
$row = mysql_fetch_row($rs_result);
$total_records = $row[0];
$total_pages = ceil($total_records / 20);

for ($i=1; $i<=$total_pages; $i++) {
echo "".$i." ";
};
?>
这pagination.php文件将打印最多20每页记录和底部5页码指向一个页面显示不同的20条记录的表。不要忘了一小笔费用，我可以添加分页你所有的PHP文件。让我知道如果你需要帮助，我会给你一个报价。

作者：newcnzz