PHP学习笔记六（数据库操作）

数据库操作

1、目录结构

• text

  • config
    • config.ini
  • db
    • getcon.php
  • index.php

2、配置参数文件 config.ini

[mysql]
servername = "localhost"
username = "root"
password = "密码"
dbname = "text" // 数据库名称

3、定义数据库类（getcon.php）

 class Database {
     // 获取连接
    static function getcon()
    {
        try {
            // 解析参数
            $config = parse_ini_file(realpath(dirname(__DIR__) . '/config/config.ini'), true);
            // 读取参数
            $servername = $config['mysql']['servername'];
            $username = $config['mysql']['username'];
            $password = $config['mysql']['password'];
            $dbname = $config['mysql']['dbname'];
            // 创建连接
            $con = new mysqli($servername, $username, $password, $dbname);
            // 测试连接是否成功
            if ($con->connect_error) {
                return null;
            }
            return $con;
        }catch (Exception $e) {
            echo $e->getMessage();
        }

    }
}

4、测试连接（index.php）

include("./db/getcon.php") ;
$con = Database::getcon();
if($con != null) 
{
    echo "连接成功";
}

5、插入数据

$sql = "insert into user (user, password) values ('Doe', '456')";
if ($con->query($sql) === TRUE)
{
    echo "新记录插入成功";
}
// 关闭连接
$con->close();

6、插入多条数据

$sql = "insert into user (user, password) values ('Doe', '456');"
$sql .= "insert into user (user, password) values ('John', '321');"
$sql .= "insert into user (user, password) values ('Jon', '321');"
if ($con->multi_query($sql) === TRUE)
{
    echo "新记录插入成功";
}
// 关闭连接
$con->close(); 

7、查找多条数据

 $sql = "SELECT * FROM user";
    $result = $con->query($sql);
    if ($result->num_rows > 0) {
        // 输出数据
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - Name: " . $row["user"]. " " . $row["password"]. "<br>";
        }
    } else {
        echo "0 结果";
    }
// 关闭连接
$con->close(); 

8、更新数据

$sql = "update user set user='haohao' where user='Doe'";
$result = $con->query($sql);
if ($result == 1)
{
   echo "更新数据成功";
}
// 关闭连接
$con->close(); 

9、删除数据

$sql = "delete from user where user='haohao'";
$result = $con->query($sql);
if ($result == 1)
{
  echo "删除数据成功";
}
// 关闭连接
$con->close(); 

10、预处理语句

$stmt = $con->prepare("insert into user (user, password) values (?, ?)");
$stmt->bind_param("ss", $user, $password);
$user = "haohao";
$password = "123";
$stmt->execute();