PHP操作数据库

1.连接数据库

1.1配置文件

代码展示：

<?php
//连接参数
return [
    //类型
    'type' => $type ?? 'mysql',
    //默认主机名
    'host' => $username ?? 'localhost',
    //默认编码集
    'charset' => $charset ?? 'utf8',
    //默认端口号
    'port' => $porst ?? '3306',
    //默认用户名
    'username' => $usernam ?? 'root',
    //默认用户密码
    'password' => $password ?? 'root',
    //默认数据库
    'dbname' => $dbname ?? 'phpedu',
];
?>

1.2连接数据库

代码展示：

<?php
//导入配置文件
$config = require 'config.php';
//将参数数组分解成独立的变量
extract($config);
//1.创建数据库的连接对象，连接数据库
$mysqli = new mysqli($host, $username, $password, $dbname);
//2.检测错误，die/exit， 终止代码，后面不再执行
if ($mysqli->connect_errno) die ('Connect Error:'. $mysqli->connect_error);
//3.设置字符编码
$mysqli-> set_charset($charset);
?>

---

2.PHP操作数据库的增、删、改、查

2.1 INSERT

2.1.1新增单条记录

代码展示：

<?php
//连接数据库
require 'connect.php';
//操作数据表
$sql = 'INSERT `users` SET `name`=?, `email`=?, `password`=?;';
//预处理操作防止sql注入攻击
$stmt = $mysqli->prepare($sql);
//占位符绑定变量名
$stmt->bind_param('sss', $name, $email, $password);
$name = 'mike';
$email = 'mike@php.cn';
$password = sha1('456789');
//执行sql语句
$stmt->execute() or die($stmt->error);
if($stmt->affected_rows === 1)
printf('成功新增 %s 条记录，新增主键id是： %d', $stmt->affected_rows, $stmt->insert_id);
else echo '没有记录被添加';
$mysqli->close();
?>

运行结果：

2.1.2新增多条记录

代码展示：

<?php
require 'connect.php';
$sql = 'INSERT `users` SET `name`=?, `email`=?, `password`=?;';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('sss', $name, $email, $password);
$users = [
    ['name'=>'james', 'email'=>'james@php.cn', 'password'=>sha1('123789')],
    ['name'=>'jhon', 'email'=>'jhon@php.cn', 'password'=>sha1('123789')],
    ['name'=>'jacky', 'email'=>'jacky@php.cn', 'password'=>sha1('123789')],
];
foreach($users as $user){
    extract($user);
    if ($stmt->execute())
    printf('成功新增 %s 条记录，新增主键id是：%d <br>', $stmt->affected_rows, $stmt->insert_id);
    else
    exit(sprintf('更新失败, $d, $s', $stmt->errno, $stmt->error));
}
$mysqli->close();
?>

运行结果：

2.2 UPDATE

代码展示：

<?php
require 'connect.php';
$sql = 'UPDATE `users` SET `name`=?, `email`=?, `password`=? WHERE `id`=?;';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('sssi', $name, $email, $password, $id);
$user = ['name'=>'ododo', 'email'=>'ododo@php.cn', 'password'=>'123321', 'id'=>1];
extract($user);
$stmt->execute() or die($stmt->error);
if ($stmt->affected_rows === 1)
printf('成功更新 %s 条记录', $stmt->affected_rows);
else echo '没有记录被更新';
$mysqli->close();
?>

运行结果：

2.3 DELETE

代码展示：

<?php
require 'connect.php';
$sql = 'DELETE FROM `users` WHERE `id`=?;';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id);
$id = 1;
$stmt->execute() or die($stmt->error);
if ($stmt->affected_rows === 1)
printf('成功删除 %s 条记录', $stmt->affected_rows);
else echo '没有记录被删除';
$mysqli->close();
?>

运行结果：

2.4 SELECT

2.4.1 逐条查询

fetch()_assoc() + while()

代码展示：

<?php
require 'connect.php';
$sql = 'SELECT `id`, `name`, `email` FROM `users` WHERE `id`>?;';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id);
$id = 17;
$stmt->execute() or die($stmt->error);
$result = $stmt->get_result();
if ($result->num_rows === 0) exit('结果集为空');
while ($user = $result->fetch_assoc()){
    vprintf('%d: %s | %s <br>', $user);
}
$result->free();
$mysqli->close();
?>

运行结果：

2.4.2 一次性获取所有满足条件的记录

fetch_all(), foreach()

代码展示：

<?php
require 'connect.php';
$sql = 'SELECT `id`, `name`, `email` FROM `users` WHERE `id`>?;';
$stmt = $mysqli->prepare($sql);
$stmt->bind_param('i', $id);
$id = 28;
$stmt->execute() or die($stmt->error);
$result = $stmt->get_result();
if ($result->num_rows === 0) exit('结果集为空');
$users = $result->fetch_all(MYSQLI_ASSOC);
foreach($users as $user){
    vprintf('%d: %s | %s <br>', $user);
}
$result->free();
$mysqli->close();
?>

运行结果：