How to get "field names" using PHP ADOdb?

Question

I'm using PHP ADOdb and I can get the result set:

$result = &$db->Execute($query);

How do I get the field names from that one row and loop through it?

(I'm using access database if that matters.)

Answer 1

It will depend on your fetch mode - if you setFetchMode to ADODB_FETCH_NUM (probably the default) each row contains a flat array of columns. If you setFetchMode to ADODB_FETCH_ASSOC you get an associative array where you can access each value by a key. The following is taken from ADODB documentation - http://phplens.com/lens/adodb/docs-adodb.htm#ex1

$db->SetFetchMode(ADODB_FETCH_NUM);
$rs1 = $db->Execute('select * from table');
$db->SetFetchMode(ADODB_FETCH_ASSOC);
$rs2 = $db->Execute('select * from table');

print_r($rs1->fields); # shows array([0]=>'v0',[1] =>'v1')
print_r($rs2->fields); # shows array(['col1']=>'v0',['col2'] =>'v1')

To loop through a set of results:

$result = &$db->Execute($query);
foreach ($result as $row) {
    print_r($row);
}

Answer 2

This is a function I use to return a field array - I've stripped out some extra stuff that, for example, allows it to work with other DBs than MySQL.

function getFieldNames($strTable, $cn) {

    $aRet = array();

    # Get Field Names:
    $lngCountFields = 0;

    $strSQL = "SELECT * FROM $strTable LIMIT 1;";

    $rs = $cn->Execute($strSQL)
            or die("Error in query: \n$strSQL\n"  . $cn->ErrorMsg());
    if (!$rs->EOF) {
        for ($i = 0; $i < $rs->FieldCount(); $i++) {
            $fld = $rs->FetchField($i);
            $aRet[$lngCountFields] = $fld->name;
            $lngCountFields++;
        }
    }

    $rs->Close();
    $rs = null;

    return $aRet;

}

Edit: just to point out that, as I say, I've stripped out some extra stuff, and the EOF check is therefore no longer necessary in the above, reduced version.

Answer 3

I initally tried to use MetaColumnNames, but it gave differing results in VisualPHPUnit and actual site, while running from the same server, so eventually I ended up doing something like this:

$sql = "select column_name, column_key,  column_default, data_type, table_name, table_schema from information_schema.columns";
$sql .= ' where table_name="'.$table.'" and table_schema="'.$database_name.'"';
$result = $conn->Execute($sql);
while($row = $result->fetchRow()) {
    $out[] = strToUpper($row['column_name']);
}   

I think it should work with mysql, mssql and postgres. The benefit of doing it like this, is that you can get the column names, even if a query from a table returns an empty set.

Answer 4

The OP is asking for a list of fieldnames that would result of executing an sql statement stored in $query.

Using $result->fetchRow(), even with fetch mode set to associative, will return nothing if no records match the criteria set by $query. The $result->fields array would also be empty and would give no information for getting the fieldnames list.

Actually, we don't know what's inside the $query statement. Besides, setting limit to 1 may not compatible with all database drivers supported by PHP ADOdb.

Answer by Radon8472 is the right one, but the correct code could be:

$result = $db->Execute($query);
// FieldTypesArray - an array of ADOFieldObject Objects 
// read from $result, even for empty sets or when 
// using * as field list.
    
$colInfo = [];
if (is_subclass_of($result, 'ADORecordSet')){
    foreach ($result->FieldTypesArray() as $info) {
        $colInfo[] = $info->name;
    }
}

I have the habit of checking the class name of $result, for as PHP ADOdb will return false if execution fails.

Answer 5

Small improvement to the solution posted by @thetaiko.

If you are ONLY needing the field names, append LIMIT 1 to the end of your select statement (as shown below). This will tell the server to send you a single row with column names, rather than sending you the entire table.

SELECT * FROM table LIMIT 1;

I'm working with a table that contains 9.1M records, so this minor change speeds up the query significantly!

Answer 6

If you need the Coloumn names even for empty tables or for joins about multiple tables use this:

$db->Execute("SELECT .......");
// FieldTypesArray - Reads ColoumnInfo from Result, even for Joins
$colInfo = $res->FieldTypesArray(); 

$colNames = array();
foreach($colInfo as $info) $colNames[] = $info->name;