PHP数据操作 - MYSQL

MYSQL 是一个开源的、跨平台关系型数据库管理系统，PHP 是一个开源的、跨平台的脚本语言，PHP 扩展包对MYSQL 支持比较完善，也有比较多开源包供参考，很多开源项目与 MYSQL 搭配也比较多，因此 PHP+MYSQL 是一套应用极为广泛的方案，下面我们通过代码来了解如何使用 PHP 操作 MYSQL 数据库。

连接数据库服务器

mysqli_connect() 函数打开一个到 MySQL 服务器的新的连接，它的语法如下：

1	mysqli_connect(host,username,password,dbname,port,socket);

参数	描述
host	可选。规定主机名或 IP 地址。
username	可选。规定 MySQL 用户名。
password	可选。规定 MySQL 密码。
dbname	可选。规定默认使用的数据库。
port	可选。规定尝试连接到 MySQL 服务器的端口号。
socket	可选。规定 socket 或要使用的已命名 pipe。

它的返回值是一个代表 MySQL 服务器连接的对象。

示例如下：

<?php
$sql = mysqli_connect('localhost', 'root', '');
if ($sql) {
    echo '连接成功';
}
else {
    die("连接错误".mysqli_connect_error());
}

连接SQL数据库

mysqli_select_db() 函数用于指定要获取的服务器上的某一个数据库。语法如下：

1	mysqli_select_db(connection,dbname);

参数	描述
connection	必需。规定要使用的 MySQL 连接。
dbname	必需，规定要使用的默认数据库。

它的返回值是bool类型，如果连接数据库成功则返回 TRUE，如果失败则返回 FALSE。

示例如下：

<?php
$sql = mysqli_connect('localhost', 'root', 'root');
if ($sql) {
    echo '连接服务器成功';
    if (mysqli_select_db($sql, 'test');) {
        echo '连接数据库test成功';
    }
}
else {
    die("连接错误".mysqli_connect_error());
}

读取SQL数据库

mysqli_query() 函数执行某个针对数据库的查询。语法如下：

1	mysqli_query(connection,query,resultmode);

参数	描述
connection	必需。规定要使用的 MySQL 连接。
query	必需，规定查询字符串。
resultmode	可选。MYSQLI_USE_RESULT（数据较多）MYSQLI_STORE_RESULT（默认）

针对成功的 SELECT、SHOW、DESCRIBE 或 EXPLAIN 查询，将返回一个 mysqli_result 对象。

针对其他成功的查询，将返回 TRUE。如果失败，则返回 FALSE。

实例如下：

$sql = mysqli_connect('localhost', 'root', 'root');
if ($sql) {
    echo '连接服务器成功';
    if (mysqli_select_db($sql, 'test');) {
        echo '连接数据库test成功';
    }
    $result = mysqli_query($sql ,'SELECT * FROM users');
    print_r($result);
}
else {
    die("连接错误".mysqli_connect_error());
}

获取SQL条目数

mysqli_num_rows() 函数返回结果集中行的数量，它的语法如下：

1	mysqli_num_rows(result);

参数	描述
result	必需。由 mysqli_query()、mysqli_store_result() 或 mysqli_use_result() 返回的结果集标识符。

示例如下：

$sql = mysqli_connect('localhost', 'root', 'root');
if ($sql) {
    echo '连接服务器成功';
    
    $result = mysqli_query($sql ,'SELECT * FROM users');
    $rows = mysqli_num_rows($result);
    echo '现在数据库条数是'.$rows;
}

MySql 语句中也集成了获取条目数的函数 COUNT() ，它的语法如下：

SELECT COUNT(column_name) FROM table_name
COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）
SELECT COUNT(*) FROM table_name
COUNT(*) 函数返回表中的记录数
SELECT COUNT(DISTINCT column_name) FROM table_name
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

示例如下：

$sql = mysqli_connect('localhost', 'root', 'root');
if ($sql) {
    mysqli_select_db($sql, 'test');
    $result = mysqli_query($sql, 'SELECT COUNT(*) FROM users');
    $rows = mysqli_fetch_array($result);	
    echo '现在数据库条数是'.$rows[0].'<br>';
}

COUNT() 因为是 SQL 的原生语句，效率要比 PHP 的 mysqli_num_rows() 高很多，也可以配合 WHERE & AND & OR 进行查询。

SQL转数组

mysqli_fetch_array() 函数从结果集中取得一行作为关联数组，或数字数组，或二者兼有。它的语法如下：

1	mysqli_fetch_array(result,resulttype);

参数	描述
result	必需。由 mysqli_query() mysqli_store_result() mysqli_use_result() 返回的结果集标识符。
resulttype	可选。规定产生哪种类型的数组。MYSQLI_ASSOC MYSQLI_NUM MYSQLI_BOTH。

示例如下：


