php pdo对象使用详解: 连接数据库与exec方法

时间:2022-06-01 16:33:49

要使用pdo,首先需要开启pdo扩展,我这里已经开启了mysql的pdo扩展

ghostwu@dev:~$ php -m | grep pdo
pdo_mysql
ghostwu@dev:~$

1,连接数据库

mysql> show create database shop \G;
*************************** 1. row ***************************
Database: shop
Create Database: CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8 */
1 row in set (0.00 sec)
mysql> show create table account \G;
*************************** 1. row ***************************
Table: account
Create Table: CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_name` varchar(20) CHARACTER SET latin1 NOT NULL,
`user_pwd` varchar(40) CHARACTER SET latin1 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
try{
$dsn = 'mysql:host=localhost;dbname=shop';
$username = 'root';
$pwd = 'root';
$pdo = new PDO( $dsn, $username, $pwd );
var_dump( $pdo );
}catch( PDOException $e ) {
echo $e->getMessage();
}

>上面为参数形式连接数据库

>uri形式连接数据库

dsn.txt

mysql:host=localhost;dbname=shop;
try{
$dsn = 'uri:file:///home/ghostwu/php/php2/pdo/dsn.txt';
$username = 'root';
$pwd = 'root';
$pdo = new PDO( $dsn, $username, $pwd );
var_dump( $pdo );
}catch( PDOException $e ) {
echo $e->getMessage();
}

还有一种是php.ini中写dsn连接信息,不太推荐使用

2,exec执行一条sql语句,返回值为受影响的行数,如果没有受影响的行数,返回值为0,该方法对select语句无效

try{
$dsn = 'mysql:host=localhost;dbname=shop';
$username = 'root';
$pwd = 'root';
$pdo = new PDO( $dsn, $username, $pwd ); $sql =<<< SQL
create table if not exists user(
id int unsigned not null auto_increment,
username varchar( 20 ) not null unique,
pwd char( 32 ) not null,
email varchar( 30 ) not null,
primary key( id )
)engine myisam;
SQL;
$res = $pdo->exec( $sql );
var_dump( $res );
}catch( PDOException $e ) {
echo $e->getMessage();
}

3,执行insert语句

$insertUserSql = "insert into user( username, pwd, email ) values( 'ghostwu'," . "'" . md5( 'ghostwu' )  . "'" . ",'ghostwu@test.com')";
$res = $pdo->exec( $insertUserSql );
var_dump( $res );

4,一次性执行多条sql语句

 try{
$dsn = 'mysql:host=localhost;dbname=shop';
$username = 'root';
$pwd = 'root';
$pdo = new PDO( $dsn, $username, $pwd );
$bajie = md5( 'bajie' );
$wukong = md5( 'wukong' );
$tangsheng = md5( 'tangsheng' );
$insertUserSql =<<<EOF
insert into user( username, pwd, email ) values( 'wukong', '$wukong', 'wukong@huaguoshan.com' ),( 'bajie', '$bajie','bajie@tianting.com' ),( 'tangsheng', '$tangsheng','tangsheng@datang.com' );
EOF;
$res = $pdo->exec( $insertUserSql );
var_dump( $res );
}catch( PDOException $e ) {
echo $e->getMessage();
}

5,获取最后一次插入数据的自增id

    try{
$dsn = 'mysql:host=localhost;dbname=shop';
$username = 'root';
$pwd = 'root';
$pdo = new PDO( $dsn, $username, $pwd );
$insertUserSql = "insert into user( username, pwd, email ) values( 'zhanzhao'," . "'" . md5('zhanzhao' ) . "','zhan@kaifeng.com')";
echo $insertUserSql . PHP_EOL;
$res = $pdo->exec( $insertUserSql );
echo $pdo->lastInsertId() . PHP_EOL;
}catch( PDOException $e ) {
echo $e->getMessage();
}

6,执行delete语句

try{
$pdo = new PDO( "mysql:host=localhost;dbname=shop", 'root', 'root' );
$sql = "delete from user where id = 1";
$res = $pdo->exec( $sql );
var_dump( $res );
}catch( PDOException $e ) {
echo $e->getMessage();
}