PHP连接数据库

常用连接数据库操作

mysqli()函数是php操作数据库的函数库

mysqli_connect() 创建mysql连接
mysqli_connect_error() mysql连接报错
mysqli_connect_errno() mysql连接报错编码
mysqli_set_charset() 解决sql语句中无法使用中文匹配，也无法显示数据库中拿到的中文
mysqli_affected_rows() delete update instert 等受影响行数
mysqli_error() 输出sql语句的执行错误
mysqli_query() 执行sql语句
mysqli_fetch_all() 一次性查询所有数据 不好用
mysqli_fetch_assoc() 逐条查询数据 数据内容为关联数组
mysqli_close() 关闭数据库连接

数据库操作 - 函数版本

查询操作 - 字符串拼接(sql注入)

// 创建 mysql连接
$con = @mysqli_connect("localhost","root","666888","news",3306);
// 判断 mysql 连接
if (mysqli_connect_errno()){
   die("连接MySQL失败:".mysqli_connect_errno()." 错误为:".mysqli_connect_error());
}
// 设置字符集
mysqli_set_charset($con,"utf8");
// 定义sql语句
$sql = "select * from admin";
// 执行sql语句
$result = mysqli_query($con,$sql);
if(!$result){
   die("SQL语句失败:".mysqli_errno($con)." 错误为:".mysqli_error($con));
}
// 定义一个数组
$result_list = [];
// 遍历结果集
while($row = mysqli_fetch_assoc($result )){
   array_push($result_list,$row);
}
// 显示数据
var_dump($result_list);
// 释放 mysql连接
mysqli_close($con);

查询语句 - 预定义参数(防止sql注入)

$user = @$_POST['user'];
$passwd = @$_POST['passwd'];
$authcode = @$_POST['authcode'];
 // 创建 mysql连接
 $con = @mysqli_connect("localhost","root","666888","news",3306);
 // 判断 mysql 连接
 if (mysqli_connect_errno()){
     die("连接MySQL失败:".mysqli_connect_errno()." 错误为:".mysqli_connect_error());
 }
 // 设置字符集
 mysqli_set_charset($con,"utf8");
 // 定义sql语句 需要添加参数的位置 挖坑 ?
 $sql = "select * from admin where user = ? and passwd = ?";
 // 创建预处理对象
 $stmt = mysqli_prepare( $con,$sql);
 // 填充参数
 mysqli_stmt_bind_param($stmt,"ss",$user,$passwd);
 // 使用预定于执行sql
 mysqli_stmt_execute($stmt);
 // 使用预处理获取结果集
 $result = mysqli_stmt_get_result($stmt);
 // 定义一个数组
 $result_list = [];
 // 遍历结果集
 while($row = mysqli_fetch_assoc($result )){
     array_push($result_list,$row);
 }
 // 显示数据
 var_dump($result_list);
 // 释放预处理对象
 mysqli_stmt_close($stmt);
 // 释放 mysql连接
 mysqli_close($con);

更新、删除、插入-字符串拼接

$con = mysqli_connect("localhost", "root", "root", "test");
if(mysqli_connect_errno());
die("连接MySQL失败, 错误代码: ".mysqli_connect_errno().',错误描述:'.mysqli_connect_error());
}
$sql = "insert into ums_user values('1', '2', '3')";
$result = mysqli_query($con, $sql);
if ($result) {
  echo "操作成功,共计" . mysqli_affected_rows($con) . "行受影响";
} else {
  echo "插入失败";
}
mysqli_close($con);

更新、删除、插入-预定义参数

$con = mysqli_connect("localhost", "root", "root", "test");
if(mysqli_connect_errno());
die("连接MySQL失败, 错误代码: ".mysqli_connect_errno().',错误描述:'.mysqli_connect_error());
}
$sql =  "insert into ums_user values(?,?,?)";
//创建 stement
$stmt = mysqli_prepare($con,$sql);
//绑定参数
$params = [uniqid(), "4", "4"];
mysqli_stmt_bind_param($stmt, "sss", ...$params);
//执行sql
$success = mysqli_stmt_execute($stmt);
//取得影响行数
if ($success) {
  $count = mysqli_stmt_affected_rows($stmt);
  echo "执行成功";
} else {
  die("执行失败");
  mysqli_stmt_close($stmt);
  mysqli_close($con);
}

数据库操作 - 面向对象版本

查询操作 - 字符串拼接

//取得连接
$con = new mysql("localhost", "root", "root", "woniu");
if ($con->connect_error){
  die("连接 MySQL 失败, 错误代码: " . $con->connect_errno . ', 错误描述: ' . $con->connect_error);
}
//设置编码
$con->set_charset("utf8mb4");
$sql = "select * from users";
//执行sql
$result = $con->query($sql);
//获取结果
$rows = [];
while ($row = $result->fetch_assoc()) {
  array_push($rows, $row);
}
var_dump($rows);
//关闭连接
$con->close();

查询操作 - 预绑定参数

