Backend Development
PHP Tutorial
Detailed tutorials and practical demonstrations on connecting to the database with PDO in PHP
Detailed tutorials and practical demonstrations on connecting to the database with PDO in PHP
PDO—Database Abstraction Layer
Introduction: The PDO extension defines a lightweight, consistent interface for PHP to access the database. PDO solves the problem of inconsistent database connections.
1. Introduction to PDO
This chapter mainly introduces the installation and configuration of PDO, and how to use PDO to connect to the database.
1-1 Introduction to PDO
PDO is the abbreviation of PHP Data Object. It is released together with PHP5.1 version and currently supports databases. Includes Firebird, FreeTDS, Interbase, MySQL, MS SQL Server, ODBC, Oracle, Postgre SQL, SQLite and Sybase. When operating different databases, you only need to modify the DSN (database source) in PDO to operate using the unified interface of PDO.
PDO Features:
Coding Consistency: PDO provides a single interface that can be used with various databases
Flexibility: PDO must be loaded at runtime Database driver, so there is no need to reconfigure and recompile PHP every time you use the database
High performance: PDO is written in C language and compiled into PHP. Compared with other solutions written in PHP, Although other functions are the same, it provides higher performance
Object-oriented features: PDO uses the object-oriented features of PHP5 to achieve more efficient database communication.
Note: PDO extension is just an abstract interface layer. Using PDO extension itself cannot realize any database operation. You must use a characteristic form to express their respective characteristics.
##1-2 PDO configuration and activation
#1-3 PDO connection database
//通过参数形式连接数据库
try{
$dsn='mysql:host=localhost;dbname=school';
$username='root';
$password='root';
$pdo=new PDO($dsn,$username,$password);
var_dump($pdo);
}catch (PDOException $e){
echo $e->getMessage();
};
需要注意：dsn是你的数据源
输出结果：object(PDO)#1 (0) { }
2. Use of PDO objects 主要介绍PDO对象方法的使用。 2-1 [PDO] exec()方法执行建表操作 输出结果：int(0); 2-2 [PDO] exec()方法执行插入记录操作 续上面：插入一条或多条记录 2-3 [PDO] exec()方法执行其他SQL操作 本身是king，修改为king，会是0条记录被影响. lastInsertId() 只能对插入有影响。 exec（）对查询无作用 2-4 [PDO] errorCode()和errorInfo()方法查看错误信息 2-5 [PDO] query()方法执行查询语句 注意：更多的用query()查询数据，用exec()实现增删改 2-6 [PDO] prepare()和execute()方法执行查询语句 指定类型：我们更多的是想得到关联数组，我们可以通过两种方式来获得，第一种方式：设置其取回数据的方式（设置参数、常量）；第二种方式：通过方法 三、 PDOStatement对象的使用 本章主要介绍PDOStatement对象方法的使用，以及参数的绑定与预处识。 3-1 [PDO] quote()方法防止SQL注入 带条件查询 登录实现的例子 3-2 [PDO] 预处理语句中的占位符的使用 3-3 [PDO] bindParam()方法绑定参数 两种方式:命名参数占位符，问号方式 3-4 [PDO] bindValue()方法绑定参数 向用户表插入数据：命名参数占位符，问号方式类似 3-5 [PDO] bindColumn()方法绑定参数 3-6 [PDO] fetchColumn()方法从结果集中返回一列 3-7 [PDO] debugDumpParams()方法打印一条预处理语句 四、PDO事务处理 主要介绍如何使用PDO进行事务处理 4-1 PDO错误处理模式 3种错误处理模式 4-2 PDO事务处理 好了，以上就是关于本文介绍的关于PHP中PDO操作数据库的详细操作以及实例了，相了解更多相关问题请访问PHP中文网：
<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//exec():执行一条sql语句并返回其受影响的行数;如果没有受影响的记录，它返回0
//exec对于select没有作用
//PHP是一个Web编程语言，在编程过程中难免会遇到用echo来输出大段的html和javascript脚本的情况，
//如果用传统的输出方法 ——按字符串输出的话，
//肯定要有大量的转义符来对字符串中的引号等特殊字符进行转义，以免出现语法错误。
//如果是一两处还可以容忍，但是要是一个完整的 html文本或者是一个200行的js我想是谁都会崩溃的。
//这就是PHP为什么要引入一个定界符的原因——至少一大部分原因是这样的。
/* 1.PHP定界符的作用就是按照原样，包括换行格式什么的，输出在其内部的东西；
2.在PHP定界符中的任何特殊字符都不需要转义；
3.PHP定界符中的PHP变量会被正常的用其值来替换。
PHP中的定界符格式是这样的：
<<<Eof
……
Eof;*/
$sql=<<<EOF
create table if not exists t_teacher(
id int UNSIGNED auto_increment primary key,
teaname varchar(20) not null UNIQUE,
pwd char(32) not null,
email varchar(30) not null
);
EOF;
$res= $pdo->exec($sql);
var_dump($res);
}catch (PDOException $e){
echo $e->getMessage();
};<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
$sql='insert into t_teacher values(default,"king5","'.md5('king').'","waly@qq.com");';
$res=$pdo->exec($sql);
echo $res;
}catch (PDOException $e){
echo $e->getMessage();
};<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//$sql='insert into t_teacher values(default,"king6","'.md5('king').'","waly@qq.com");';
$sql=<<<EOF
insert into t_teacher values
(default,"king7","'.md5('king').'","waly@qq.com"),
(default,"king8","'.md5('king').'","waly@qq.com"),
(default,"king9","'.md5('king').'","waly@qq.com")
EOF;
$res=$pdo->exec($sql);
echo '受影响的记录的条数为：'. $res."<br/>";
//$pdo->lastInsertId():得到新插入记录的ID号
//echo '最后插入的ID号为：'.$pdo->lastInsertId();
}catch (PDOException $e){
echo $e->getMessage();
};
<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//错误的表名
$sql='insert into t_teacher1 values(default,"king6","'.md5('king').'","waly@qq.com");';
$res=$pdo->exec($sql);
if($res===false){
//$pdo->errorCode(); SQLSTATE的值
echo $pdo->errorCode();
echo '<hr/>';
//$pdo->errorInfo():返回的错误信息的数组，数组中包含3个单元
//0=>SQLSTATE（错误编号）,1=>CODE(错误码),2=>INFO（错误信息）
$errInfo=$pdo->errorInfo();
print_r($errInfo);
}
}catch (PDOException $e){
echo $e->getMessage();
};<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//查询一条记录
//$sql='select * from t_teacher where id=5';
//查询多条记录
$sql='select * from t_teacher';
//$pdo->query($sql):执行sql语句，返回PDOStatement对象:需要遍历这个对象，将里面的内容取出来
$stmt=$pdo->query($sql);
var_dump($stmt); //只能看出这个语句返回的是一个对象
echo '<hr/>';
foreach ($stmt as $row){
print_r($row);
echo '<hr/>';
echo '编号：'.$row['id'].'<br/>';
echo '用户名：'.$row['teaname'].'<br/>';
echo '邮箱：'.$row['email'].'<br/>';
echo '<hr/>';
}
}catch (PDOException $e){
echo $e->getMessage();
};Query()用于插入数据
<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//插入一条记录
$sql='insert into t_teacher values(default,"king12","'.md5('king').'","waly@qq.com");';
//$pdo->query($sql):执行sql语句，返回PDOStatement对象:需要遍历这个对象，将里面的内容取出来
$stmt=$pdo->query($sql);
var_dump($stmt); //只能看出这个语句返回的是一个对象
}catch (PDOException $e){
echo $e->getMessage();
};
<?php
//查询单条语句
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//查询一条记录
$sql='select * from t_teacher where id=5';
//$pdo->prepare($sql);准备sql语句
$stmt=$pdo->prepare($sql);
//execute():执行预处理语句
$res=$stmt->execute();
//var_dump($res); //会返回bool(true)
//查数据使用
//fetch():得到结果集中的一条记录(作为索引+关联样式返回)
$row=$stmt->fetch();
print_r($row);
}catch (PDOException $e){
echo $e->getMessage();
};<?php
try{
//驱动器的名称 mysql
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//查询多条记录
$sql='select * from t_teacher';
//$pdo->prepare($sql);准备sql语句
$stmt=$pdo->prepare($sql);
//execute():执行预处理语句
$res=$stmt->execute();
//var_dump($res); //会返回bool(true)
//查数据使用
//fetch():得到结果集中的一条记录(作为索引+关联数组)
/*if($res){
while ($row=$stmt->fetch()){
print_r($row);
echo '<hr/>';
}
}*/
//fetchAll() 查询所有记录，以二维数组（索引+关联方式）
$rows=$stmt->fetchAll();
print_r($rows);
}catch (PDOException $e){
echo $e->getMessage();
};
<?php
header('content-type:text/html;charset=utf-8');
$username=$_POST['username'];
$password=$_POST['password'];
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//向数据库表查找对应的用户信息//如果存在，证明有这个用户，登录成功；否则登录失败
//输入 'or 1=1 # 可以查看查到的数据
//$sql="select * from t_user WHERE `name`='{$username}' AND `password`='{$password}'"; //通过quote():返回带引号的字符串，过滤字符串中的特殊字符
$username=$pdo->quote($username);
$sql="select * from t_user WHERE `name`={$username} AND `password`={$password}";
echo $sql;
$stmt=$pdo->query($sql);
//PDOStatement对象的方法：rowCount() :对于select操作返回的结果集中记录的条数，
//对于INSERT、UPDATE、DELETE返回受影响的记录的条数
echo $stmt->rowCount();
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
$username=$_POST['username'];
$password=$_POST['password'];
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//占位符有两种方法
//第一种方法
$sql="select * from t_user WHERE `name`=:username and `password`=:password";
$stmt=$pdo->prepare($sql);
$stmt->execute(array(":username"=>$username,":password"=>$password));
//PDOStatement对象的方法：rowCount() :对于select操作返回的结果集中记录的条数，
//对于INSERT、UPDATE、DELETE返回受影响的记录的条数
echo $stmt->rowCount();
//第二种方法
$sql="select * from t_user WHERE `name`=? and `password`=?";
$stmt=$pdo->prepare($sql);
$stmt->execute(array($username,$password));
echo $stmt->rowCount();
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
// $sql="insert into t_user(`name`,`password`,`sex`) VALUES (:username,:password,:sex)";
$sql="insert into t_user VALUES (DEFAULT ,:username,:password,:sex)";
$stmt=$pdo->prepare($sql);
$stmt->bindParam(":username",$username,PDO::PARAM_STR);
$stmt->bindParam(":password",$password,PDO::PARAM_STR);
$stmt->bindParam(":sex",$sex,PDO::PARAM_STR);
$username='张三';
$password='123654';
$sex='M';
$stmt->execute();
echo $stmt->rowCount();
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
// $sql="insert into t_user(`name`,`password`,`sex`) VALUES (:username,:password,:sex)";
$sql="insert into t_user VALUES (DEFAULT ,?,?,?)";
$stmt=$pdo->prepare($sql);
$stmt->bindParam(1,$username);
$stmt->bindParam(2,$password);
$stmt->bindParam(3,$sex);
$username='张三1';
$password='1236541';
$sex='F';
$stmt->execute();
echo $stmt->rowCount();
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
// $sql="insert into t_user(`name`,`password`,`sex`) VALUES (:username,:password,:sex)";
$sql="insert into t_user VALUES (DEFAULT ,?,?,?)";
$stmt=$pdo->prepare($sql);
$username='李四';
$password='123654';
$stmt->bindValue(1,$username);
$stmt->bindValue(2,$password);
$stmt->bindValue(3,'M');
$stmt->execute();
echo $stmt->rowCount();
$username='李四1';
$password='1236541';
$stmt->bindValue(1,$username);
$stmt->bindValue(2,$password);
$stmt->bindValue(3,'M');
$stmt->execute();
echo $stmt->rowCount();
}catch (PDOException $e){
echo $e->getMessage();
}
<?php
header('content-type:text/html;charset=utf-8');
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
// $sql="insert into t_user(`name`,`password`,`sex`) VALUES (:username,:password,:sex)";
$sql="select `name`,`password`,`sex` from t_user";
$stmt=$pdo->prepare($sql);
$stmt->execute();
echo '结果集中的列数一共有：'.$stmt->columnCount();
echo "<hr/>";
print_r($stmt->getColumnMeta(0));
$stmt->bindColumn(1,$username);
$stmt->bindColumn(2,$password);
$stmt->bindColumn(3,$sex);
while ($stmt->fetch(PDO::FETCH_BOUND)){
echo '用户名:'.$username."-密码：".$password."-性别：".$sex."<hr/>";
}
echo $stmt->rowCount();
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root'); $sql="select `name`,`password`,`sex` from t_user";
$stmt=$pdo->query($sql);
//索引默认从0开始
echo $stmt->fetchColumn(0),"<br/>";
echo $stmt->fetchColumn(1),"<br/>";
echo $stmt->fetchColumn(2);
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
// $sql="insert into t_user(`name`,`password`,`sex`) VALUES (:username,:password,:sex)";
$sql="insert into t_user VALUES (DEFAULT ,:username,:password,:sex)";
$stmt=$pdo->prepare($sql);
$stmt->bindParam(":username",$username,PDO::PARAM_STR);
$stmt->bindParam(":password",$password,PDO::PARAM_STR);
$stmt->bindParam(":sex",$sex,PDO::PARAM_STR);
$username='张三';
$password='123654';
$sex='M';
$stmt->execute();
$stmt->debugDumpParams();
}catch (PDOException $e){
echo $e->getMessage();
}静默模式
<?php
header('content-type:text/html;charset=utf-8');
/*PDO::ERRMODE_SLIENT:默认模式，静默模式*/
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
$sql="select * from nonet_user";
$stmt=$pdo->query($sql);
echo $pdo->errorCode();
echo '<br/>';
echo $pdo->errorInfo();
}catch (PDOException $e){
echo $e->getMessage();
}<?php
header('content-type:text/html;charset=utf-8');
/*PDO::ERRMODE_SLIENT:默认模式，静默模式
*PDO::ERRMODE_WARNING:警告模式
* PDO::ERRMODE_EXCEPTION:异常模式
*/
try{
//连接数据库
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root');
//设置警告模式
//$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);
//设置异常模式:推荐使用
$pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
$sql="select * from nonet_user";
$stmt=$pdo->query($sql);
echo $pdo->errorCode();
echo '<br/>';
echo $pdo->errorInfo();
}catch (PDOException $e){
echo $e->getMessage();
}
<?php
header('content-type:text/html;charset=utf-8');
try{
$option=array(PDO::ATTR_AUTOCOMMIT,0);
$pdo=new PDO('mysql:host=localhost;dbname=school','root','root',$option);
//开启事务
$pdo->beginTransaction();
var_dump($pdo->inTransaction());
$sql="update account set money=money-200 WHERE username='king'";
$res=$pdo->exec($sql);
if($res==0){
throw new PDOException('转账失败');
}
$res1=$pdo->exec('update account set money=money+200 WHERE username="queen"');
if($res1==0){
throw new PDOException('接收失败');
}
$pdo->commit();
}catch (PDOException $e){
$pdo->rollBack();
echo $e->getMessage();
}
The above is the detailed content of Detailed tutorials and practical demonstrations on connecting to the database with PDO in PHP. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1662
14
1419
52
1311
25
1261
29
1234
24
Solution to PHP Fatal error: Call to undefined method PDO::prepare() in
Jun 22, 2023 pm 06:40 PM
PHP is a popular web development language that has been used for a long time. The PDO (PHP Data Object) class integrated in PHP is a common way for us to interact with the database during the development of web applications. However, a problem that some PHP developers often encounter is that when using the PDO class to interact with the database, they receive an error like this: PHPFatalerror:CalltoundefinedmethodPDO::prep
How to use PHP's PDO_PGSQL extension?
Jun 02, 2023 pm 06:10 PM
As a popular programming language, PHP is widely used in the field of web development. Among them, PHP's PDO_PGSQL extension is a commonly used PHP extension. It provides an interactive interface with the PostgreSQL database and can realize data transmission and interaction between PHP and PostgreSQL. This article will introduce in detail how to use PHP's PDO_PGSQL extension. 1. What is the PDO_PGSQL extension? PDO_PGSQL is an extension library of PHP, which
PHP and PDO: How to perform bulk inserts and updates
Jul 28, 2023 pm 07:41 PM
PHP and PDO: How to perform batch inserts and updates Introduction: When using PHP to write database-related applications, you often encounter situations where you need to batch insert and update data. The traditional approach is to use loops to perform multiple database operations, but this method is inefficient. PHP's PDO (PHPDataObject) provides a more efficient way to perform batch insert and update operations. This article will introduce how to use PDO to implement batch insert and update operations. 1. Introduction to PDO: PDO is PH
PHP and PDO: How to perform paging queries and display data
Jul 29, 2023 pm 04:10 PM
PHP and PDO: How to query and display data in pages When developing web applications, querying and displaying data in pages is a very common requirement. Through paging, we can display a certain amount of data at a time, improving page loading speed and user experience. In PHP, the functions of paging query and display of data can be easily realized using the PHP Data Object (PDO) library. This article will introduce how to use PDO in PHP to query and display data by page, and provide corresponding code examples. 1. Create database and data tables
PHP and PDO: How to perform a full-text search in a database
Jul 30, 2023 pm 04:33 PM
PHP and PDO: How to perform a full-text search in a database In modern web applications, the database is a very important component. Full-text search is a very useful feature when we need to search for specific information from large amounts of data. PHP and PDO (PHPDataObjects) provide a simple yet powerful way to perform full-text searches in databases. This article will introduce how to use PHP and PDO to implement full-text search, and provide some sample code to demonstrate the process. first
PHP and PDO: How to handle JSON data in a database
Jul 29, 2023 pm 05:17 PM
PHP and PDO: How to handle JSON data in databases In modern web development, processing and storing large amounts of data is a very important task. With the popularity of mobile applications and cloud computing, more and more data are stored in databases in JSON (JavaScript Object Notation) format. As a commonly used server-side language, PHP's PDO (PHPDataObject) extension provides a convenient way to process and operate databases. Book
PHP PDO vs. mysqli: compare and contrast
Feb 19, 2024 pm 12:24 PM
PDOPDO is an object-oriented database access abstraction layer that provides a unified interface for PHP, allowing you to use the same code to interact with different databases (such as Mysql, postgresql, oracle). PDO hides the complexity of underlying database connections and simplifies database operations. Advantages and Disadvantages Advantages: Unified interface, supports multiple databases, simplifies database operations, reduces development difficulty, provides prepared statements, improves security, supports transaction processing Disadvantages: performance may be slightly lower than native extensions, relies on external libraries, may increase overhead, demo code uses PDO Connect to mysql database: $db=newPDO("mysql:host=localhost;dbnam
PHP PDO Tutorial: An Advanced Guide from Basics to Mastery
Feb 19, 2024 pm 06:30 PM
1. Introduction to PDO PDO is an extension library of PHP, which provides an object-oriented way to operate the database. PDO supports a variety of databases, including Mysql, postgresql, oracle, SQLServer, etc. PDO enables developers to use a unified API to operate different databases, which allows developers to easily switch between different databases. 2. PDO connects to the database. To use PDO to connect to the database, you first need to create a PDO object. The constructor of the PDO object receives three parameters: database type, host name, database username and password. For example, the following code creates an object that connects to a mysql database: $dsn="mysq