$sql = mysqli_connect('localhost', 'root', 'root');
if ($sql) {
    $result = mysqli_query($sql ,'SELECT * FROM users');
    $result_arr = mysqli_fetch_array($result);
    print_r($result_arr);
}
else {
    die("连接错误".mysqli_connect_error());
}

mysqli_fetch_assoc() 函数从结果集中取得一行作为关联数组。它的语法如下：

1	mysqli_fetch_assoc(result);

参数	描述
result	必需。规定由 mysqli_query() mysqli_store_result() mysqli_use_result() 返回的结果集标识符。

它的输出结果等同于 mysqli_fetch_array($result, MYSQLI_ASSOC);

需要注意的是，两者返回的都是单条数据，如需获得数据库全部数据，需要循环遍历，示例如下：

$sql = mysqli_connect('localhost', 'root', 'root');
if ($sql) {
    $result = mysqli_query($sql ,'SELECT * FROM users');
    $rows = mysqli_num_rows($result);
    $data_all = array();
    for ($i=0; $i<$rows; $i++) {
        $data = mysqli_fetch_assoc($result);
        array_push($data_all, $data);
    }
    
    print_r($data_all);
}
else {
    die("连接错误".mysqli_connect_error());
}

查询SQL数据

以下示例是一个用 PHP 实现查询数据库的程序，首先设定数据库服务器链接，账户，密码的默认值：

config.php
<?php
define('MYSQL_HOST', 'localhost');
define('MYSQL_USER', 'root');
define('MYSQL_PASSWORD', 'root');

创建一个方法，获得并返回数据库的对象：

function.php
  
<?php
require_once 'config.php';
function connectDataBase() {
    return mysqli_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASSWORD, 'test');
}

使用 HTML 创建表格，获得所有 name 列为 ZhangSan 的行，并按 id 逆序排序：

select_data.php
  
<?php
require_once 'function.php';
?>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>所有用户</title>
</head>
<body>
<table width="100%"><tr><th>id</th><th>name</th><th>age</th></tr>
<?php
$coon = connectDataBase();
$data_count = mysqli_fetch_array(mysqli_query($coon, 'SELECT COUNT(*) FROM users'));
$result = mysqli_query($coon, "SELECT * FROM users WHERE name='ZhangSan' ORDER BY id DESC");
for ($i=0; $i<$data_count[0]; $i++) {
    $data = mysqli_fetch_assoc($result);
    $id = $data['id'];
    $name = $data['name'];
    $age = $data['age'];
    echo "<tr><td>$id</td><td>$name</td><td>$age</td></tr>";
}
?>
</table>
</body>
</html>

添加SQL数据

建立一个PHP页面，展示数据库里的所有数据，并新建一个链接，指向数据库添加页面：

data_all.php
  
<?php
require_once 'function.php';
?>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>所有用户</title>
</head>
<body>
<a href="adduser.html">添加用户</a>
<table width="100%"><tr><th>id</th><th>name</th><th>age</th></tr>
<?php
$coon = connectDataBase();
$data_count = mysqli_fetch_array(mysqli_query($coon, 'SELECT COUNT(*) FROM users'));
$result = mysqli_query($coon, "SELECT * FROM users ORDER BY id DESC");
for ($i=0; $i<$data_count[0]; $i++) {
    $data = mysqli_fetch_assoc($result);
    $id = $data['id'];
    $name = $data['name'];
    $age = $data['age'];
    echo "<tr><td>$id</td><td>$name</td><td>$age</td></tr>";
}
?>
</table>
</body>
</html>

创建一个 HTML 页面，并添加输入框和提交按钮，将输入框的文本用 POST 方式传给 user_add.php :

user_add.html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form method="post" action="user_add.php">
    <div>
        姓名:<input type="text" name="name">
    </div>
    <div>
        年龄:<input type="text" name="age">
    </div>
    <input type="submit" value="提交">
</form>
</body>
</html>

使用 SQL 语句向数据库添加数据，如果没有添加错误，转跳到 data_all.php ：

user_add.php
<?php
require_once 'function.php';
if (!isset($_POST['name'])) {
    echo '没有收到name的值';
}
if (!isset($_POST['age'])) {
    echo '没有收到age的值';
}
if (empty($_POST['name'])) {
    echo 'name的值是空的';
}
if (empty($_POST['age'])) {
    echo 'age的值是空的';
}
$name = $_POST['name'];
$age = $_POST['age'];
$coon = connectDataBase();
$age = intval($age);
if ($coon) {
    mysqli_query($coon, "INSERT INTO users(name,age) VALUES ('$name', $age)");
}
else {
    die('数据库未成功连接');
}
if (mysqli_error()) {
    echo mysqli_error();
}
else {
    header('Location:allusers.php');
}

SQL防注入

