Home arrow MySQL arrow Page 4 - Implementing Additional Methods with mysqli and PHP 5

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

Welcome to the third installment of the series "Using mysqli in PHP 5." Comprised of three articles, this series teaches you how to use the most important methods and properties included in the "mysqli" extension, which is bundled with PHP 5, in order to get the most out of the MySQL 4.1 database server and above.

TABLE OF CONTENTS:
  1. Implementing Additional Methods with mysqli and PHP 5
  2. Fetching rows, finding IDs and moving result set pointers: implementing the “fetch_array()” and “data_seek()” methods
  3. Counting fields and retrieving rows in a faster way: using the “fetch_assoc()” method and the “field_count” property
  4. Getting information about table fields: using the “fetch_field()”, field_seek()” methods and the “current_field” property
By: Alejandro Gervasio
Rating: starstarstarstarstar / 14
July 10, 2006

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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!



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: