淺談 PHP-MySQL, PHP-MySQLi, PDO 的差異
注意語法大小寫敏感
$hostname = 'localhost'; $username = 'user'; $password = 'password'; $db_name="drupaldb"; try{ $db=new PDO("mysql:host=".$hostname."; dbname=".$db_name, $username, $password, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")); //PDO::MYSQL_ATTR_INIT_COMMAND 設定編碼 //echo '連線成功'; $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION); //錯誤訊息提醒 //Query SQL $sql="Select * from poi "; $result=$db->query($sql); while($row=$result->fetch(PDO::FETCH_OBJ)){ //PDO::FETCH_OBJ 指定取出資料的型態 echo $row->id." "; } $db=null; //結束與資料庫連線 } catch(PDOException $e){ //error message echo $e->getMessage(); }登入查詢
//登入驗證 function user_login_query($email,$password) { // database connection $dbh = new PDO("mysql:host=" . db('hostname') . ";dbname=" . db('dbname'), db('username'), db('password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); //POD處理中文的顯示的問題 $sql_user = "SELECT * FROM user WHERE email = '$email' AND binary password= '$password' "; //binary區分大小寫 $stmt = $dbh->prepare($sql_user); $stmt->execute(); if($stmt->fetchColumn() > 0) { echo "login.." } else { echo "login fail"; } }查詢
require_once("db.config.php"); $dbh = new PDO("mysql:host=" . db('hostname') . ";dbname=" . db('dbname'), db('username'), db('password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); //POD處理中文的顯示的問題 $id = 15; $sql = "SELECT * FROM " . db('tablename') . " WHERE id = $id"; foreach ($dbh->query($sql) as $row) { print $row[fields('icon')] . "\t"; }單筆查詢
require_once("db.config.php"); $dbh = new PDO("mysql:host=" . db('hostname') . ";dbname=" . db('dbname'), db('username'), db('password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); //POD處理中文的顯示的問題 $id = 15; $sql_preson = "SELECT name FROM person WHERE id =$id"; $sth = $dbh->prepare($sql_preson); $sth->execute(); $result = $sth->fetch(PDO::FETCH_OBJ); echo $result->name ;查詢有幾筆
require_once("db.config.php"); $dbh = new PDO("mysql:host=" . db('hostname') . ";dbname=" . db('dbname'), db('username'), db('password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); //POD處理中文的顯示的問題 $statement = $dbh->prepare('SELECT *FROM yourtable'); $statement->execute(); $count = $statement->rowCount(); return $count ;查詢結果存入陣列
$sql = "SELECT * FROM yourtable WHERE(NOW()- INTERVAL 1 DAY > end) and open=1 order by start DESC "; $a=array("one", "two"); foreach ($dbh->query($sql) as $row) { array_push($a,$row['id']); } return $a[2];寫入
require_once("db.config.php"); // database connection $dbh = new PDO("mysql:host=" . db('hostname') . ";dbname=" . db('dbname'), db('username'), db('password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); //POD處理中文的顯示的問題 // new data $person_id =5; $image_id = 3; $sql = "INSERT INTO location (person_id,image_id,time) VALUES (:person_id,:image_id,NOW())"; $stmt = $dbh->prepare($sql); # the data we want to insert $params = array( ':person_id' => $person_id, ':image_id' => $image_id, ); $stmt->execute($params); echo "write record"; $dbh = null;更新
$id =2; $lat = 25.35; $lng = 150.05; $sql= "UPDATE location SET lat = :lat, lng = :lng WHERE id='$id'"; $stmt = $dbh->prepare($sql); $table_content = array( ':lat'=>$lat, ':lng'=>$lng ); $stmt->execute($table_content);刪除
$dbh = new PDO("mysql:host=" . db('hostname') . ";dbname=" . db('dbname'), db('username'), db('password'), array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8") ); //POD處理中文的顯示的問題 $sql= "DELETE FROM user WHERE userID=".$userID; $result = $dbh->exec($sql); if ($result) { // echo "del success"; //$json_output .= ' "action" :"success" ,'; } else { // echo "del fail"; //$json_output .= ' "action" :"fail" ,'; }淨空資料表
$dbh->query("TRUNCATE TABLE tablename");
沒有留言:
張貼留言
注意:只有此網誌的成員可以留言。