php 串連mysql（查詢 增刪改）

標籤：串連數   set   let   color   波浪   tin   逸出字元   轉義   led   

1、串連資料庫方法：

<?php
//方法1
$mysqli = new mysqli("localhost", "root", "root", "easyadmin");
if (!$mysqli) {
    echo "database error";
} else {
    echo "php env successful";
}
$mysqli->close();

//方法2
try {
    $dbh = new PDO(‘mysql:host=localhost;dbname=easyadmin‘, ‘root‘, ‘root‘, array(PDO::ATTR_PERSISTENT => true)); //持久性串連
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $dbh->exec(‘SET CHARACTER SET utf8‘);
    echo "串連成功";
    echo "<br/>";
    /* 1.中斷連線*/
    //$dbh = null;
    $name = ‘kevin2%‘;
    /* 2.查詢：sql語句中user與name不能是單引號‘’,是左上方的波浪線~,sql語句以外的可以是單引號，否則查詢語句會報錯*/
    $sql = "select * from `user` where `name` like :name";
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(‘:name‘ => $name));
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        print_r($row);
        echo("<br/>");
    }
//    /*3.添加*/
//    $sql = "INSERT INTO `user` (`name` ,`password`)VALUES (:name, :password)";
//    $stmt = $dbh->prepare($sql);
//    $stmt->execute(array(‘:name‘ => ‘kevin2‘, ‘:password‘ => ‘123459‘));
//    echo $dbh->lastInsertId();

    /*4.刪除*/
    $name = "zj";
    $sql = "delete from `user` where `name` like :name";
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(‘:name‘ => $name));
    echo $stmt->rowCount();

    /*5.修改*/
    $id = 3;
    $sql = "update `user` set `password`=:password where `id`=:id ";
    $stmt = $dbh->prepare($sql);
    $stmt->execute(array(‘:id‘ => $id, ‘:password‘ => ‘testabc‘));
    echo "Update：" . $stmt->rowCount();


} catch (PDOException $ex) {
    print"Error!:" . $ex->getMessage() . "<br/>";
    die();
}


?>

2.參數綁定
舉例：
 $stmt = $dbh->prepare($sql);    
 $stmt->bindParam(‘:country‘, $country, PDO::PARAM_STR);    
 $stmt->execute();   

 

3.事務

  try {  
  　　$dbh = new PDO(‘mysql:host=localhost;dbname=test‘, ‘root‘, ‘root‘);  
  　　$dbh->query(‘set names utf8;‘);  
  　　$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);  
  　　$dbh->beginTransaction();  
  　　$dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES (‘mick‘, 22);");  
 　　 $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES (‘lily‘, 29);"); 
 　　 $dbh->exec("Insert INTO `test`.`table` (`name` ,`age`)VALUES (‘susan‘, 21);");  
 　　 $dbh->commit(); 
 } catch (Exception $e) {  
 　　 $dbh->rollBack();  
 　　 echo "Failed: " . $e->getMessage();  
 } 

 

4.foreach語句

<?php
$dbh->query($sql); 當$sql 中變數可以用$dbh->quote($params); //逸出字元串的資料
$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"; 
} 
?>

 

php 串連mysql（查詢 增刪改）