PHP学习（二）MySql增删改查图形界面增加htmlphp总结一、和数据库建立连接二、检测连接，设置编码格式三、写sq

增删改

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phplesson";
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
} else {
    // 设置编码格式
    mysqli_set_charset($conn, 'utf8');
    // 增
    $addSql = "INSERT INTO `news`(`news_title`, `news_img`, `news_content`,`addtime`) VALUES ('今天心情不错','图片地址','哈哈哈哈哈哈哈','2020-02-02 10:10:10')";
    // 删
    $delSql = "DELETE FROM `news` WHERE `news_id` = 5";
    // 改
    $changeSql = "UPDATE `news` SET `news_title`='惊讶',`news_img`='惊讶地址'  WHERE `news_id` = 7";

    $res = mysqli_query($conn, $changeSql);
    if ($res) {
        echo "操作成功";
    } else {
        echo "Error: " . $changeSql . "<br>" . mysqli_error($conn);
    }
    mysqli_close($conn);
}
?>

查

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phplesson";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
} else {
    // 设置编码格式
    mysqli_set_charset($conn, 'utf8');
    // 查
    $viewSql = "SELECT * from news";

    $res = mysqli_query($conn, $viewSql);
    if ($res) {
        if (mysqli_num_rows($res) > 0) {
            $arr = array();
            // 输出数据
            while ($row = mysqli_fetch_assoc($res)) {
                array_push($arr, array("news_title" => $row['news_title'], "news_img" => $row['news_img'], "news_content" => $row['news_img'], "addtime" => $row['addtime']));
            }
            $res = array("errCode" => 0, "data" => $arr);
            echo json_encode($res, JSON_UNESCAPED_UNICODE);
        } else {
            echo "0 结果";
        }
    } else {
        echo "Error: " . $viewSql . "<br>" . mysqli_error($conn);
    }
    mysqli_close($conn);
}
?>

图形界面增加

html

<!doctype html>
<html lang="en">
<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>Document</title>

</head>
<body>
标题：<input type="text" id="title"><br>
图片：<input type="text" id="img"><br>
内容：<input type="text" id="content"><br>
日期：<input type="text" id="date"><br>
<button id="btn">提交</button>

<script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.js"></script>
<script>
    $("#btn").click((ev) => {
        $.ajax({
            url: "mysql.php",
            data: {
                title: $('#title').val(),
                img: $('#img').val(),
                content: $('#content').val(),
                date: $('#date').val(),
            },
            type: "get",
            dataType: "json",
            success: data => {
                console.log(data);
            },
            error: err => {
                console.log(err);
            }
        })
    });
</script>
</body>
</html>

php

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phplesson";

// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);

// 检测连接
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
} else {
    // 设置编码格式
    mysqli_set_charset($conn, 'utf8');

    $title = $_REQUEST['title'];
    $img = $_REQUEST['img'];
    $content = $_REQUEST['content'];
    $date = $_REQUEST['date'];

    $addSql = "INSERT INTO `news`(`news_title`, `news_img`, `news_content`,`addtime`) VALUES ('$title','$img','$content','$date')";

    $res = mysqli_query($conn, $addSql);
    if ($res) {
        echo "添加成功"
    } else {
        echo "Error: " . $addSql . "<br>" . mysqli_error($conn);
    }
    mysqli_close($conn);
}
?>

总结

一、和数据库建立连接

$conn = mysqli_connect($servername, $username, $password, $dbname);

二、检测连接，设置编码格式

if ($conn) {
    mysqli_set_charset($conn, 'utf8');
}

三、写sql语句，执行sql语句

$viewSql = "SELECT * from news";
$res = mysqli_query($conn, $viewSql);

四、检查sql语句

if ($res) {
   ···
} else {
    echo "Error: " . $viewSql . "<br>" . mysqli_error($conn);
}

五、检查是否一条没有

if (mysqli_num_rows($res) > 0) {
    ···
} else {
   echo "0 结果";
}

六、输出

$arr = array();
// 输出数据
while ($row = mysqli_fetch_assoc($res)) {
    array_push($arr, array("news_title" => $row['news_title'], "news_img" => $row['news_img'], "news_content" => $row['news_img'], "addtime" => $row['addtime']));
}
$res = array("errCode" => 0, "data" => $arr);
echo json_encode($res, JSON_UNESCAPED_UNICODE);

注：$row 代表从 sql 语句中查出来的每一行数据，当某一行数据没了跳出循环。