基础篇——php与sql联动
基础篇——php与sql联动
php与sql的交互主要通过以下函数：
mysqli_connect：建立连接
mysqli_select_db：选择数据库
mysqli_query：发送请求
mysqli_fetch_array：将第一条记录转换为数组(无记录时返回NULL)
login.php：
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Login</title>
<link rel="stylesheet" href="login.css">
<?php
function filter($input) {
$input = trim($input);
$input = stripslashes($input);
$input = htmlspecialchars($input);
return $input;
}
$conn = mysqli_connect("localhost", "root", "root") or die("Failed to connect to MySQL!");
$seldb = mysqli_select_db($conn, "users") or die("Failed to connect to the database!");
mysqli_query($conn, "set names 'utf8'");
$username_err = "";
$password_err = "";
if (isset($_POST["submit"])) {
$username = filter($_POST["username"]);
$password = filter($_POST["password"]);
if (empty($username)) $username_err = "Username cannot be empty!";
if (empty($password)) $password_err = "Password cannot be empty!";
if ($username_err == "" && $password_err == "") {
$search_query = "select * from password where username = '$username';";
$search_result = mysqli_query($conn, $search_query);
$search_result_array = mysqli_fetch_array($search_result);
if (!empty($search_result) && $search_result_array['password'] == $password) echo "<script>alert('Login Success!');</script>";
else echo "<script>alert('Failed to login!');</script>";
}
}
?>
</head>
<body>
<div class="container">
<div class="header">
<h1>LOGIN PAGE</h1>
</div>
<div class="content">
<form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method="POST">
<table>
<tr>
<td><label for="username">Username</label></td>
<td><input type="text" id="username" name="username"></td>
<td><span style="color: red; font-size: 12px;"><?php echo $username_err;?></span></td>
</tr>
<tr>
<td><label for="password">Password</label></td>
<td><input type="password" id="password" name="password"></td>
<td><span style="color: red; font-size: 12px;"><?php echo $password_err;?></span></td>
</tr>
<tr>
<td colspan="3" class="center-align">
<input type="submit" id="submit" name="submit" value="Submit">
</td>
</tr>
<tr>
<td colspan="3" class="center-align">
No account? <a href="register.php" id="link">Register</a>
</td>
</tr>
</table>
</form>
</div>
</div>
</body>
</html>
register.php:
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Register</title>
<link rel="stylesheet" href="register.css">
<?php
function filter($input) {
$input = trim($input);
$input = stripslashes($input);
$input = htmlspecialchars($input);
return $input;
}
$conn = mysqli_connect("localhost", "root", "root") or die("Failed to connect to MySQL!");
$seldb = mysqli_select_db($conn, "users") or die("Failed to connect to the database!");
mysqli_query($conn, "set names 'utf8'");
$username_err = "";
$password0_err = "";
$password1_err = "";
$username = "";
$password0 = "";
$password1 = "";
if (isset($_POST["submit"])) {
$username = filter($_POST["username"]);
$password0 = filter($_POST["password0"]);
$password1 = filter($_POST["password1"]);
if (empty($username)) $username_err = "Username cannot be empty!";
else if (!preg_match("/^[a-zA-Z]+$/", $username)) $username_err = "Username can only contain letters!";
if (empty($password0)) $password0_err = "Password cannot be empty!";
else if (!preg_match("/^(?=.*[a-zA-Z])(?=.*\d)(?=.*[\W_]).*$/", $password0)) $password0_err = "Password must contain letters, numbers, and special characters!";
if (empty($password1)) $password1_err = "Confirm Password cannot be empty!";
else if (!preg_match("/^(?=.*[a-zA-Z])(?=.*\d)(?=.*[\W_]).*$/", $password1)) $password1_err = "Confirm Password must contain letters, numbers, and special characters!";
if ($username_err == "" && $password0_err == "" && $password1_err == "") {
if ($password0 === $password1) {
$search_query = "select * from password where username = '$username';";
$search_result = mysqli_query($conn, $search_query);
$search_result_array = mysqli_fetch_array($search_result);
if (!empty($search_result_array)) echo "<script>alert('Username is already taken!');</script>";
else {
$insert_query = "insert into password(username,password) values('$username','$password0');";
mysqli_query($conn, $insert_query);
echo "<script>alert('Register Success!');window.location.href='login.php';</script>";
}
}
else echo "<script>alert('Passwords do not match!');</script>";
}
}
?>
</head>
<body>
<div class="container">
<div class="header">
<h1>REGISTER PAGE</h1>
</div>
<div class="content">
<form action="<?php echo htmlspecialchars($_SERVER['PHP_SELF']);?>" method="POST">
<table>
<tr>
<td><label for="username">Username</label></td>
<td><input type="text" id="username" name="username" value="<?php echo $username;?>"></td>
<td><span style="color: red; font-size: 12px;"><?php echo $username_err;?></span></td>
</tr>
<tr>
<td><label for="password0">Password</label></td>
<td><input type="password" id="password0" name="password0" value="<?php echo $password0;?>"></td>
<td><span style="color: red; font-size: 12px;"><?php echo $password0_err;?></span></td>
</tr>
<tr>
<td><label for="password1">Confirm Password</label></td>
<td><input type="password" id="password1" name="password1" value="<?php echo $password1;?>"></td>
<td><span style="color: red; font-size: 12px;"><?php echo $password1_err;?></span></td>
</tr>
<tr>
<td colspan="3" class="center-align">
<input type="submit" id="submit" name="submit" value="Register">
</td>
</tr>
<tr>
<td colspan="3" class="center-align">
Have an account? <a href="login.php" id="link">Login</a>
</td>
</tr>
</table>
</form>
</div>
</div>
</body>
</html>
Navicat对应页面:
