PDO method of operating MySQL database in php-PHP Tutorial-php.cn

PDO method of operating MySQL database in php

不言

Release： 2023-03-29 21:52:02

Original

1479 people have browsed it

This article mainly introduces the PDO method of operating MySQL database with PHP. It has certain reference value. Now I share it with you. Friends in need can refer to it

Basic use

1) Connect to the database

$pdo = new PDO(“mysql:host=localhost; dbname=db_name”,username,password);

Copy after login

2) Execute SQL statement

$pdo -> query()    针对返回有结果的操作    适用于SELECT

$pdo -> exec()     针对没有返回结果的操作  适用于增删改

Copy after login

3) Get the result set (exclusive for query operation)

$stmt -> fetch($mode)      获取一条数据

$stmt -> fetchAll($mode)   获取所有数据

Copy after login

mode is optional PDO::FETCH_ASSOC, the result becomes associative array.

Instance

<?php

// PDO操作数据库例子(查询)

header("content-type:text/html;charset=utf-8");

// 连接数据库
$dsn = "mysql:host=localhost; dbname=test";

$pdo = new PDO($dsn,&#39;root&#39;,&#39;123456789&#39;);


/*********** 执行SQL语句  *************/

// 设置字符集
$pdo -> exec("set names utf8");

$sql = "SELECT * FROM users";
$stmt = $pdo -> query($sql);

// 获取结果集
$data = $stmt -> fetchAll(PDO::FETCH_ASSOC);

var_dump($data);

Copy after login

Transaction control

1) Open transaction

$pdo -> beginTransaction()

Copy after login

2)Transaction rollback

$pdo -> rollback()

Copy after login

3) Transaction submission

$pdo -> commit()

Copy after login

4) Automatic submission

$pdo -> setAttribute(PDO::ATTR_AUTOCOMMIT,1)

Copy after login

Instance

 exec("set names utf8");

//　开启事务
$pdo -> beginTransaction();

// SQL语句
$sql1 = "UPDATE users SET `money`=`money`+1 WHERE `id` = 1 ";
$r1 = $pdo -> exec($sql1);

$sql2 = "UPDATE users SET `money`=`money`-1 WHERE `id` = 12 ";
$r2 = $pdo -> exec($sql2);

// 做判断
if($r1 > 0 && $r2 > 0){
    $pdo -> commit();
    echo '操作成功';
}else{
    $pdo -> rollback();
    echo '操作失败';
}

$pdo -> setAttribute(PDO::ATTR_AUTOCOMMIT,1);

Copy after login

Preprocessing

1)$sql = "???"

Parameters in VALUE of the SQL statement are replaced by question marks.

2) Create a precompiled object

$pdo -> prepare($sql)

Copy after login

3) Parameter assignment

$stmt -> bindParam(参数位置 | 参数伪名, 变量名（需要有值）) 

$stmt -> bindValue(参数参数位置 | 参数伪名, 变量名（需要有值）| 具体的值)

Copy after login

$stmt -> bindParam detailed explanation:

$sql = "UPDATE users SET `money`=100 WHERE id = :num ";

// 注意，这里必须先给$num赋值
$num = 3;
$stmt -> bindParam(":num",$num);


另一种形式（占位符变成问号）
$sql = "UPDATE users SET `money`=100 WHERE id = ? ";

// 注意，这里必须先给$num赋值
$num = 3;
$stmt -> bindParam(":num",$num);

Copy after login

$stmt -> Detailed explanation of bindValue

After using bindValue to bind a variable, even if the value of the variable is changed before executing execute, the result will not change.

Please see here for details and write the link content here

4) Execute the code

$stmt -> execute()

Copy after login

5) Get the results (exclusive for query operation)

$stmt -> fetch($mode)      获取一个结果

$stmt -> fetchAll($mode)   获取所有结果

Copy after login

mode can Select PDO::FETCH_ASSOC, and the result becomes associative array.

Example

 exec("set names utf8");


// SQL语句
$sql = "SELECT * FROM users WHERE id > :num ";

// 创建预编译对象
$stmt = $pdo -> prepare($sql);

// 参数绑定
$num = 3;
$stmt -> bindParam(":num",$num);

// 执行SQL语句
$stmt -> execute();

// 获取结果集
$data = $stmt -> fetchAll(PDO::FETCH_ASSOC);

var_dump($data);

Copy after login

Related recommendations:

MySQLI method of operating MySQL database

The above is the detailed content of PDO method of operating MySQL database in php. For more information, please follow other related articles on the PHP Chinese website!