PHP and PDO: How to deal with BLOB and CLOB data types

PHP and PDO: How to handle BLOB and CLOB data types

Overview:
Using PDO (PHP Data Object) as a tool for the database access layer in PHP can easily connect and operate the database. Special handling is required when processing columns containing binary data (BLOB) or long text data (CLOB). This article will introduce how to use PDO to process BLOB and CLOB type data and provide code examples.

PDO Introduction:
PDO is a PHP extension that provides a unified API to access different types of databases. It provides a set of object-oriented methods to perform database operations. Through PDO, we can use the same code to access MySQL, SQLite, PostgreSQL and other databases.

Handling BLOB data type:
BLOB (Binary Large Object) is a database column type used to store binary data. When we need to store binary files such as images, audio, and video, we can use the BLOB type. When using PDO to process BLOB data, we need to use the prepare statement to pass binary data through placeholders.

The following is an example that shows how to save a picture as a BLOB type through PDO, read it from the database and display it:

// 连接数据库
$dsn = "mysql:host=localhost;dbname=test";
$user = "username";
$password = "password";

try {
    $pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage();
    exit;
}

// 保存图片为BLOB
$imagePath = 'path/to/image.jpg';
$imageData = file_get_contents($imagePath);

$stmt = $pdo->prepare("INSERT INTO images (data) VALUES (:data)");
$stmt->bindParam(':data', $imageData, PDO::PARAM_LOB);
$stmt->execute();

// 从数据库读取并显示图片
$stmt = $pdo->query("SELECT data FROM images LIMIT 1");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$imageData = $row['data'];

header("Content-type: image/jpeg");
echo $imageData;

Processing CLOB data type:
CLOB ( Character Large Object) is a database column type used to store character data. When we need to store long text, rich text and other character data, we can use the CLOB type. When using PDO to process CLOB data, we can use the bindValue method to pass long text data to the placeholder.

The following is an example showing how to save long text as CLOB type through PDO, read it from the database and display it:

// 连接数据库
$dsn = "mysql:host=localhost;dbname=test";
$user = "username";
$password = "password";

try {
    $pdo = new PDO($dsn, $user, $password);
} catch (PDOException $e) {
    echo "数据库连接失败: " . $e->getMessage();
    exit;
}

// 保存长文本为CLOB
$textContent = "This is a long text";

$stmt = $pdo->prepare("INSERT INTO texts (content) VALUES (:content)");
$stmt->bindValue(':content', $textContent, PDO::PARAM_STR);
$stmt->execute();

// 从数据库读取并显示长文本
$stmt = $pdo->query("SELECT content FROM texts LIMIT 1");
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$textContent = $row['content'];

echo $textContent;

Conclusion:
Use PDO to process BLOB and CLOB type data require special processing methods. For BLOB type data, we can use the bindParam method to transfer binary data; for CLOB type data, we can use the bindValue method to transfer character data. The above example code shows how to save and read BLOB and CLOB type data through PDO. Readers can make corresponding adjustments and expansions according to the actual situation.

The above is the detailed content of PHP and PDO: How to deal with BLOB and CLOB data types. For more information, please follow other related articles on the PHP Chinese website!