Getting information about table fields: using the “fetch_field()”, field_seek()” methods and the “current_field” property - MySQL

As I explained before, the “mysqli” extension comes with a fairly comprehensive set of methods for obtaining specific information about table fields, which can be used so simply that it’ll take you only a few lines of code to implement them.

I’ll start showing you how to use the “fetch_field()” method, tasked with obtaining information about fields of a particular database table. Here’s the corresponding example:

// example of 'fetch_field()' method
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
if($result=$mysqli->query("SELECT * FROM customers")){
    // display information about fields
    while($fieldData=$result->fetch_field()){
        echo 'Name of field : '.$fieldData->name.'<br />';
        echo 'Name of table : '.$fieldData->table.'<br />';
        echo 'Length of field : '.$fieldData->length.'<br />';
        echo 'Type of field : '.$fieldData->type.'<br />';
    }
    $result->close();
}
// close connection
$mysqli->close();

As you can see, the “fetch_field()” method is called after fetching a result set, and returns an object that exposes some useful properties, such as “name,” “table,” “length” and so forth, which can be used for obtaining information about the fields that compose a particular database table. In this specific case, since the “CUSTOMERS” table was defined with the “Id,” “name” and “email” fields, this is the data displayed by the previous script:

Name of field : id
Name of table : customers
Length of field : 10
Type of field : 3
Name of field : name
Name of table : customers
Length of field : 50
Type of field : 254
Name of field : email
Name of table : customers
Length of field : 50
Type of field : 254

That was pretty simple, right? By using the properties that I explained before, it is possible to get a good idea of how the selected table was originally defined. In addition, there are other properties that you can learn and use when implementing this method, but since in this article I showed you the most useful ones, feel free to check the PHP for additional information.

Right, when it comes to getting information about table fields, the “mysqli” library has plenty of methods and properties that you can use with only minor hassles. Take a look at the “field_seek()” method, which can be utilized for locating a particular column and obtaining detailed information about it. Here’s how to use it:

// example of 'field_seek()' method
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
if($result=$mysqli->query("SELECT * FROM customers")){
    // display information about third column
    $result->field_seek(2);
    $fieldData=$result->fetch_field();
    echo 'Name of field : '.$fieldData->name.'<br />';
    echo 'Name of table : '.$fieldData->table.'<br />';
    echo 'Length of field : '.$fieldData->length.'<br />';
    echo 'Type of field : '.$fieldData->type.'<br />';
    $result->close();
}
// close connection
$mysqli->close();

In this case, the above example demonstrates how to navigate to the third field (field_seek(2)), and display data about that column in particular. According to this concept, the output of the prior script looks like this:

Name of field : email
Name of table : customers
Length of field : 50
Type of field : 254

Didn’t I tell you that getting information about table fields was really easy? Now, before I get too excited, let me demonstrate the use of one more property related to retrieving information about table fields. I’m speaking of the “current_field” property, and it can be implemented as follows:

// example of 'current_field' property
$mysqli=new mysqli('host','user','password','database');
if(mysqli_connect_errno()){
    trigger_error('Error connecting to host. '.$mysqli-
>error,E_USER_ERROR);
}
if($result=$mysqli->query("SELECT * FROM customers")){
    // display information about fields
    while($fieldData=$result->fetch_field()){
        echo 'Displaying information about field: '.$result-
>current_field.'<br />';
        echo 'Name of field : '.$fieldData->name.'<br />';
        echo 'Name of table : '.$fieldData->table.'<br />';
        echo 'Length of field : '.$fieldData->length.'<br />';
        echo 'Type of field : '.$fieldData->type.'<br />';
    }
    $result->close();
}
// close connection
$mysqli->close();

The example shown above demonstrates how to use the “current_field” property, after a result set has been retrieved. In this case, I used the “fetch_field()” method inside a “while()” loop, in order to display information about the current field being processed, which results in the following output:

Displaying information about field: 1
Name of field : id
Name of table : customers
Length of field : 10
Type of field : 3
Displaying information about field: 2
Name of field : name
Name of table : customers
Length of field : 50
Type of field : 254
Displaying information about field: 3
Name of field : email
Name of table : customers
Length of field : 50
Type of field : 254

As you can see, the “current_field” property returns the number of the field being traversed by the loop, and specific information about that particular field is displayed. Although this property may not be the most useful one, it can be used in scripts that only handle primitive data about table fields.

Final thoughts

At this point, I provided you with a fairly robust set of methods and properties that you can use in conjunction with MySQL 4.1 and above, in order to perform the most common operations involved in database-driven PHP 5 applications.

As I said before, the “mysqli” extension also allows you to implement all this functionality using procedural functions. If you’re currently out of PHP object-oriented programming terrain, take a few minutes to read the PHP manual to learn more on this topic. See you in the next PHP tutorial!