Current Location: Home> Latest Articles> How to Use PDOStatement::columnCount() to Accurately Get the Number of Columns in a Query Result?

How to Use PDOStatement::columnCount() to Accurately Get the Number of Columns in a Query Result?

gitbox 2025-06-09

1. What is PDOStatement::columnCount()?

PDOStatement::columnCount() is a method of the PDOStatement object that returns an integer value representing the number of columns in the result set. Its prototype is as follows:

public PDOStatement::columnCount(): int

After executing an SQL query (especially a SELECT statement), you can call this method to get how many columns are included in the result set.

2. Basic Usage

Here is a simple example:

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$stmt = $pdo->query('SELECT id, name, email FROM users');
<p>$columnCount = $stmt->columnCount();</p>
<p>echo "Number of columns: " . $columnCount;<br>
?><br>

The output after running the above code will be:

Number of columns: 3

This indicates that the query result contains three fields: id, name, and email.

3. Important Note: You Must Execute the Query Before Getting Column Count

A common mistake is trying to call columnCount() before executing the query or after executing a non-result-set query (such as INSERT, UPDATE, or DELETE). This is incorrect:

<?php
$stmt = $pdo->prepare('SELECT id, name FROM users WHERE id = :id');
// Not executed yet -> columnCount may return 0 or incorrect value
echo $stmt->columnCount(); // Usually 0
?>

To ensure the returned value is accurate, always call after executing the query and obtaining the result set:

$stmt->execute([':id' => 1]);
echo $stmt->columnCount(); // Correctly returns the number of columns

4. Typical Use Cases of columnCount()

1. Dynamically Building Table Headers

Suppose you are building a generic database table viewer tool that needs to dynamically generate the HTML table header row based on the query results. You can combine columnCount() with getColumnMeta() to achieve this:

<?php
$stmt = $pdo->query('SELECT * FROM users');
$colCount = $stmt->columnCount();
<p>echo "<table border='1'><tr>";<br>
for ($i = 0; $i < $colCount; $i++) {<br>
$meta = $stmt->getColumnMeta($i);<br>
echo "<th>" . htmlspecialchars($meta['name']) . "</th>";<br>
}<br>
echo "</tr>";<br>
?><br>

This will automatically generate a table header that corresponds to the query result.

5. Comparing rowCount() and columnCount()

Many beginners confuse rowCount() and columnCount(). The difference is as follows:

  • columnCount(): Gets the number of columns in the query result.

  • rowCount(): Gets the number of affected rows, but it is not always reliable for SELECT queries.

To get the number of rows returned by a SELECT query, you should use fetchAll() or iterate through the result set to count.

6. Compatibility Across Different Databases

columnCount() behaves consistently across most databases, but some drivers (such as older SQLite versions or certain ODBC drivers) may return 0 before execution. To get accurate results, follow these rules:

  1. Ensure execute() or query() has been called;

  2. Use drivers that support metadata;

  3. Do not call this method on statements without a result set.

7. Handling Field Information in REST API Output

If you are developing a REST API and want to return the field information for each record, you can use columnCount() to automatically generate the field list, for example:

<?php
$stmt = $pdo->query('SELECT * FROM users');
$columns = [];
for ($i = 0; $i < $stmt->columnCount(); $i++) {
    $meta = $stmt->getColumnMeta($i);
    $columns[] = $meta['name'];
}
<p>header('Content-Type: application/json');<br>
echo json_encode([<br>
'columns' => $columns,<br>
'data' => $stmt->fetchAll(PDO::FETCH_ASSOC)<br>
]);<br>
?><br>

This is especially convenient when building APIs like https://gitbox.net/api/v1/table/users.

Related