php查询数据库，并通过表格展示

Java全栈开发工程师

848人浏览 · 2023-12-16 20:54:36

Java全栈开发工程师 · 2023-12-16 20:54:36 发布

第一步：创建数据库

创建一个数据库php-crud

第二步：创建数据库表

在数据库php-crud下创建一个歌曲表song

/*
 Navicat Premium Data Transfer

 Source Server         : MariaDB
 Source Server Type    : MariaDB
 Source Server Version : 100605 (10.6.5-MariaDB)
 Source Host           : 127.0.0.1:3306
 Source Schema         : php-crud

 Target Server Type    : MariaDB
 Target Server Version : 100605 (10.6.5-MariaDB)
 File Encoding         : 65001

 Date: 16/12/2023 20:49:56
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for song
-- ----------------------------
DROP TABLE IF EXISTS `song`;
CREATE TABLE `song`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '歌曲编号',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '歌曲名',
  `singer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '歌手',
  `note` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '描述信息',
  `last_update_time` datetime NULL DEFAULT NULL COMMENT '最后一次修改时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of song
-- ----------------------------
INSERT INTO `song` VALUES ('20230822193447', '阴天', '莫文蔚', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20230822215824', '赤伶', '执素兮', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20230902030903', '直到世界尽头', '张杰', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20230906185524', '曾经守候', '张碧晨', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20230906185614', '给未来的自己', '梁静茹', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20230925223008', '失衡', '梁琪清', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231006181259', '哭砂', '张惠妹', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231006181317', '小小', '容祖儿', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231013232337', '花开忘忧', '周深', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231017163512', '贪杯琥珀', '阿YueYue', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231017163541', '晚风作酒', 'L (桃籽) 、周林枫、三楠', '', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231103090132', '卿清叙', '圈9', '《猫妖的诱惑》动画OP', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231110004454', '再见的，不见的', '郁可唯', '《我的女友是机器人》电影主题曲', '2023-12-02 01:30:55');
INSERT INTO `song` VALUES ('20231203192229', '明天', '朱心怡', '《斗破苍穹》决战云岚片尾曲', '2023-12-03 19:23:22');

SET FOREIGN_KEY_CHECKS = 1;

第三步：编写php代码

WampServer下的www目录下创建php文件php_select.php

<!DOCTYPE html>
<html>
	<head>
		<meta charset='utf-8' />
		<title>查询MYSQL数据</title>
	</head>
	
	<body>
		<table border="1">
			<tr>
				<th>name</th>
				<th>single</th>
				<th>note</th>
			</tr>
			
			<?php
				$host = 'localhost';
				$username = 'root';
				$password = 'root';
				$database = 'php-crud';
				$port = 3306;
				
				$conn = new mysqli($host, $username, $password, $database, $port);

				if ($conn->connect_error) {
					die('连接失败：' . $conn->connect_error);
				}
				
				$sql = "SELECT name, singer, note FROM song";
				$result = $conn->query($sql);
				
				if ($result->num_rows > 0) {
					// 输出数据
					while($row = $result->fetch_assoc()) {
						echo <<<EOF
							<tr>
								<td>$row[name]</td>
								<td>$row[singer]</td>
								<td>$row[note]</td>
							</tr>
						EOF;
					}
				}
				

				$conn->close();
			?>
		</table>
	</body>
</html>

页面效果如下，注意：EOF里的字符串不能加引号，否则会报错~