【CI PDO】CI框架下 Mysql PDO 使用方法小记

来源：互联网发布：企业网站用什么cms 编辑：程序博客网时间：2024/06/06 00:53

1、设置pdo链接配置信息

my_config.php

$config['mysql_host'] = 'mysql:dbname=test_db;host=127.0.0.1';$config['mysql_username'] = 'root';$config['mysql_passwd'] = '1234';$config['mysql_db'] = 'test_db';$config['dbdriver'] = 'pdo';

database.php

include(APPPATH."config/my_config.php");......$db['default']['hostname'] = $config['mysql_host'];$db['default']['username'] = $config['mysql_username'];$db['default']['password'] = $config['mysql_passwd'];$db['default']['database'] = $config['mysql_db'];$db['default']['dbdriver'] = $config['dbdriver'];

2、手动链接数据库

$this->load->database();

3、常见pdo数据库操作

3.1 查询

// 查询$sql1 = "SELECT * FROM tbl_test1 WHERE condition1 = :condition1 and condition2 = :condition2";$sql_data1 = Array(":condition1" => 1,":condition2" => "abc");$sth1 = $this->db->conn_id->prepare($sql1);$sth1->execute($sql_data1);// 获取一条$result1 = $sth1->fetch(PDO::FETCH_ASSOC);// 获取所有// $result1 = $sth1->fetchAll(PDO::FETCH_ASSOC);// 判断是否成功if($result1){// 查询成功}else{// 查询失败}

3.2 更新

// 更新$sql2 = "UPDATE tbl_test1 SET `key1` = :val1, `key2` = :val2 WHERE  condition1 = :condition1 and condition2 = :condition2";$sql_data2 = Array(":val1" => 1,":val2" => "hello",":condition1" => 1,":condition2" => "abc");$sth2 = $this->db->conn_id->prepare($sql2);$sth2->execute($sql_data2);// 判断是否成功if($sth2->rowCount() >0){// 更新成功}else{// 更新失败}

3.3 插入

// 插入$sql3 = "INSERT INTO tbl_test1 (`key1`,`key2`,`key3`,`key4`,`key5`) VALUES ( :val1 , :val2 , :val3 , :val4 , :val5 )";$sql_data3 = Array("val1" => 1,"val2" => "hello","val3" => 100.25,"val4" => "随便写写""val5" => "2015-10-30");$sth3 = $this->db->conn_id->prepare($sql3);$result3 = $sth3->execute($sql_data3);// 判断是否成功if($result3){// 插入成功// 最新插入的数据的自增长id// $this->db->conn_id->lastInsertId();}else{// 插入失败if($sth3->errorCode() == 23000){// 409，需要唯一的字段冲突重复了}else{// 其它}}

3.4 事务

// 事务// 所有select语句都可以在事务开启前操作，即使查询不成功，也不会影响数据的完整性// insert、update、delete相互关联，涉及数据完整性的操作，需要放在事务内部，一旦其中有一个操作失败，就进行事务回滚，以确保事务完整try{// do select here// 关闭 PDO 的自动提交$this->db->conn_id->setAttribute(PDO::ATTR_AUTOCOMMIT, false);// 开启异常处理$this->db->conn_id->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);// 开启一个事务$this->db->conn_id->beginTransaction();// do insert、update、delete// insert、update、delete失败的时候抛出异常if($sth2->rowCount() <= 0){throw new PDOException('Update Failed');}// 手动提交$this->db->conn_id->commit();}catch(PDOException $e){// 事务回滚，抛出异常$this->db->conn_id->rollBack();$return = Array("code" => 500,"data" => $e->getMessage());return $return;}

0 0