php pdo對象使用詳解: 串連資料庫與exec方法

標籤：use   執行   file   cte   參數   var   對象   base   pre   

要使用pdo，首先需要開啟pdo擴充，我這裡已經開啟了mysql的pdo擴充

[email protected]:~$ php -m | grep pdopdo_mysql[email protected]:~$ 

１，串連資料庫

mysql> show create database shop \G;*************************** 1. row ***************************       Database: shopCreate Database: CREATE DATABASE `shop` /*!40100 DEFAULT CHARACTER SET utf8 */1 row in set (0.00 sec)

mysql> show create table account \G;*************************** 1. row ***************************       Table: accountCreate Table: CREATE TABLE `account` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `user_name` varchar(20) CHARACTER SET latin1 NOT NULL,  `user_pwd` varchar(40) CHARACTER SET latin1 NOT NULL,  PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8

try{        $dsn = ‘mysql:host=localhost;dbname=shop‘;        $username = ‘root‘;        $pwd = ‘root‘;        $pdo = new PDO( $dsn, $username, $pwd );        var_dump( $pdo );    }catch( PDOException $e ) {        echo $e->getMessage();    }

>上面為參數形式串連資料庫

>uri形式串連資料庫

dsn.txt

mysql:host=localhost;dbname=shop;

try{        $dsn = ‘uri:file:///home/ghostwu/php/php2/pdo/dsn.txt‘;        $username = ‘root‘;        $pwd = ‘root‘;        $pdo = new PDO( $dsn, $username, $pwd );        var_dump( $pdo );    }catch( PDOException $e ) {        echo $e->getMessage();    }

還有一種是php.ini中寫dsn串連資訊，不太推薦使用

２，exec執行一條sql語句，傳回值為受影響的行數，如果沒有受影響的行數，傳回值為0，該方法對select語句無效

try{        $dsn = ‘mysql:host=localhost;dbname=shop‘;        $username = ‘root‘;        $pwd = ‘root‘;        $pdo = new PDO( $dsn, $username, $pwd );        $sql =<<< SQL        create table if not exists user(        id int unsigned not null auto_increment,        username varchar( 20 ) not null unique,        pwd char( 32 ) not null,        email varchar( 30 ) not null,        primary key( id )            )engine myisam;SQL;        $res = $pdo->exec( $sql );        var_dump( $res );    }catch( PDOException $e ) {        echo $e->getMessage();    }

３，執行insert語句

$insertUserSql = "insert into user( username, pwd, email ) values( ‘ghostwu‘," . "‘" . md5( ‘ghostwu‘ )  . "‘" . ",‘[email protected]‘)";        $res = $pdo->exec( $insertUserSql );        var_dump( $res );

４，一次性執行多條sql語句

 1 try{ 2         $dsn = ‘mysql:host=localhost;dbname=shop‘; 3         $username = ‘root‘; 4         $pwd = ‘root‘; 5         $pdo = new PDO( $dsn, $username, $pwd ); 6         $bajie = md5( ‘bajie‘ ); 7         $wukong = md5( ‘wukong‘ ); 8         $tangsheng = md5( ‘tangsheng‘ ); 9         $insertUserSql =<<<EOF10         insert into user( username, pwd, email ) values( ‘wukong‘, ‘$wukong‘, ‘[email protected]‘ ),( ‘bajie‘, ‘$bajie‘,‘[email protected]‘ ),( ‘tangsheng‘, ‘$tangsheng‘,‘[email protected]‘ );11 EOF;12         $res = $pdo->exec( $insertUserSql );13         var_dump( $res );14     }catch( PDOException $e ) {15         echo $e->getMessage();16     }

５，擷取最後一次插入資料的自增id

    try{        $dsn = ‘mysql:host=localhost;dbname=shop‘;        $username = ‘root‘;        $pwd = ‘root‘;        $pdo = new PDO( $dsn, $username, $pwd );        $insertUserSql = "insert into user( username, pwd, email ) values( ‘zhanzhao‘," . "‘" . md5(‘zhanzhao‘ ) . "‘,‘[email protected]‘)";        echo $insertUserSql . PHP_EOL;        $res = $pdo->exec( $insertUserSql );        echo $pdo->lastInsertId() . PHP_EOL;    }catch( PDOException $e ) {        echo $e->getMessage();    }

６，執行delete語句

try{        $pdo = new PDO( "mysql:host=localhost;dbname=shop", ‘root‘, ‘root‘ );        $sql = "delete from user where id = 1";        $res = $pdo->exec( $sql );        var_dump( $res );    }catch( PDOException $e ) {        echo $e->getMessage();    }

 

php pdo對象使用詳解: 串連資料庫與exec方法