PHP学习笔记 20

来源：互联网发布：java调用url 编辑：程序博客网时间：2024/05/22 02:26

PHP 中的 MySQL

PHP 通过 mysqli 操作 MySQL，需要在 php.ini 中打开 extension=php_mysqli.dll
PHP 通过 PDO（PHP Data Object）操作数据，需要在 php.ini 中打开 extension=php_pdo_mysql.dll

数据库操作

PHP 对数据库操作有三种方式：面向对象方式、面向过程方式、PDO方式

面向对象方式

<?php$servername = "localhost";$username = "root";$password = "???";$conn = new mysqli($servername, $username, $password);if ($conn->connect_error) {    die("连接失败，Error： " . $conn->connect_error . "<br>");} else {    echo "连接成功！<br>";    // 创建数据库    $sql = "CREATE DATABASE test";    if ($conn->query($sql) === TRUE) {        echo "创建数据库成功！<br>";    } else {        echo "创建数据库失败：" . $conn->error . "<br>";    }    // 进入数据库    $conn->select_db("test");    //$sql = "USE test";    //$conn->query($sql);    // 创建表    $sql = "CREATE TABLE User (        id INT AUTO_INCREMENT PRIMARY KEY,        name TEXT,        email TEXT        )";    if ($conn->query($sql) === TRUE) {        echo "创建表成功！<br>";    } else {        echo "创建表失败！" . $conn->error . "<br>";    }    // 插入数据1    $sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";    if ($conn->query($sql) === TRUE) {        echo "插入数据成功！<br>";    } else {        echo "插入数据失败！" . $conn->error . "<br>";    }    // 插入数据2    $stmt = $conn->prepare("INSERT INTO User(name, email) VALUES (?,?)");    // i - integer    // d - double    // s - string    // b - BLOB    $stmt->bind_param("ss", $name, $email);    $name = "Gopher";    $email = "gopher@ggg.com";    if ($stmt->execute() === TRUE) {        echo "插入数据成功！<br>";    } else {        echo "插入数据失败！" . $conn->error . "<br>";    }    $name = "PHPer";    $email = "php@ppp.com";    if ($stmt->execute() === TRUE) {        echo "插入数据成功！<br>";    } else {        echo "插入数据失败！" . $conn->error . "<br>";    }    $stmt->close();    // 获取最后一次插入的ID    $lastId = $conn->insert_id;    // 删除数据    $sql = "DELETE FROM User WHERE id=$lastId";    if ($conn->query($sql) === TRUE) {        echo "删除数据成功！<br>";    } else {        echo "删除数据失败！" . $conn->error . "<br>";    }    // 修改数据    $sql = "UPDATE User SET name='Comaxy' WHERE id=1";    if ($conn->query($sql) === TRUE) {        echo "修改数据成功！<br>";    } else {        echo "修改数据失败！" . $conn->error . "<br>";    }    // 查找数据    $sql = "SELECT * FROM User";    $result = $conn->query($sql);    if ($result === FALSE) {        echo "查找失败！Error：" . $conn->error . "<br>";    } else {        if ($result->num_rows > 0) {            while ($row = $result->fetch_assoc()) {                echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";            }        }    }    // 删除表    $sql = "DROP TABLE User";    if ($conn->query($sql) === TRUE) {        echo "删除表成功！<br>";    } else {        echo "删除表失败！" . $conn->error . "<br>";    }    // 删除数据库    $sql = "DROP DATABASE test";    if ($conn->query($sql) === TRUE) {        echo "删除数据库成功！<br>";    } else {        echo "删除数据库失败！" . $conn->error . "<br>";    }    // 关闭数据库    $conn->close();    echo "数据库已关闭！<br>";}?>

面向过程方式

<?php$servername = "localhost";$username = "root";$password = "???";$conn = mysqli_connect($servername, $username, $password);if (!$conn) {    die("连接失败，Error： " . mysqli_connect_error() . "<br>");} else {    echo "连接成功！<br>";    // 创建数据库    $sql = "CREATE DATABASE test";    if (mysqli_query($conn, $sql) === TRUE) {        echo "创建数据库成功！<br>";    } else {        echo "创建数据库失败：" . mysqli_error($conn) . "<br>";    }    // 进入数据库    mysqli_select_db($conn, "test");    //$sql = "USE test";    //mysqli_query($conn, $sql);    // 创建表    $sql = "CREATE TABLE User (        id INT AUTO_INCREMENT PRIMARY KEY,        name TEXT,        email TEXT        )";    if (mysqli_query($conn, $sql) === TRUE) {        echo "创建表成功！<br>";    } else {        echo "创建表失败！" . mysqli_error($conn) . "<br>";    }    // 插入数据    $sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";    if (mysqli_query($conn, $sql) === TRUE) {        echo "插入数据成功！<br>";    } else {        echo "插入数据失败！" . msqli_error($conn) . "<br>";    }    // 获取最后一次插入的ID    $lastId = mysqli_insert_id($conn);    // 修改数据    $sql = "UPDATE User SET name='Comaxy' WHERE id=1";    if (mysqli_query($conn, $sql) === TRUE) {        echo "修改数据成功！<br>";    } else {        echo "修改数据失败！" . msqli_error($conn) . "<br>";    }    // 查找数据    $sql = "SELECT * FROM User";    $result = mysqli_query($conn, $sql);    if ($result === FALSE) {        echo "查找失败！Error：" . msqli_error($conn) . "<br>";    } else {        if (mysqli_num_rows($result) > 0) {            while ($row = mysqli_fetch_assoc($result)) {                echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";            }        }    }    // 删除数据    $sql = "DELETE FROM User WHERE id=$lastId";    if (mysqli_query($conn, $sql)) {        echo "删除数据成功！<br>";    } else {        echo "删除数据失败！" . msqli_error($conn) . "<br>";    }    // 删除表    $sql = "DROP TABLE User";    if (mysqli_query($conn, $sql)) {        echo "删除表成功！<br>";    } else {        echo "删除表失败！" . msqli_error($conn) . "<br>";    }    // 删除数据库    $sql = "DROP DATABASE test";    if (mysqli_query($conn, $sql)) {        echo "删除数据库成功！<br>";    } else {        echo "删除数据库失败！" . msqli_error($conn) . "<br>";    }    // 关闭数据库    mysqli_close($conn);    echo "数据库已关闭！<br>";}?>

PDO方式

<?php$servername = "localhost";$username = "root";$password = "33857431lxy4978";try {    $conn = new PDO("mysql:host=$servername;dbname=test", $username, $password);    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    echo "连接成功！<br>";    // 创建表    $sql = "CREATE TABLE User (    id INT AUTO_INCREMENT PRIMARY KEY,    name TEXT,    email TEXT    )";    $conn->exec($sql);    echo "创建表成功！<br>";    // 插入数据1    $sql = "INSERT INTO User(name, email) VALUES ('Cynhard', 'cynhard85@126.com')";    $conn->exec($sql);    echo "插入数据成功！<br>";    // 插入数据2    $stmt = $conn->prepare("INSERT INTO User(name, email) VALUES (:name, :email)");    $stmt->bindParam(":name", $name);    $stmt->bindParam(":email", $email);    $name = "Gopher";    $email = "gopher@ggg.com";    $stmt->execute();    echo "插入数据成功！<br>";    $name = "PHPer";    $email = "php@ppp.com";    $stmt->execute();    echo "插入数据成功！<br>";    // 获取最后一次插入的ID    $lastId = $conn->lastInsertId();    // 删除数据    $sql = "DELETE FROM User WHERE id=$lastId";    $conn->exec($sql);    echo "删除数据成功！<br>";    // 修改数据    $sql = "UPDATE User SET name='Comaxy' WHERE id=1";    $conn->exec($sql);    echo "修改数据成功！<br>";    // 查找数据    $sql = "SELECT * FROM User";    $stmt = $conn->prepare($sql);    $stmt->execute();    $stmt->setFetchMode(PDO::FETCH_ASSOC);    $result = $stmt->fetchAll();    foreach ($result as $i => $row) {        echo "id: " . $row["id"] . ", name: " . $row["name"] . ", email: " . $row["email"] . "<br>";    }    // 删除表    $sql = "DROP TABLE User";    $conn->exec($sql);    echo "删除表成功！<br>";    // 删除数据库    $sql = "DROP DATABASE test";    $conn->exec($sql);    echo "删除数据库成功！<br>";    // 关闭数据库    $conn = null;    echo "数据库已关闭！<br>";} catch (PDOException $e) {    echo "连接失败，Error：" . $e->getMessage() . "<br>";}?>

阅读全文

1 0