PHP MySQL （二）物件導向 增刪查改 (預編譯方式)

增

<?php
    header('content-type:text/html;charset=utf-8');
    $host = "127.0.0.1";
    $user = "root";
    $password = "root";
    $db = "test";

    $mysqli = new mysqli($host, $user, $password, $db); //例項化mysqli物件，連線mysql資料庫
    if ($mysqli->connect_errno) {
        die($mysqli->connect_error);
    }
    $mysqli->set_charset('utf8'); //設定字符集

    $sql = "insert user(username,password,create_time,update_time) values(?,?,?,?)";
    $mysqli_stmt = $mysqli->prepare($sql);//準備預處理語句

    $username = 'king';
    $password = md5('king');
    $create_time = date("Y-m-d H:i:s");
    $update_time = date("Y-m-d H:i:s");

    //s代表string型別
    $mysqli_stmt->bind_param('ssss', $username, $password, $create_time, $update_time);

    //執行預處理語句
    if ($mysqli_stmt->execute()) {
        echo $mysqli_stmt->insert_id;//程式成功，返回插入資料表的行id
        echo PHP_EOL;
    } else {
        echo $mysqli_stmt->error;//執行失敗，錯誤資訊
    }

    //再新增一條資料
    $username = 'seven';
    $password = md5('seven');
    $create_time = date("Y-m-d H:i:s");
    $update_time = date("Y-m-d H:i:s");

    //s代表string型別
    $mysqli_stmt->bind_param('ssss', $username, $password, $create_time, $update_time);

    //執行預處理語句
    if ($mysqli_stmt->execute()) {
        echo $mysqli_stmt->insert_id;//程式成功，返回插入資料表的行id
        echo PHP_EOL;
    } else {
        echo $mysqli_stmt->error;//執行失敗，錯誤資訊
    }

刪

<?php
    header('content-type:text/html;charset=utf-8');
    $host = "127.0.0.1";
    $user = "root";
    $password = "root";
    $db = "test";

    $mysqli = new mysqli($host, $user, $password, $db);
    if($mysqli->connect_errno){
        die($mysqli->connect_error);
    }
    $mysqli->set_charset('utf8');

    $_GET['id'] = 2;
    $id = $_GET['id'];
    $sql = "DELETE FROM `user` WHERE `id` = ?";
    $mysqli_stmt = $mysqli->prepare($sql);
    $mysqli_stmt->bind_param('i', $id);
    if($mysqli_stmt->execute()) {
        if($mysqli_stmt->num_rows > 0){
            echo '刪除成功';
        }else{
            echo '刪除失敗';
        }
    }

改

<?php
    //mysql事務
    header('content-type:text/html;charset=utf-8');

    $host = '127.0.0.1';
    $user = 'root';
    $password = 'root';
    $db = 'test';

    $mysqli = new myslqi($host, $user, $password, $db);

    if($mysqli->connect_errno){
        die($mysqli->connect_error);
    }
    $mysqli->set_charset('utf8');
    $sql = "UPDATE `user` set `username` = ? WHERE `id` = ?";
    $mysqli_stmt = $mysqli->prepare($sql);//準備預處理語句
    //s代表string型別,i代表int
    $mysqli_stmt->bind_param('si', $username, $id);

    //執行預處理語句
    if ($mysqli_stmt->execute()) {

        echo PHP_EOL;
    } else {
        echo $mysqli_stmt->error;//執行失敗，錯誤資訊
    }
    $mysqli->close();

查

<?php
header('content-type:text/html;charset=utf-8');
$host = "127.0.0.1";
$user = "root";
$password = "root";
$db = "test";

$mysqli = new mysqli($host, $user, $password, $db);
if($mysqli->connect_errno){
    die($mysqli->connect_error);
}
$mysqli->set_charset('utf8');

$_GET['id'] = 2;
$id = $_GET['id'];

$sql = "SELECT `id`, `username`, `create_time` FROM `user` WHERE `id` > ?";
$mysqli_stmt = $mysqli->prepare($sql);
$mysqli_stmt->bind_param('i', $id);
$mysqli_stmt->execute();

if($mysqli_stmt->execute()){
    //bind_result() 繫結結果集中的值到變數
    $mysqli_stmt->bind_result($id, $username, $create_time);
    //遍歷結果集
    while($mysqli_stmt->fetch()){
        echo '編號'.$id;
        echo '使用者名稱'.$username;
        echo '新增時間'.$create_time;
        echo "<br/>";
    }
}

//釋放結果集
$mysqli_stmt->free_result();
$mysqli_stmt->close();
$mysqli->close();

本作品採用《CC 協議》，轉載必須註明作者和本文連結