PHP/MySQL - How do you determin the field names from a given query result?

Question

Given a result set, how can I determin the actual names of the fields specified in the query (NOT their aliases).

$query = "SELECT first AS First_Name, last AS Last_Name FROM people";
$dbResult = mysql_query($query);

$fieldCount = mysql_num_fields($dbResult);
for ($i=0; $i<$fieldCount; $i++) {    
    // Set some values
    $fieldName = mysql_field_name($dbResult, $i);
}

This example returns field names, but in this example it returns the alias "First_Name" instead of the actual field name "first".

Is it possible to get the actual field name from such a query. Particularly if I am writing a function and have no idea what query will be thrown at it.

Answer 1

If you are using MySQLi:

http://www.php.net/manual/en/mysqli-result.fetch-field.php

The field object has a "orgname" property.

The "classic" MySQL equivalent function doesn't report back the original column names.

Answer 2

I'm not 100% sure about this, but I would say: there is no way.

The MySQL gives you back the result set, nothing more. It does not return the select statement nor any details about it.

So you cannot get the original field names because the server will provide you the information you asked: alias names.

Answer 3

Short answer: you don't.

Long answer: Once the dataset is pulled by MySQL and sent back to PHP, the only information PHP now has is the columns, or aliases if you used them. There is no way to look at a result set and determine what the original column names were. You have to switch to another DB driver like mysqli to obtain this info.

Answer 4

Your question doesn't make sense. What are you going to do if you get a derived column i.e.

select column_a + column_b as order_total from orders;

are you saying you want to know that the original query was column_a + column b ??

if so, you probably need to write a query parser, or get one off the internet.

I think the implementation of that is beyond the scope of your question though :)

Answer 5

If you don't mind making a second query (and your using MySQL 5 or greater) you can ask information_schema for the names. Check out MySQL Reference for the details:

SHOW COLUMNS FROM tbl_name;

Answer 6

if you have access to the string of the query you could try a regular expression to parse it. I'm no regex master but you could chop up the string by looking at the text between 'select' and 'from' then grabbing all the field names as either

 field FieldAlias

or

 field as FieldAlias

Answer 7

If you're trying to write some functionality to let you know what fields are being fetched for handling updates - the only way to do this correctly is for it to present an SQL-less interface to the code above and manage all SQL generation itself. This is called a data abstraction layer.