$con = new mysqli("localhost", "root", "root", "woniu");
if ($con->connect_error){
  die("连接 MySQL 失败, 错误代码: " . $con->connect_errno . ', 错误描述: ' . $con->connect_error);
}
//设置编码
$con->set_charset("utf8mb4");
$sql = "select * from ums_user where id = ?";
//创建stement
$stmt = $con->prepare($sql);
//绑定参数
$id = "2";
$stmt->bind_param("s", $id);
//执行sql
$succcess = $stmt->execute();
if ($success) {
  //取得结果集
  $result = $stmt->get_result();
  //循环结果集
  $rows = [];
  while ($row = $result->fetch_assoc()) {
    array_push($rows, $row);
  }
  var_dump($rows);
} else {
  die("执行失败");
}
//关闭连接
$stmt->close();
$con->close();

更新、删除、插入 - 字符串拼接

$con = new mysqli("localhost", "root", "root", "test");
if ($con->connect_error){
  die("连接 MySQL 失败, 错误代码: " . $con->connect_errno . ', 错误描述: ' . $con->connect_error);
}
//设置编码
$con->set_charset("utf8mb4");
$sql = "insert into ums_user values('5', '5', '5')";
//执行sql
$success = $con->query($sql);
if ($success) {
  //取得影响行数
  $count = $con->affected_rows;
  echo "执行成功,影响行数：". $count;
} else {
  die("执行失败");
}
//关闭连接
$con->close();

更新、删除、插入 - 预绑定参数

$con = new mysqli("localhost", "root", "666888", "sqlilabs");
if ($con->connect_error){
  die("连接 MySQL 失败, 错误代码: " . $con->connect_errno . ', 错误描述: ' . $con->connect_error);
}
//设置编码
$con->set_charset("utf8mb4");
$sql = "insert into users values(?,?,?)";
//创建stement
$stmt = $con->prepare($sql);
//绑定参数
$params = [rand(10,100), "6", "6"];
$stmt->bind_param("sss", ...$params);
//执行sql
$success = $stmt->execute();
if ($success) {
  //取得影响行数
  $count = $stmt->affected_rows;
  echo "执行成功，影响行数：" . $count;
} else {
  die("执行失败");
}
//关闭连接
$stmt->close();
$con->close();

二、PHP连接数据库封装

配置文件

define("MYSQL_HOST","localhost");
define("MYSQL_USER","root");
define("MYSQL_PASSWD","666888");
define("MYSQL_DB","news");
define("MYSQL_CHARSET","utf8");
define("MYSQL_PORT",3306);

函数版本封装

 include "../config/db_config.php";
// 创建连接函数
function connect(){
    // 创建 mysql连接
    $con = @mysqli_connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASSWD,MYSQL_DB,MYSQL_PORT);
    // 判断 mysql 连接
    if (mysqli_connect_errno()){
        die("连接MySQL失败:".mysqli_connect_errno()." 错误为:".mysqli_connect_error());
    }
    // 设置字符集
    mysqli_set_charset($con,MYSQL_CHARSET);
    return $con;
}
function close($con){
    mysqli_close($con);
}
function command($con,$sql,$ctrl){
    mysqli_query($con,$sql);
    $line = mysqli_affected_rows($con);
    if($line){
        return $line;
    }else {
        return 0;
    }
}
function insert($con,$sql){
    return command($con,$sql,"insert");
}
function update($con,$sql){
    return command($con,$sql,"update");
}
function delete($con,$sql){
    return command($con,$sql,"delete");
}
function select($con,$sql){
    $result = mysqli_query($con,$sql);
    if(!$result){
        return 0;
    }
    // 定义一个数组
    $result_list = [];
    // 遍历结果集
    while($row = mysqli_fetch_assoc($result )){
        array_push($result_list,$row);
    }
    return $result_list;
}

面向对象封装

include "/opt/lampp/htdocs/news/config/db_config.php";
// 面向对象封装数据库操作
class DB{
    public $con;
    function __construct() {
        $this->con = @mysqli_connect(MYSQL_HOST,MYSQL_USER,MYSQL_PASSWD,MYSQL_DB,MYSQL_PORT);
        if(mysqli_connect_errno()){
            die("连接MySQL失败:".mysqli_connect_errno()." 错误为:".mysqli_connect_error());
        }
        mysqli_set_charset($this->con,MYSQL_CHARSET);
    }
    function close(){
        mysqli_close($this->con);
    }
    function __destruct(){
        $this->close();
    }
    function command($sql,$ctrl){
         mysqli_query($this->con,$sql);
         $line = mysqli_affected_rows($this->con);
         if($line){
            return $line;
         }else {
            return 0;
         }
    }
    function insert($sql){
        return $this->command($sql,"insert");
    }
    function update($sql){
        return $this->command($sql,"update");
    }
    function delete($sql){
        return $this->command($sql,"delete");
    }
    function select($sql){
        $result = mysqli_query($this->con,$sql);
        if(!$result){
            return 0;
        }
        // 定义一个数组
        $result_list = [];
        // 遍历结果集
        while($row = mysqli_fetch_assoc($result )){
            array_push($result_list,$row);
        }
        return $result_list;
    }
}