PDO_ColumnOrdinal() - Return the ordinal position of a column (by name)

Sometimes, you need to know what the ordinal position of a column is. This is useful for PDO functions that require an ordinal column number but you'd prefer to use a column name so you don't break your code just by altering the database. Here's an example of just such an instance:

Code
// Fetch column values.
$row = $q->fetchAll(PDO::FETCH_COLUMN, $ReturnColumn);


In this case $ReturnColumn must be a column ordinal, and CANNOT be a column name.

This function was designed and tested with MySQL. It may or may not work with other PDO database drivers that don't support the SQL syntax used.

Here's the function that solves the problem...

Code
function PDO_ColumnOrdinal($hDB, $TableName, $ColumnName) {
    /*
    Purpose
        Used to get the ordinal position of the specified table/column.
    Parameters
        hDB (resource) = A PDO database handle to an open database.
        TableName (string) = The name of the table we're working with.
        ColumnName (string) = The name of the column we're working with.
    Return (int, false)
        Returns the zero-based ordinal position of the specified column (ColumnName) in the specified table (TableName).
        Returns false if the specified table or column does not exist, or if a database error occurs.
    Version History
        1.0 2012.10.09 Tested by Russ Tanner on PHP 5.3.8.
    */

    // Prepare the SQL statement.
    $q = $hDB->prepare('
        SELECT ORDINAL_POSITION
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE table_name = ? AND COLUMN_NAME = ?
    ');
    // Execute the SQL statment. Error Trap: Database Error. Return false.
    if (! $q->execute(array($TableName, $ColumnName))) return false;

    // Fetch the first - and only - matching record.
    $x = $q->fetch(PDO::FETCH_ASSOC);

    // Make sure the cursor is closed (necessary after fetch() with MySQL).
    $q->closeCursor();

    // Error Trap: The specified table or column does not exist, or an error occurred. Return false.
    if ($x === false) return false;

    // Return the ordinal index of the specified table/column.
    // The SELECT statement returns 1-based values. Subtract 1 so it's zero-based.
    return $x['ORDINAL_POSITION'] - 1;

}

The Captian
Today they call you "crazy". Tomorrow they call you "ahead of your time."
Global Skywatch Learn about Chemtrails - You're breathing them now!
OnlyTheBestHerbs.com World-class supplements
Mercury Talk Why you are sick.
OneUp Domains Domains, Hosting, Email
1-800-358-4278 (U.S. & Canada)