使用 PHP 從資料庫中獲取資料並在 HTML 表中顯示資料
本教程將逐步教你如何使用 PHP 獲取 MySQL 表並在 HTML 中顯示記錄。
在 MySQL 中建立資料庫和表
首先,我們將建立一個 "demo"
資料庫和一個 "products"
表。你可以使用 PHPMyAdmin MySQL
或 SQLyog
執行以下 SQL 查詢:
MySQL 查詢:
/*Your SQL queries*/
CREATE DATABASE demo; /*phpmyadmin MySQL Database Query*/
/*or*/
CREATE DATABASE demo; /*SQLyog Database Query*/
USE demo;
/*Table structure*/
DROP TABLE IF EXISTS `products`;
CREATE TABLE `products` (
`id` int(11) NOT NULL,
`Manufacturer` char(60) DEFAULT NULL,
`Module` char(60) DEFAULT NULL,
`Series` char(60) DEFAULT NULL,
`MPN` char(60) DEFAULT NULL,
`Function` char(60) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*Data for the table*/
insert into `products`(`id`,`Manufacturer`,`Module`,`Series`,`MPN`,`Function`) values
(1,'Microsoft','Operation System','A','1263187','OS'),
(2,'Amazon','Web Services','B','3473747','Web'),
(3,'Rockwell Automation','Electronic Modules','C','9854747','Machine Control'),
(4,'Facebook','Social Connectivity','D','1271517','Social'),
(5,'Google','Search Engine','E','6372673','Search');
要匯入這些記錄,你可以複製此查詢並直接在 PHPMyAdmin MySQL
或 SQLyog
中執行。
表格 "products"
包含 5 個表格行和列,如下所示:
- 產品編號
- 產品製造商
- 模組型別
- 產品系列
- 產品功能
建立資料庫後,我們將用 PHP 將我們的 database.php
檔案與 MySQL 伺服器連線起來。
在 PHP 中連線到 MySQL 伺服器
讓我們瞭解 PHP 中使用的幾個關鍵 MySQL 函式。
define()
- 定義本地主機資訊。mysqli_connect()
- 通過從define()
函式傳遞引數連線到 MySQL。die(mysqli_connect_error())
- 在發生資料庫故障和db
時顯示錯誤。
程式碼片段(database.php
):
<?php
define("server", "localhost");
define("user", "root");
define("password", "");
define("database", "demo");
//mysql_connect(); parameters
$connect = mysqli_connect(server, user, password, database);
//run a simple condition to check your connection
if (!$connect)
{
die("You DB connection has been failed!: " . mysqli_connect_error());
}
$connection = "You have successfully connected to the mysql database";
//echo $connection;
?>
輸出:
You have successfully connected to the MySQL database.
現在我們已連線到 MySQL 伺服器,讓我們檢索 PHP 指令碼中的資料。
使用 PHP 在 HTML 表格中顯示資料
我們將使用 require_once()
函式包含 database.php
。然後一個 while
迴圈將從 mysql_fetch_array()
屬性動態建立資料。
HTML (Index.php
):
<!DOCTYPE html>
<body>
<head>
<title> Fetch data from the database in show it into a HTML table dynamically</title>
<link rel="stylesheet" href="style.css">
</head>
<form action="index.php" method="post" align="center">
<input type="submit" name="fetch" value="FETCH DATA" />
</form>
樣式 style.css
和 HTML
僅用於我們在 index.php
檔案中合併的前端問題。
PHP 指令碼 (Index.php
):
<?php
//fetch connection details from database.php file using require_once(); function
require_once ('database.php');
//check if it work!
echo $connection; //from database.php file
if (isset($_POST['fetch']))
{
//mysql_query() performs a single query to the currently active database on the server that is associated with the specified link identifier
$response = mysqli_query($connect, 'SELECT * FROM products');
echo "<table border='2' align='center'>
<H2 align='center'> Products Table </h2>
<tr>
<th>Product ID</th>
<th>Product Manufacturer</th>
<th>Product Type</th>
<th>Product Series</th>
<th>MPN</th>
<th>Product Function</th>
</tr>";
while ($fetch = mysqli_fetch_array($response))
{
echo "<tr>";
echo "<td>" . $fetch['id'] . "</td>";
echo "<td>" . $fetch['Manufacturer'] . "</td>";
echo "<td>" . $fetch['Module'] . "</td>";
echo "<td>" . $fetch['Series'] . "</td>";
echo "<td>" . $fetch['MPN'] . "</td>";
echo "<td>" . $fetch['Function'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($connect);
}
?>
isset($_POST['fetch'])
函式在表單提交時觸發。然後我們使用 mysql_query('Your query')
從 products
表中選擇所有記錄。
我們將它們儲存在 $response
變數中。之後,我們使用 while
迴圈生成一個表,直到 mysql_fetch_array()
完成以陣列索引的形式獲取記錄。
$fetch['array_index']
以 mysql_query
成功影響的先前儲存的陣列索引為目標。
輸出:
Sarwan Soomro is a freelance software engineer and an expert technical writer who loves writing and coding. He has 5 years of web development and 3 years of professional writing experience, and an MSs in computer science. In addition, he has numerous professional qualifications in the cloud, database, desktop, and online technologies. And has developed multi-technology programming guides for beginners and published many tech articles.
LinkedIn