PDO operation on Mysql database

1. Briefly introduce what is PDO

PDO extension defines a lightweight and consistent interface for PHP to access database, which provides a data access abstraction layer, so that no matter what database is used, it can query and get data through consistent functions. PDO is released with PHP5.1 and can also be used in the PECL extension of PHP5.0. It cannot run in the previous PHP version.

2.PDO link database (two methods are introduced here)

,

1.
try{
//The first way to connect data
$dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
//host hostname dbname database name charset character set   
$pdo = new PDO($dsn, 'user', 'Password');
} catch(PDOException $e) {
    die('Database connection failed:'. $e->getMessage());
}

2.The second way to link
    try{
$pdo = new PDO('uri:file:///Absolute path file ',' user name ',' password ');

} catch(PDOException $e) {
    die('Database connection failed:'. $e->getMessage());
}

3. exec(), a function for adding, deleting and modifying;

    try{
        $dsn = 'mysql:host=localhost;dbname=test;charset=utf8';
        $pdo = new PDO($dsn, 'root', '123456');
        //You can manually set properties to pdo objects to manage their warning s and exceptions
        $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_WARNING);


        } catch(PDOException $e) {
            die('Database connection failed:'. $e->getMessage());
        }
        try{
            //Addition, deletion and modification
            $result = $pdo->exec('delete from star1 where id = 3');

        }catch (PDOException $e) {
            echo $e->getCode() . '<br />';
            echo $e->getMessage();
        }
        //When the pdo property is set to warning, you can print the following information
        //Wrong number
        echo  $pdo->errorCode();
        //Error details
        var_dump($pdo->errorInfo());

4.query functions for query processing

    try {
        $result = $pdo->query('select * from star');
        //This place needs to be noted that the object of query can only be retrieved after traversing through foreach

        foreach ($result as $key => $value) {
            var_dump($value);
        }
        } catch (PDOException $e) {
            echo $e->getMessage();
        }

5.PDO support affairs

try {
        //Open transaction
        //innodb: transaction supported myisam: transaction not supported
        $pdo->beginTransaction();
        $sql = "update star set money=money-10000 where id = 2";
        $result = $pdo->exec($sql);
        if ($result > 0) {
            echo 'Transfer out successful';
        } else {
            echo 'Feign failure';
            throw new Exception("Transfer out failed", 1);

        }
        $sql = "update star1 set money=money+10000 where id = 6";
        $result = $pdo->exec($sql);
        if ($result > 0) {
            echo 'Load successful';
        } else {
            echo 'Mount failed';
            throw new Exception("Load failed", 1);

        }
        //Transaction must be committed
        $pdo->commit();
    } catch (PDOException $e) {
        //Back to the original state
        $pdo->rollBack();
        echo $e->getMessage();
    }

6.PDO preprocessing (two kinds of placeholders)? : name

     try{
        //The preprocessing method is prepare
        $stmt = $pdo->prepare("insert into class (cname,prive) values(?,?)");
        //Method 1: bind a value to a parameter 
         $stmt->bindValue(1,'Xiao Ming');
         $stmt->bindValue(2,'1000000');
         $stmt->execute();

        //Method 2: define a variable to receive the value, and then attach the value to the binding
        $name = 'Xiaohong';
        $prive = '200000';
        $yu->bindValue(1,$name);
        $yu->bindValue(2,$prive);
        $yu->execute();

     }catch(PDOException $e){
        echo $e->getMessage();
     }
    //Method 3
    $yu = $pdo->prepare("insert into class (cname,prive) values (?,?)");
$yu->execute(['Maze','1898089080']); 

Tags: PDO Database SQL PHP

Posted on Mon, 04 May 2020 01:10:06 -0400 by christophebmx