PDO method of operating MySQL database in php

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);

2) Execute SQL statement

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

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

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

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

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

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);

Transaction control

1) Open transaction

$pdo -> beginTransaction()

2)Transaction rollback

$pdo -> rollback()

3) Transaction submission

$pdo -> commit()

4) Automatic submission

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

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);

Preprocessing

1)$sql = "???"

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

2) Create a precompiled object

$pdo -> prepare($sql)

3) Parameter assignment

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

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

$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);

$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()

5) Get the results (exclusive for query operation)

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

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

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);

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!