PDO columnCount(): Number of Columns Returned by a Query

PDOStatement::columnCount() returns the number of columns in the result set produced by a query.

require 'config.php';

$stmt=$dbo->query(
    "SELECT id,userid FROM pdo_admin"
);

echo 'Number of columns: '.
    $stmt->columnCount();

Output:

Number of columns: 2

The count is based on the columns returned by the SELECT query, not simply on the number of columns defined in the database table.

PDOStatement::columnCount() Syntax Top ↑

$number_of_columns=$stmt->columnCount();

columnCount() is called on the PDOStatement object returned by query() or created with prepare().

$stmt=$dbo->prepare(
    "SELECT id,userid,status
     FROM pdo_admin"
);

$stmt->execute();

$count=$stmt->columnCount();

echo $count;

Output:

3

The SELECT List Controls the Column Count Top ↑

The number returned by columnCount() changes according to the columns requested by the query.

$stmt=$dbo->query(
    "SELECT id,userid
     FROM pdo_admin"
);

echo $stmt->columnCount();

Output:

2

If another column is added:

$stmt=$dbo->query(
    "SELECT id,userid,status
     FROM pdo_admin"
);

echo $stmt->columnCount();

Output:

3

This makes columnCount() useful when an application needs to inspect the structure of a returned result set dynamically.

Using columnCount() with SELECT * Top ↑

If the query uses SELECT *, the result normally contains all columns exposed by that SELECT.

$stmt=$dbo->query(
    "SELECT *
     FROM pdo_admin"
);

echo 'Columns returned: '.
    $stmt->columnCount();

If the query returns five columns, the output is:

Columns returned: 5

What if the SELECT Query Returns No Rows? Top ↑

columnCount() describes the columns in the result set, not the number of rows returned.

$stmt=$dbo->prepare(
    "SELECT id,name,class,mark
     FROM student
     WHERE id=:id"
);

$stmt->bindValue(
    ':id',
    999999,
    PDO::PARAM_INT
);

$stmt->execute();

echo 'Columns: '.
    $stmt->columnCount();

The query can return zero student records while still having a result structure containing the four selected columns.

Do not use columnCount() to determine whether records were found. Fetch the record or use an appropriate SQL count query when a record total is required.

Get Column Names with getColumnMeta() Top ↑

After finding the number of result columns, getColumnMeta() can be used to inspect metadata for individual columns when the PDO driver supports it.

$stmt=$dbo->query(
    "SELECT id,name,class,mark
     FROM student"
);

$column_count=$stmt->columnCount();

for($i=0;$i<$column_count;$i++){
    $meta=$stmt->getColumnMeta($i);

    if($meta!==false && isset($meta['name'])){
        echo htmlspecialchars(
            (string)$meta['name'],
            ENT_QUOTES,
            'Windows-1252'
        ).'<br>';
    }
}

Possible output:

id
name
class
mark

columnCount() vs rowCount() Top ↑

The two PDOStatement methods answer different questions:

For example:

$stmt=$dbo->query(
    "SELECT id,name,class
     FROM student"
);

echo $stmt->columnCount();

This reports:

3

It does not tell us how many student rows were returned.

For a portable SELECT record count, use SQL COUNT() with fetchColumn():

$total=(int)$dbo->query(
    "SELECT COUNT(*) FROM student"
)->fetchColumn();

echo $total;

columnCount() with INSERT, UPDATE and DELETE Top ↑

columnCount() is primarily meaningful for statements that produce a result set.

An ordinary INSERT, UPDATE or DELETE does not return a normal column result set, so columnCount() is not the method to use for those operations.

For example, after an UPDATE:

$stmt=$dbo->prepare(
    "UPDATE student
     SET mark=:mark
     WHERE id=:id"
);

$stmt->execute([
    ':mark' => 80,
    ':id' => 1
]);

echo 'Rows affected: '.
    $stmt->rowCount();

Here rowCount(), not columnCount(), is the relevant method.

Common columnCount() Mistakes Top ↑

Calling it PDO::columnCount() Top ↑

columnCount() belongs to the PDOStatement object, not directly to the PDO connection.

$stmt=$dbo->query($sql);
$count=$stmt->columnCount();

Using columnCount() to Count Records Top ↑

It counts result columns, not rows. Use SQL COUNT(*) when the purpose is to count SELECT records.

Assuming It Counts Every Column in the Table Top ↑

It reports columns returned by the statement. A query selecting only id and name returns a column count of two even if the table contains many more columns.

Using SELECT * Only to Discover the Column Count Top ↑

If the application already knows which fields it needs, select those fields explicitly rather than requesting unnecessary data.

Assuming getColumnMeta() Is Fully Portable Top ↑

Metadata support varies between PDO drivers. Check the return value before using metadata fields.

Related PHP PDO Tutorials Top ↑

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: