The difference between PDO and Mysqli

For PHP 5 and above, the following methods are recommended to connect to MySQL:

MySQLi extension ("i" means improved)

PDO (PHP Data Objects)

PDO is used in 12 different databases, MySQL is only for MySQL database.

Both support preprocessing statements to prevent SQL injection.

PDO connects to Mysql database

Code example:

<?php

$servername = "localhost";

$username = "username";

$password = "password";

try {   

$pdo = new PDO("mysql:host=$servername;", $username, $password);//Connect to database and create PDO object 

    echo "Successful connection";

}

catch(PDOException $e)

{   

echo $e->getMessage();//Catch and handle exceptions

}

?>

Data source dsn of PDO:

The most important four parameters to connect a database: database address, database name, user name, password

Because pdo supports many kinds of databases, we encapsulate database type, address and name in data source dsn

$dsn="{Database type}:host={Database address};dbname={Database name}";

Connect to the database and create the PDO object:

 New PDO (data source, user name, password);

Add, delete and modify data with PDO

$sql="INSERT INTO Table name VALUES (Value 1, Value 2,....)";

$num=$pdo -> exec($sql);//Use exec statement to execute SQL statement and return the number of affected rows.

$insertId=$pdo ->lastInsertId(); //PDO::lastInsertId - returns the ID or sequence value of the last inserted row

if($number>0){

    print'Successfully added' .$num.'Records, adding primary key id Yes,'.$insertId;

}

Querying data with PDO

Use PDO::query() to get the result set;

The query results are placed in the PDOStatement object;

Query single record: $result - > fetch()

Get all records: $result - > fetchall()

Direct traversal of result set: foreach ($result as $row) {}

The result set is mapped to the object and then traversed: the class name is consistent with the table name, the property is consistent with the field name, and the query is realized by the _get() magic method

fetch query single record

Setfetchmode (PDO:: fetch ﹣ Assoc) read mode: get the associated array indexed by the column name from the result set

PDOStatement::fetch() parsing result set

The fetch() method in the PDOStatement class can return the current record in the result set in some way, move the result set pointer to the next row, and return FALSE when the end of the result set is reached

$sql="SELECT Column name FROM Table name"

$result=$pdo->query($sql);

if($result && $result->rowCount()){

    $result->setFetchMode(PDO::FETCH_ASSOC);

    $row = $result->fetch();

    echo'$row['Field name1']';

    echo'$row['Field name2']';

    }

fetchAll query all records

The fetchAll() method is similar to the previous method fetch(), but it only needs to be called once to get all the rows in the result set and assign them to the returned array (2D)

$result->setFetchMode(PDO::FETCH_ASSOC);

$rows=$result->fetchAll();//Convert result set to 2D array

foreach ($rows as $row){

    echo'$row['Field name1']';

    echo'$row['Field name2']';   

}  

You can also skip fetch parsing and directly manipulate the result set

$result->setFetchMode(PDO::FETCH_ASSOC); 

$foreach ($result as $row){ 

}

Use model mapping to process result sets

PDO:: fetch Chu class returns a new instance of the request class, mapping the column name in the result set to the corresponding property name in the class

class student

{
    private $name；

    private $id;
    
        public function __get($propertyName)

{    
    return $this -> $propertyName ;
}
}
$result->setFetchMode(PDO::FETCH_CLASS，'student'); 

PDO preprocessing statement

Separate static SQL statement from dynamic data, and use placeholder "?" or ": field name in SQL statement

PDO::prepare() prepares the preprocessing statement;

execute([real replacement data]) executes the preprocessing statement

Placeholder ": field name"

$sql="SELECT Field name 1=:Field name 1, Field name 2=:Field name 2  FROM Table name";

$stmt = $pdo->prepare($sql);

$stmt->bindParam(":Field name 1", $value1);

$stmt->bindParam(" :Field name 2 ", $value2); 

//Assign values to variables

$stmt->execute();

Placeholder "?"? "

$sql= "SELECT ？, ？  FROM Table name";

$stmt = $pdo->prepare($sql);

$stmt->bindParam(1, $value1);

$stmt->bindParam(2, $value2); 

//Assign values to variables

$stmt->execute();