1,配置文件下二维数组的连接,代码如下:
public function index()
{
$sql = "SELECT * FROM user WHERE username = :user";
$db1 = $this->load->database('slave',true);
$dbFlag = $db1->conn_id;
$loginQ = $dbFlag->prepare($sql);
$loginQ->bindParam(":user", $value, PDO::PARAM_STR, 4000);
$value='liwenming';
$loginQ->execute();
$result = $loginQ->fetchAll();
print_r($result);
}
注:通过$this->load->database()中第一个参数控制主从数据库的连接,其中,config中的database.php中的连接配置如下:
$db['slave'] = array(
'dsn' => 'mysql:host=localhost;port=3306;dbname=test',
'hostname' => 'localhost',
'username' => 'root',
'password' => '',
'database' => 'test',
'dbdriver' => 'pdo',
'dbprefix' => '',
'pconnect' => FALSE,
'db_debug' => (ENVIRONMENT !== 'production'),
'cache_on' => FALSE,
'cachedir' => '',
'char_set' => 'utf8',
'dbcollat' => 'utf8_general_ci',
'swap_pre' => '',
'encrypt' => FALSE,
'compress' => FALSE,
'stricton' => FALSE,
'failover' => array(),
'save_queries' => TRUE
);
2,通过dsn方式连接,代码如下:
public function test()
{
$sql = "SELECT * FROM user WHERE username = :user";
//pdo://用户名:密码@ip:端口/库名?subdriver=mysql'
$db1 = $this->load->database('pdo://root:@localhost:3306/test?subdriver=mysql', TRUE);
$dbFlag = $db1->conn_id;
$loginQ = $dbFlag->prepare($sql);
$loginQ->bindParam(":user", $value, PDO::PARAM_STR, 4000);
$value='liwenming';
$loginQ->execute();
$result = $loginQ->fetchAll();
print_r($result);
}
注:可以通过$this->load->database()中dsn变量的串改变主从的连接
总结,在使用pdo的过程中,一定要使用预编译及绑定变量的方式,这样才能有效防止sql注入,同时在pdo的使用过程中,要注意like查询方式的使用,比如like查询,应该用:
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE ?"); $stmt->execute(array("%$_GET[name]%"));不能使用如下代码:
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name LIKE '%?%'"); $stmt->execute(array($_GET['name']));
关于其他的使用方式,请查看pdo使用手册。