PHP / MySQL - How to determine field names from a given query result?

Question

Given the set of results, how can I determine 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); } 

In this example, field names are returned, 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. In particular, if I write a function and do not know what request 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 the "orgname" property.

The "classic" equivalent MySQL function does not report the original column names.

Answer 2

Short answer: you do not.

Long answer: as soon as the dataset pulls out MySQL and is sent back to PHP, the only information that PHP now has is columns or aliases, if you used them. There is no way to look at the result set and determine what the names of the source columns were. To get this information, you need to switch to another DB driver, such as mysqli.

Answer 3

Your question does not make sense. What are you going to do if you get a derived column ie

select column_a + column_b as order_total from orders;

You say 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 from the internet.

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

Answer 4

I am not 100% sure, but I would say: there is no way.

MySQL returns you a result set, nothing more. It does not return a select statement or any details about this.

Thus, you cannot get the original field names, because the server will provide you with the information you requested: aliases.

Answer 5

If you don't mind the second query (and use MySQL 5 or more), you can query information_schema for names. See the MySQL Reference for more details:

SHOW COLUMNS FROM tbl_name;

Answer 6

if you have access to the query string, you can try a regular expression to parse it. I am not a regular expression wizard, but you can cut the string by looking at the text between "select" and "from" and then grab all the field names as

  field FieldAlias 

or

  field as FieldAlias 

Answer 7

If you are trying to write some kind of functionality to find out which fields are retrieved for processing updates, the only way to do this correctly is to provide the SQL-less interface for the code above and manage all the generation SQL itself. This is called the data abstraction layer.