Google Code Prettify - 輕量級的語法上色工具

顯示具有 PDO 標籤的文章。 顯示所有文章
顯示具有 PDO 標籤的文章。 顯示所有文章

星期二, 9月 08, 2015

PDO SQL語法的適用


// 使用 quote 避免 SQL Injection;相當於 mysql_real_escape_string()
$sql  = "INSERT INTO product (name, description, price, sale_status) ";
$sql .= "VALUES (" . $db->quote($strName) . ", " . $db->quote($strDescription) . ", " . $strPrice . ", " . $db->quote($strStatus) . ")";
$db->query($sql);

方法O
$sql = 'select * from test';
foreach ( $dbh->query($sql) as $value)
{
    echo $value[col];
};
// 使用 execute(),會自動 quote $where 的參數
方法一
$sql = "INSERT INTO `users` (id, name, gender, location) VALUES(?, ?, ?, ?)"; $sth = $dbh->prepare($sql); $sth->execute(array(1, 'roga', 'male', 'tpe')); 方法二 $sth = $dbh->prepare('SELECT * FROM table WHERE id = :id AND name = :name'); $where = array(':id' => 2, ':name' => 'John'); $sth->execute($where); foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) { print_r($row); } 方法三 $sth = $dbh->prepare('update db set zh_CN= :str where SN=:SN'); $sth->bindParam(':str',$str,PDO::PARAM_STR,12); $sth->bindParam(':SN',$SN); $sth->execute(); 方法四 $sth = $db->prepare('select * from table where id =:id and title= :title '); $sth->bindValue(':id', $_GET['id'], PDO::PARAM_INT); $sth->bindValue(':title', $_GET['title'], PDO::PARAM_STR); $sth->execute(); $sth->fetch(PDO::FETCH_ASSOC);
或 $sth->fetch(PDO::FETCH_OBJ};

PDO輸出轉換有幾種型態
  • PDO::FETCH_NUM--數字索引數組形式
  • PDO::FETCH_ASSOC--關聯數組形式
  • PDO::FETCH_OBJ--按照對象的形式

啟用 PDO 連結資料庫


PHP 5.3 以上已內建PDO模組

php.ini 或 extensions.ini 中啟用這二行
extension=pdo.so
extension=pdo_mysql.so


define('DB_NAME','test');
define('DB_USER','test');
define('DB_PASSWD','test');
define('DB_HOST','localhost');
define('DB_TYPE','mysql');

try( //有沒有try其實沒關係 多學一個try的用法也不錯
//連接MySQL
$dbh = new PDO(DB_TYPE.':host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASSWD);
//資料庫使用 UTF8 編碼
$dbh->query('SET NAMES UTF8');
} catch (PDOException $e) {
    echo 'Error!: ' . $e->getMessage() . ' ';
}


//連接Access
//http://www.sitepoint.com/using-an-access-database-with-php/
$dbName = $_SERVER["DOCUMENT_ROOT"] . "\products.mdb";
if (!file_exists($dbName)) {
    die("Could not find database file.");
}
$dbh = new PDO("odbc:DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$dbName; Uid=; Pwd=;");