php操作MySQL

1. 查询全部

<?php
    header('Access-Control-Allow-Methods:GET,POST,PATCH,PUT,OPTIONS');
    header('Access-Control-Allow-Origin:*');
    $mysqli = new mysqli("10.90.83.71", "bb", "bb", "friday");
    if ($mysqli -> connect_error) {
        exit("数据库链接失败,错误信息:{$mysqli->connect_error}");
    }

    $mysqli -> set_charset("utf8");
    $selectSQL = "select * from message";
    $result = $mysqli -> query($selectSQL);

    if ($result) {
        $arr = array();
        for ($i = 0; $i < $result -> num_rows; $i++) {
            $row = $result -> fetch_object();
            array_push($arr, $row);
        }
        echo json_encode($arr);
    } else {
        echo '0';
    }
    $mysqli -> close();
?>

2.查询全部并按ID倒叙排列

    <?php
    header('Access-Control-Allow-Methods:GET,POST,PATCH,PUT,OPTIONS');
    header('Access-Control-Allow-Origin:*');
    $mysqli = new mysqli("127.0.0.1", "root", "root", "test");
    if ($mysqli -> connect_error) {
        exit("数据库链接失败,错误信息:{$mysqli->connect_error}");
    }
    $mysqli -> set_charset("utf8");
    $selectSQL = "select * from test order by id desc";
    $result = $mysqli -> query($selectSQL);
    if ($result) {
        $arr = array();
        while($row = mysqli_fetch_array($result)){
            array_push($arr, $row);
      } 
      echo json_encode($arr);
    } else {
        echo "0";
    }
    $mysqli -> close();
?>

3.按ID查询

<?php
    header('Access-Control-Allow-Methods:GET,POST,PATCH,PUT,OPTIONS');
    header('Access-Control-Allow-Origin:*');

    $mysqli = new mysqli("127.0.0.1", "root", "root", "test");
    if ($mysqli -> connect_error) {
        exit("数据库链接失败,错误信息:{$mysqli->connect_error}");
    }
    $mysqli -> set_charset("utf8");
    $selectSQL = "select * from test where id=1";
    $result = $mysqli -> query($selectSQL);
    if ($result) {
        $arr = array();
        while($row = mysqli_fetch_array($result)){
            array_push($arr, $row);
      } 
      echo json_encode($arr[0]);
    } else {
        echo "0";
    }
    $mysqli -> close();
?>

4.插入一条数据

<?php
    header('Access-Control-Allow-Methods:GET,POST,PATCH,PUT,OPTIONS');
    header('Access-Control-Allow-Origin:*');

    $username = $_REQUEST['username'];
    $password = $_REQUEST['password'];
    $mysqli = new mysqli("127.0.0.1", "root", "root", "test");
    if ($mysqli -> connect_error) {
        exit("数据库链接失败,错误信息:{$mysqli->connect_error}");
    }
    $mysqli -> set_charset("utf8");
    $insertSQl = "insert into userinfor(username,password) values ({$username},'{$password}')";
    $result = $mysqli -> query($insertSQl);
    if ($result) {
        echo "1";
    } else {
        echo "0";
    }
    $mysqli -> close();
?>

5.删除一条数据

<?php
    header('Access-Control-Allow-Methods:GET,POST,PATCH,PUT,OPTIONS');
    header('Access-Control-Allow-Origin:*');
    $id = $_REQUEST['id'];
    $mysqli = new mysqli("127.0.0.1", "root", "root", "test");
    if ($mysqli -> connect_error) {
        exit("数据库链接失败,错误信息:{$mysqli->connect_error}");
    }

    $mysqli -> set_charset("utf8");
    $deleateSQL = "delete from test where id={$id}";

    $result = $mysqli -> query($deleateSQL);
    if ($result) {
        echo "1";
    } else {
        echo "0";
    }
    $mysqli -> close();
?>

6.更新一条数据

<?php
    header('Access-Control-Allow-Methods:GET,POST,PATCH,PUT,OPTIONS');
    header('Access-Control-Allow-Origin:*');

    $username = $_REQUEST['username'];
    $password = $_REQUEST['password'];
    $mysqli = new mysqli("127.0.0.1", "root", "root", "test");
    if ($mysqli -> connect_error) {
        exit("数据库链接失败,错误信息:{$mysqli->connect_error}");
    }
    $mysqli -> set_charset("utf8");
        $updateSQL = "update userinfor set password='{$password}' where username={$username}";
        $result = $mysqli -> query($updateSQL);
        if ($result) {
            echo "1";
        } else {
            echo '0';
        }
    $mysqli -> close();
?>