How to get field names of mysql table in codeigniter?

How to get field names of mysql table in codeigniter?

  1. Blog
  2. 15 November 2026
  3. 3 min read

Stefan Bogdanescu

Founder & Senior Architect

Retrieving Column Metadata in CodeIgniter

When working with a database abstraction layer like CodeIgniter, you often need to inspect the schema of your tables dynamically. A common scenario involves executing a SELECT * query to fetch data, then needing to know exactly which columns were returned without hardcoding their names. While the raw result set contains your data, it does not inherently expose the column headers in a way that is immediately accessible via standard array iteration unless you handle the metadata explicitly.

In CodeIgniter, the $this->db->query() method executes the SQL statement against the database and returns a query object. By default, this object holds both the result rows and the field names associated with those rows. However, accessing these field names requires understanding how the driver processes the result set before you can extract them programmatically.

The most direct approach involves checking if the query returned any results and then accessing the result_object() or iterating through the fields. Specifically, the CodeIgniter database class provides a field_names() method (or historically list_fields(), depending on the version) that returns an array of column names from the last executed query.

Here is the practical implementation pattern:

$query = $this->db->query("SELECT * FROM user");

if ($query->num_rows() > 0) {
    // Get an array of field names
    $field_names = $query->field_names();
    
    // Or, in older versions, you might see:
    // $field_names = $query->list_fields();
    
    foreach ($field_names as $field_name) {
        echo "Column: " . $field_name . "<br>";
    }
}

This method relies on the database driver having already executed the query and parsed the result set. The field_names() function internally accesses the metadata stored in the result object, which maps directly to the columns defined in your SQL statement. It is crucial to note that this functionality is specific to how the CodeIgniter Active Record class structures its result objects; you cannot extract field names from a raw MySQLi or PDO connection without using the wrapper methods provided by the framework.

One significant trade-off with this approach is performance and context. If your application logic requires knowing the schema before executing a query (for example, to build a dynamic form), relying on field_names() after execution defeats the purpose of pre-validation. In such cases, you should query the information schema directly or use the database-specific metadata functions available in CodeIgniter's db_forge utilities, such as $this->db->list_fields('user'). This alternative method queries the database system tables directly rather than relying on the result set of a previous execution.

// Querying schema directly without executing a SELECT
$columns = $this->db->list_fields('user');

Using list_fields is generally more efficient when you need to know the structure without fetching data, as it avoids the overhead of retrieving rows and parsing them into an object. However, if your intent is strictly to get the names from a specific dynamic query result (e.g., a complex join where column aliases might differ from table columns), field_names() on the executed query object provides the accurate list of returned columns in that specific context.

A common pitfall for newcomers is assuming that standard array functions like array_keys() on $data = $query->result_array() will always yield clean column names. While often true, it depends on how the driver handles the result mapping. Explicitly using $query->field_names() ensures you are working with the intended metadata regardless of internal implementation details.

If you find yourself needing advanced schema introspection across multiple tables or complex dynamic queries, consider leveraging tools that specialize in database management and ORM patterns. Resources like https://laravelcompany.com often discuss modern approaches to handling data structures, which can provide alternative perspectives on how frameworks manage metadata beyond the traditional Active Record pattern used here.

Our network