创建数据库,创建数据表,插入数据。
<?php
// 创建连接
$mysql = new mysqli($servername, $username, $password);
// 检测连接
if ($mysql->connect_error) {
die("连接失败: " . $mysql->connect_error);
}
echo "连接数据库成功"."<br>";
//判断是否存在database,如果不存在才继续创建
$result = $mysql->query("show databases like 'myDb'");
if ($result->fetch_all()[0][0] == "myDb"){
die( "数据库已存在"."<br>");
}
// 创建数据库
$sql = "CREATE DATABASE myDb";
if ($mysql->query($sql) === TRUE) {
echo "数据库创建成功"."<br>";
} else {
die("数据库创建失败: " . $mysql->error);
}
$mysql->select_db("myDb");
$sql = "CREATE TABLE myTable(
inputId INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
userName VARCHAR(50) NOT NULL,
passWord VARCHAR(255) NOT NULL,
nickname VARCHAR(50),
logTimes INT(11) NOT NULL DEFAULT 0,
lastLoginTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($mysql->query($sql) === TRUE) {
echo "数据表创建成功";
} else {
die( "创建数据表失败: " . $mysql->error);
}
$sql = "INSERT INTO myTable(userName, passWord, nickname)
VALUES ('admin', '".sha1("admin")."', 'admin')";
if ($mysql->query($sql) === TRUE) {
echo "数据写入成功"."<br>";
} else {
die( "数据写入错误: " . $mysql->error);
}
?>
使用phpmyadmin验证结果正确。
读取数据,查找数据
// 创建连接
$mysql = new mysqli($servername, $username, $password,$dbname);
// 检测连接
if ($mysql->connect_error) {
die("连接失败: " . $mysql->connect_error);
}
echo "连接数据库成功"."<br>";
// 读取数据库
$result = $mysql->query("SELECT inputId, userName, nickName FROM userauthentication");
if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo "id: " . $row["inputId"]. " - Name: " . $row["userName"]. " " . $row["nickName"]. "<br>";
}
} else {
echo "0 结果";
}
//使用where选择读取数据库
echo "使用where查找“admin”". "<br>";
$result = $mysql->query("SELECT * FROM userauthentication WHERE userName='admin'");
if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo "id: " . $row["inputId"]. " - Name: " . $row["userName"]. " " . $row["nickName"]. "<br>";
}
} else {
echo "0 结果";
}
echo "使用where查找“guest”". "<br>";
$result = $mysql->query("SELECT * FROM userauthentication WHERE userName='guets'");
if ($result->num_rows > 0) {
// 输出数据
while($row = $result->fetch_assoc()) {
echo "id: " . $row["inputId"]. " - Name: " . $row["userName"]. " " . $row["nickName"]. "<br>";
}
} else {
echo "0 结果";
}
$mysql->close();
运行读取结果,因为没有插入“guest”数据,所以查找“guest”提示0结果。