PHP PDO的简单使用

Php代码  

PHP PDO的简单使用(query(),exec(),prepare(),Transaction,行锁)  

<?php  
            #数据库连接  
            $dbtype = &#39;mysql&#39;;  
            $host = &#39;localhost&#39;;  
            $db = &#39;test&#39;;  
            $user = &#39;root&#39;;  
            $psw = &#39;19sucai&#39;;  
              
            $dsn = $dbtype . &#39;:host=&#39; . $host . &#39;;&#39; . &#39;dbname=&#39; . $db;  
              
            try {  
                $dbh = new PDO($dsn, $user, $psw, array(PDO::ATTR_PERSISTENT=>true));  
                echo &#39;连接成功<br>&#39;;  
            } catch(Exception $e) {  
                die(&#39;Connect Failed Message: &#39; . $e->getMessage());  
            }  
              
            #使用query函数查询  
            $sql = &#39;SELECT * FROM user&#39;;  
            $query = $dbh->query($sql);  
            $query->setFetchMode(PDO::FETCH_ASSOC);    //设置结果集返回格式,此处为关联数组,即不包含index下标  
            $rs = $query->fetchAll();  
            var_dump($rs);  
              
            #使用exec函数进行INSERT,UPDATE,DELETE,结果返回受影响的行数  
            $sql = &#39;INSERT INTO user (`userName`, `userPassword`, `userAge`) SELECT (MAX(userId) + 1), \&#39;123456\&#39;, 18 FROM user&#39;;    //插入一行用户数据,其中userName使用userId最大值+1  
            // $rs = $dbh->exec($sql);  
            // var_dump($rs) . &#39;<br>&#39;;  
              
            #使用prepareStatement进行CURD  
            $sql = &#39;SELECT * FROM user WHERE userId = ?&#39;;  
            $stmt = $dbh->prepare($sql);  
            $stmt->bindParam(1, $userId);    //绑定第一个参数值  
            $userId = 1;  
              
            $stmt->execute();  
            $stmt->setFetchMode(PDO::FETCH_ASSOC);  
            $rs = $stmt->fetchAll();  
            var_dump($rs);  
              
            #使用事务  
            try {  
                $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);    //设置错误模式,发生错误时抛出异常  
                $dbh->beginTransaction();  
                $sql1 = &#39;SELECT bookNum FROM book WHERE bookId = ? FOR UPDATE&#39;;    //此处加上行锁,可以对bookNum做一些判断,bookNum>1,才做下一步更新操作  
                $sql2 = &#39;UPDATE book SET bookNum=bookNum-1 WHERE bookId = ?&#39;;    //加上行锁后,如果user1在买书,并且user1的买书过程没有结束,user2就不能执行SELECT查询书籍数量的操作,这样就保证了不会出现只有1本书,却两个人同时买的状况  
                $stmt1 = $dbh->prepare($sql1);  
                $stmt2 = $dbh->prepare($sql2);  
                $stmt1->bindParam(1, $userId);  
                $stmt2->bindParam(1, $userId);  
                $userId = 1;  
                $stmt1->execute();  
                $stmt2->execute();  
                $dbh->commit();  
            } catch(Exception $e) {  
                $dbh->rollBack();  
                die(&#39;Transaction Error Message: &#39; . $e->getMessage());  
            }  
?>