PDO学习

连接mysql 数据库

<?php 
$dsn = 'mysql:host=localhost;dbname=world;'; 
$user = 'user'; 
$password = 'secret'; 
try 
{
 $dbh = new PDO($dsn, $user, $password); 
} 
catch (PDOException $e) 
{
 echo 'Connection failed: ' . $e->getMessage(); 
} 
?> 

 连接sqlite

<?php 
$dsn = 'sqlite2:"C:\sqlite\world.db"'; 
try 
{
 $dbh = new PDO($dsn); 
} 
catch (PDOException $e) 
{
 echo 'Connection failed: ' . $e->getMessage(); 
} 
?>

连接postgreSQL

<?php 
$dsn = 'pgsql:host=localhost port=5432 dbname=world user=user '; 
$dsn .= 'password=secret'; 
try 
{
 $dbh = new PDO($dsn); 
} 
catch (PDOException $e) 
{
 echo 'Connection failed: ' . $e->getMessage(); 
} 
?> 

查询数据

<?php 
$country = 'USA';
try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = 'Select * from city where CountryCode ='.$dbh->quote($country);
    foreach ($dbh->query($sql) as $row) {
        print $row['Name']."\t";
        print $row['CountryCode']."\t";
        print $row['Population']."\n";
    }
}
catch(PDOException $e) {
    echo 'PDO Exception Caught.  ';
    echo 'Error with the database: <br />';
    echo 'SQL Query: ',$sql;
    echo 'Error: '.$e->getMessage();
}
?>

 使用prepare和excute

$country = 'USA';
try {
    $dbh = new PDO($dsn, $user, $password);
    $sql = 'Select * from city where CountryCode =:country';
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $stmt = $dbh->prepare($sql);
    $stmt->bindParam(':country', $country, PDO::PARAM_STR);
    $stmt->execute();
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        print $row['Name']."\t";
        print $row['CountryCode']."\t";
        print $row['Population']."\n";
    }
}
catch(PDOException $e) {
    echo 'PDO Exception Caught.  ';
    echo 'Error with the database: <br />';
    echo 'SQL Query: ',$sql;
    echo 'Error: '.$e->getMessage();
}

 

返回每个结果为对象

while ($row = $stmt->fetchObject()) {
    print $row->Name."\t";
    print $row->CountryCode."\t";
    print $row->Population."\n";
}

 

PDO statement设置错误模式

• PDO::ERRMODE_SILENT
• PDO::ERRMODE_WARNING
• PDO::ERRMODE_EXCEPTION

得到查询结果的行数

方法一

$stmt->execute();
$stmt->rowCount();

方法二

$query = "select count(*) from `` where condition";
$stmt->execut();
$stmt->fetchColumn()

注意：方法一不一定对每个数据库都有效

得到删除，更新，添加影响的行数

$stmt->execute();
$stmt->rowCount();

取得最后插入数据的id

$stmt->lastInsertId()

 pdo transaction

try {
    $dbh = new PDO($dsn, $user, $password);
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->beginTransaction();
    $sql = 'INSERT INTO transactions (acctNo, type, value, adjustment) VALUES (:acctNo, :type, :value, :adjust)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':acctNo'=>$acctFrom, ':type'=>$withdrawal, ':value'=>$amount, ':adjust'=>'-'));
    $sql = 'INSERT INTO transactions (acctNo, type, value, adjustment) VALUES (:acctNo, :type, :value, :adjust)';
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(':acctNo'=>$acctTo, ':type'=>$deposit, ':value'=>$amount, ':adjust'=>'+'));
    $dbh->commit();
}
catch(Exception $e) {
    $dbh->rollBack();
    //  further error handling here
}