PDO实用总结

    PDO类：

        try{

            $dsn = "mysql:host=localhost;port=3306;dbname=mytest";

            $db = new PDO($dsn,'root','123456');

        }catch(PDOException $e){

            echo "数据库连接失败：".$e->getMessage();

            exit;

        }

        $affected_rows = $db->getAttribute(PDO::ATTR_SERVER_INFO);

        方法：

            执行语句exec(),query(),prepare()

                有结果集的query(),执行select语句

                有影响行数的exec(),执行update，delete,insert……

         $db->exec();返回影响的行数   

             $db->lastInsertId()最后插入的自动增长的ID

         $stmt = $db->query(select ...)

                                foreach($stmt as $row){

                    print_r($row);

                    echo "
";

                }

            设置错误报告模式：errorCode(),errorInfo()

                默认模式：ERRMODE_SILENT     0  （不用errorCode(),errorInfo()就不提示 )

                        if(!$affected_rows){

                            echo $db->errorCode()."
";

                            print_r($db->errorInfo());

                        }else{

                            echo "执行成功！";

                        }

                警告模式：ERRMODE_WARRING    1   (有错误就警告)

                        在链接之后加：$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);

                        或在链接之前通过链接中的最后一个选项额外附加array(PDO::ATTR_ERRMODE=>PDO::ERRMODE_WARNING)

                抛出模式：ERRMODE_EXCEPTION  2   (有错误就可以抛出)

                                            try{

                            $affected_rows = $db->exec(.....);

                        }catch(PDOException $e){

                            echo $e->getMessage();

                        }

            事务处理：beginTransaction,commit,rollBack,inTransaction

                $db = new PDO("mysql:host=localhost;dbname=mytest

                try{

                 $db->beginTransaction();

                    $price = 500;

                    $sql = "update zhanghao set price=price-{$price} where id=1";

                    $affected_rows = $db->exec($sql);

                    if(!$affected_rows){

                        throw new PDOException("张三转出失败");

                    }

                    $sql = "update zhanghao set price=price+{$price} where id=2";

                    $affected_rows = $db->exec($sql);

                    if(!$affected_rows){

                        throw new PDOException("李四转入失败");

                    }

                    echo "交易成功！";

                 $db->commit();

                }catch(PDOException $e){

                    echo $e->getMessage();

                 $db->rollBack();

                }

             $db->setAttribute(PDO::ATTR_AUTOCOMMIT,1);

    PDOStatement类：

            一个准备好的语句（预处理语句） 和 处理结果集

            优点：执行效率高，与exec(),query()相比

                  安全性高（可以防止sql注入）

            try{

                $db = new PDO("mysql:host=localhost;dbname=mytest

            }catch(PDOException $e){

                echo $e->getMessage();

            }

            /*PDO中有两种占位符

             *  

             *   ?   参数       ---索引数组，按索引顺序使用

            */   名字参数       ---关联数组，按名称使用，和顺序无关

            //准备好一条语句，并在服务器端了，也已经编译过来了，就差为他分配数据过来

         $stmt = $db->prepare("insert into shops(name,price,num,desn) values(?,?,?,?)");//所有的sql都可以执行

         $stmt->bindParam(1,$name,PDO::PARAM_STR);

         $stmt->bindParam(3,$num,PDO::PARAM_INI);

         $stmt->bindParam(4,$desn,PDO::PARAM_STR);

         $stmt->bindParam(2,$price,PDO::PARAM_STR);

            //或

         //$stmt->execute(array("name1

         //$stmt->execute(array("name2

         //$stmt->execute(array("name3

     //$stmt = $db->prepare("insert into shops(name,price,num,desn) values(:name,:price,:num,:desn)");

         //$stmt->bindParam(":name",$name);

         //$stmt->bindParam(":num",$num);

         //$stmt->bindParam(":desn",$desn);

         //$stmt->bindParam(":price",$price);

            //或

         //$stmt->execute(array(":name"=>99,":num"=>"45

         //$stmt->execute(array(":name"=>98,":num"=>"45

         //$stmt->execute(array(":name"=>97,":num"=>"45

            if($stmt->execute()){

                echo "执行成功";

                echo "最后插入的ID：".$db->lastInsertId();

            }else{

                echo "执行失败";

            }

            var_dump($stmt);

            //如果是select  获取结果集 用fetch(),fetchAll()

         $stmt = $db->prepare("select id,name,price,num,desn from shops where id > :id");

         $stmt->execute(array(":id"=>100));//boolean

            //或者在前面加$stmt->setFetchMode(PDO::TETCH_NUM);

            while($row = $stmt->fetch(PDO::TETCH_NUM)){//PDO::TETCH_NUM,PDO::TETCH_ASSOC,PDO::TETCH_BOTH

                print_r($row);

                echo "
";

            }

         //$rows = $stmt->fetchAll();

            //print_r($rows);

         //$stmt = $db->prepare("select id,name,price,num,desn from shops where id > :id");

         //$stmt->execute(array(":id"=>100));//boolean

         //$stmt->blindColumn("id",$id,PDO::PRAMA_INT);

         //$stmt->blindColumn("name",$name,PDO::PRAMA_STR);

         //$stmt->blindColumn("price",$price);

         //$stmt->blindColumn(4,$num);

         //$stmt->blindColumn(5,$desn);

            //for($i=0;$i<$stmt->columnCount();$i++){

            // $field=$stmt->getColumnMeta($i);

            //    echo $field["name"]."---";

            //}

            //while($stmt->fetch()){

            //    echo "$id---$name---$price---$num---$desn
";

            //}

            //echo "总记录数：".$stmt->rowCount()."
";

            //echo "总字段数：".$stmt->columnCount()."
";

    PDOException类：

很多常量：