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);

方法Ｏ
$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--按照對象的形式