Using PDO for Database Write and Read Operations in PHP

Database management is divided into write operations and query operations.

Write operations use PDO to perform insert, update, delete, handling possible SQL errors and result processing.

Query operations execute SQL statements to retrieve data from the database.

1. Initialize PDO with a function that creates a PDO instance, sets charset to utf8, and returns the connection.

//pdo_function.php
<?php
// Database connection, set charset utf8
function pdo_init() {
    $dns = "mysql:host=localhost;port=3306;dbname=my_database";
    $user = 'root';
    $pass = 'root';
    $pdo = @new PDO($dns,$user,$pass);
    if(!$pdo){
        exit("数据库连接问题");
    }
    $pdo->exec("set names utf8");
    echo "连接初始化数据库成功";
    return $pdo;
}
?>

2. Write operation function pdo_exec($pdo,$sql) executes the SQL and handles errors, returning affected rows.

3. Read operation function pdo_query($pdo,$sql) runs a query and returns the statement object, with error handling.

// Write operation
function pdo_exec($pdo,$sql){
    $result = $pdo->exec($sql);
    if($result===false){
        echo "SQL语句错误<br>";
        echo "SQL错误代码：" . $pdo->errorcode() . "<br>";
        echo "SQL错误编号：" . $pdo->errorInfo()[2] . "<br>";
        exit;
    }
    return $result;
}

// Read operation
function pdo_query($pdo,$sql){
    $statement = $pdo->query($sql);
    if($statement===false){
        echo "SQL语句错误<br>";
        echo "SQL错误代码：" . $pdo->errorcode() . "<br>";
        echo "SQL错误编号：" . $pdo->errorInfo()[2] . "<br>";
        exit;
    }
    return $statement;
}

Example of a write operation inserting a student record.

<?php
require "pdo_function.php";
$pdo = pdo_init();
$sql = "INSERT INTO `student` (`id`, `name`, `age`, `hobbby`) VALUES ('6', '白也', '70', 'drinking')";
$result = pdo_exec($pdo,$sql);
echo $result . "<br>"; // outputs number of affected rows
?>

Example of a query operation selecting all students.

<?php
require "pdo_function.php";
$pdo = pdo_init();
$sql = "select * from `student`";
$statement = pdo_query($pdo,$sql);
?>