在上面的示例中，如果 $name & $age 没有使用 '' 和 intval 进行格式转换，那么当用户输入的是SQL语句时，它有可能会被执行，所以对用户传入的值一定要进行相应的格式处理，避免被恶意注入 SQL 语句。

修改SQL数据

在 data_all.php 中添加一列，命名为修改，单元格内添加链接，指向 edituser.php ，并使用 GET 方式将此单元格的 id 进行传输：

data_all.php
  
<?php
require_once 'function.php';
?>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>所有用户</title>
</head>
<body>
<a href="adduser.html">添加用户</a>
<table width="100%"><tr><th>id</th><th>name</th><th>age</th><th>修改</th></tr>
<?php
$coon = connectDataBase();
$data_count = mysqli_fetch_array(mysqli_query($coon, 'SELECT COUNT(*) FROM users'));
$result = mysqli_query($coon, "SELECT * FROM users ORDER BY id DESC");
for ($i=0; $i<$data_count[0]; $i++) {
    $data = mysqli_fetch_assoc($result);
    $id = $data['id'];
    $name = $data['name'];
    $age = $data['age'];
    echo "<tr><td>$id</td><td>$name</td><td>$age</td><td><a href='edituser.php?id=$id'>修改</a></td></tr>";
}
?>
</table>
</body>
</html>

获取此行的所有数据，并创建输入框和提交按钮，将获取的行数据填充为输入框默认数据，创建的表单将以 POST 方式将数据传输到 edituser_server.php ：

edituser.php
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>编辑用户</title>
</head>
<body>
<?php
require_once 'function.php';
if (!isset($_GET['id'])) {
    die('没有获取到id');
}
if (empty($_GET['id'])) {
    die('id的值是空的');
}
$id = $_GET['id'];
$coon = connectDataBase();
$result = mysqli_query($coon, "SELECT * FROM users WHERE id=$id");
if (mysqli_error()) {
    die(mysqli_error());
}
$data = mysqli_fetch_assoc($result);
?>
<form action="edituser_server.php" method="post">
    <div>id:
        <input type="text" name="id" value="<?php echo $data['id']?>">
    </div>
    <div>姓名:
        <input type="text" name="name" value="<?php echo $data['name']?>">
    </div>
    <div>年龄:
        <input type="text" name="age" value="<?php echo $data['age']?>">
    </div>
        <input type="submit" value="提交修改">
</form>
</body>
</html>

获取 edituser.php 中传输过来的数据，按 id 定位到目标行并进行修改，如没有报错则转跳到 Location:data_all.php ：

require_once 'function.php';
$conn = connectDataBase();
if (empty($_POST['id'])) {
    die('id没有获取');
}
if (empty($_POST['name'])) {
    die('name没有获取');
}
if (empty($_POST['age'])) {
    die('age没有获取');
}
$name = $_POST['name'];
$age = $_POST['age'];
$id = $_POST['id'];
mysqli_query($conn, "UPDATE users SET name='$name', age=$age WHERE id=$id");
if (mysqli_error()) {
    echo mysqli_error();
}
else {
    header('Location:data_all.php');
}

删除SQL数据

在 data_all.php 中添加一列，命名为删除，单元格内添加链接，指向 deleteuser.php ，并使用 GET 方式将此单元格的 id 进行传输：

data_all.php
<?php
require_once 'function.php';
?>
<html>
<head>
    <meta charset="UTF-8">
    <meta name="viewport"
          content="width=device-width, user-scalable=no, initial-scale=1.0, maximum-scale=1.0, minimum-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">
    <title>所有用户</title>
</head>
<body>
<a href="adduser.html">添加用户</a>
<table width="100%"><tr><th>id</th><th>name</th><th>age</th><th>修改</th><th>删除</th></tr>
<?php
$coon = connectDataBase();
$data_count = mysqli_fetch_array(mysqli_query($coon, 'SELECT COUNT(*) FROM users'));
$result = mysqli_query($coon, "SELECT * FROM users ORDER BY id DESC");
for ($i=0; $i<$data_count[0]; $i++) {
    $data = mysqli_fetch_assoc($result);
    $id = $data['id'];
    $name = $data['name'];
    $age = $data['age'];
    echo "<tr><td>$id</td><td>$name</td><td>$age</td><td><a href='edituser.php?id=$id'>修改</a></td><td><a href='deleteuser.php?id=$id'>删除</a></td></tr>";
}
?>
</table>
</body>
</html>

获取 data_all.php 中传输过来的 id，按 id 定位到目标行并删除，如没有报错则转跳到 Location:data_all.php ：

deleteuser.php
<?php
require_once 'function.php';
$conn = connectDataBase();
if (empty($_GET['id'])) {
    die('id的值是空的');
}
$id = intval($_GET['id']);
mysqli_query($conn, "DELETE FROM users WHERE id=$id");
if (mysqli_error()) {
    die(mysqli_error());
}
else {
    header('Location:allusers.php');
}