Current Location: Home> Latest Articles> Example of using PDOStatement::columnCount with PDO::query

Example of using PDOStatement::columnCount with PDO::query

gitbox 2025-05-26

In PHP, using PDO (PHP Data Objects) for database operations is a very safe and efficient way. This article will focus on the combination of the PDOStatement::columnCount function and the PDO::query function, to help you better understand how to get the number of columns of the query results and apply them in combination with actual queries.

1. Introduction to PDO::query function

The PDO::query function is used to execute a SQL statement and return a PDOStatement object. It is suitable for executing SQL queries that return result sets, such as SELECT statements.

Sample code:

 <?php
$pdo = new PDO('mysql:host=gitbox.net;dbname=testdb', 'username', 'password');
$sql = "SELECT id, name, email FROM users";
$stmt = $pdo->query($sql);
?>

In the above code, $stmt is a PDOStatement object that can be used to further process query results.

2. Introduction to PDOStatement::columnCount function

columnCount is a method of the PDOStatement class, which is used to get the number of columns in the result set. This method is particularly suitable for dynamically obtaining the number of columns when processing query results, which facilitates subsequent data processing.

Example:

 <?php
$columnCount = $stmt->columnCount();
echo "Query results include {$columnCount} List。";
?>

3. Combination use examples

Combining PDO::query and PDOStatement::columnCount , you can first execute the query, then get the number of columns, and finally iterate over the column names or data, which is convenient for subsequent operations.

Complete example:

 <?php
try {
    $pdo = new PDO('mysql:host=gitbox.net;dbname=testdb', 'username', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "SELECT id, name, email FROM users";
    $stmt = $pdo->query($sql);

    // 获取List数
    $columnCount = $stmt->columnCount();
    echo "Query results are total {$columnCount} List。<br>";

    // 获取并打印List名
    echo "List名List表：<br>";
    for ($i = 0; $i < $columnCount; $i++) {
        $meta = $stmt->getColumnMeta($i);
        echo $meta['name'] . "<br>";
    }

    // Get and print all data
    echo "Data content：<br>";
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        foreach ($row as $col => $val) {
            echo "$col: $val; ";
        }
        echo "<br>";
    }

} catch (PDOException $e) {
    echo "Database error：" . $e->getMessage();
}
?>

4. Summary

  • PDO::query executes SQL query and returns the PDOStatement object.

  • PDOStatement::columnCount can get the number of columns in the query result.

  • The combination of use allows you to dynamically obtain the result structure information, making it easier to traverse and operate the result.

Mastering the combination of these two functions can improve your database operation flexibility and write more robust and general code.

Related