PHP MYSQL PDO

淺談 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");