Implementing Additional Methods with mysqli and PHP 5

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.

Introduction

If you’ve been reading up my two previous articles, then quite probably you’ll know how to utilize the mysqli’s core methods for connecting to MySQL, as well as performing many other useful tasks, such as running single and multiple queries, preparing SQL statements, and using the COMMIT and ROLLBACK features found in MySQL 4.1 and up. What’s more, you’ll surely recall that this extension allows you to handle one or more result sets via an object-oriented approach, which is very convenient if you’re particularly inclined to work with object-based applications.

Additionally, I covered, with numerous hands-on examples, other combinations of handy methods and properties, in order to show you how to perform some common MySQL-related operations. In this case obviously I’m talking about counting the number of rows affected after running a specific query, or determining the number of rows returned by a result set, among others. Of course, one of the best points regarding the use of the “mysql” library rests on how easily it allows you to accomplish all the things (and more) that I mentioned before by using an object-oriented approach, without the need to define custom classes. Once the library is available to your system, you can go ahead and start writing down object-based code.

Well, if I stand behind the previous paragraphs, I must say that the “mysqli” extension offers an impressive set of features, which can be implemented right from the very beginning. However, and speaking of its cool features, in the next few lines, I’m going to show you a few more. Over the course of this last tutorial, I’ll be covering some other methods and properties, mainly aimed at finding the IDs after inserting new rows, and obtaining information about specific table fields.

Now, be patient and start reading the last chapter of this story. Let’s do it together!

{mospagebreak title=Fetching rows, finding IDs and moving result set pointers: implementing the “fetch_array()” and “data_seek()” methods}

The first method that I plan to show you in this article is “fetch_array().” As its name clearly suggests, this method is very similar to the “mysql_fetch_array()” function available within the MySQL library. It essentially performs the same task of retrieving the rows from a result set as an array structure.

Of course, this method also allows you to fetch database rows either as a numerically-indexed or associative array, or a combination of both. To understand how this particular method is implemented, please examine the example listed below:

// example of fetch_array() method
$mysqli=new mysqli(‘host’,’user’,’password’,’database’);
if(mysqli_connect_errno()){
    trigger_error(‘Error connecting to host. ‘.$mysqli-
>error,E_USER_ERROR);
}
// display rows as numeric arrays
if($result=$mysqli->query(“SELECT * FROM customers”)){
    while($row=$result->fetch_array(MYSQLI_NUM)){
        echo ‘ID:’.$row[0].’ Name: ‘.$row[1].’ Email: ‘.$row
[2].'<br />';
    }
    $result->close();
}
// display rows as associative arrays
if($result=$mysqli->query(“SELECT * FROM customers”)){
    while($row=$result->fetch_array(MYSQLI_ASSOC)){
        echo ‘ID:’.$row['id'].’ Name: ‘.$row['name'].’ Email:
‘.$row['email'].'<br />';
    }
    $result->close();
}
// display rows as associative and numeric arrays
if($result=$mysqli->query(“SELECT * FROM customers”)){
    while($row=$result->fetch_array(MYSQLI_BOTH)){
        echo ‘ID:’.$row[0].’ Name: ‘.$row['name'].’ Email: ‘.$row
['email'].'<br />';
    }
    $result->close();
}
$mysqli->close();

As you can see, the above script uses the three constants “MYSQLI_ASSOC,” “MYSQLI_NUM” and “MYSQLI_BOTH” respectively, in order to fetch rows from a given result set. In the first case, the rows from the sample “CUSTOMERS” database table are retrieved as a numerically-indexed array, while the second and third cases show how to fetch records by using an associative array and a combination of both modes.

Assuming that the “CUSTOMERS” database table was initially populated with the following data:

1 customer1 email1@domain.com
2 customer2 email2@domain.com
3 customer3 email3@domain.com

Then the previous script will display the following results:

ID:1 Name: customer1 Email: email1@domain.com
ID:2 Name: customer2 Email: email2@domain.com
ID:3 Name: customer3 Email: email3@domain.com

ID:1 Name: customer1 Email: email1@domain.com
ID:2 Name: customer2 Email: email2@domain.com
ID:3 Name: customer2 Email: email3@domain.com

ID:1 Name: customer1 Email: email1@domain.com
ID:2 Name: customer2 Email: email2@domain.com
ID:3 Name: customer3 Email: email3@domain.com

As shown above, all the cases output the same results; however, the first two options are slightly faster since they only return one array at a time, while the third one retrieves two arrays.

In fact, if you’ve been using the “mysql_fetch_array()” function, all these methods should be easy to understand, so let’s move forward and see another useful –- and eventually familiar — method. Here, I’m referencing the “data_seek()” method, and it can be used as follows:

// example of ‘data_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”)){
    // locate result set pointer at row 1
    $result->data_seek(1);
    // display row data
    $row=$result->fetch_row();
    echo ‘Row 1 contains the following information:<br />ID:
‘.$row[0].’ Name: ‘.$row[1].’ Email: ‘.$row[2];
    $result->close();
}
// close connection
$mysqli->close();

Possibly you’ll agree with me that the previous method is truly simple, since it resembles the functionality of the corresponding “mysql_data_seek()” function. In short, all that this method does is move the internal pointer of a given result set to a specific position. The above example shows how to locate the pointer at “row 1” of the returned result set, and displays the following information:

Row 1 contains the following information:
ID: 2 Name: customer2 Email: email2@domain.com

Okay, hopefully you understood how the two previous method do their business, so do you want to know how to find the IDs of a row after performing an “INSERT” command? That’s really simple with the “insert_id” property, which works very similar to the “mysql_insert_id()” function. Have a look at the following example:

// example of insert_id property
$mysqli=new mysqli(‘host’,’user’,’password’,’database’);
if(mysqli_connect_errno()){
    trigger_error(‘Error connecting to host. ‘.$mysqli-
>error,E_USER_ERROR);
}
// run query
$mysqli->query(“INSERT INTO CUSTOMERS (id,name,email) VALUES
(NULL,’customer4′,’customer4@domain.com’)”);
echo ‘ID of new inserted row is: ‘.$mysqli->insert_id;
// close connection
$mysqli->close();

In simple terms, the above code sample inserts a new row into the “CUSTOMERS” table and then displays its ID by using the corresponding “insert_id” property. The final result is something similar to this:

ID of new inserted row is: 4

Well, now that you know how to use the “fetch_array()” and “data_seek()” methods, along with the “insert_id” property, it’s time to leap forward and continue exploring other handy methods.

In the next  section I’ll explain a faster method for fetching rows from a result set, as well as how to determine the respective number of fields of a database table. If you want to learn how this will be done, please keep reading.

{mospagebreak title=Counting fields and retrieving rows in a faster way: using the “fetch_assoc()” method and the “field_count” property}

In the previous section, I explained how to use the “fetch_array()” method, in order to fetch rows from a specific result set by using the “MYSQLI_ASSOC” and “MYSQLI_NUM” constants. However, as you may have guessed, the “mysqli” extension also exposes the “fetch_assoc()” method. This method behaves nearly identical to the popular “mysql_fetch_assoc()” function, and can be used for retrieving rows from a result set directly as an associative array. The example below demonstrates how to use the method in question:

// example of ‘fetch_assoc()’ method
$mysqli=new mysqli(‘host’,’user’,’password’,’database’);
if(mysqli_connect_errno()){
    trigger_error(‘Error connecting to host. ‘.$mysqli-
>error,E_USER_ERROR);
}
$sql=”SELECT * FROM customers”;
if($result=$mysqli->query($sql)){
   // fetch associative array
   while($row=$result->fetch_assoc()){
       echo ‘ID: ‘.$row['id'].’ Name: ‘.$row['name'].’ Email:
‘.$row['email'].'<br />';
   }
   // close result set
   $result->close();
}
// close connection
$mysqli->close();

After executing the above script, the corresponding output will be the following:

ID:1 Name: customer1 Email: email1@domain.com
ID:2 Name: customer2 Email: email2@domain.com
ID:3 Name: customer3 Email: email3@domain.com

All right, I must admit that the above example is pretty simple, thus let me teach you how to use some helpful methods and properties that return information about fields of database tables.

To begin with, there’s the “field_count” property, which can be used to determine the number of fields returned by a result set. Here’s an example that shows how to use this property; have a look at the source code:

// example of ‘field_count’ 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”)){
    // count number of fields
    echo ‘Number of fields in result set: ‘.$result->field_count;
    $result->close();
}
// close connection
$mysqli->close();

The example shown above illustrates how to determine the number of fields returned by a result set by using the pertinent “field_count” property that you saw before. As you’ll realize, using this property is a no-brainer process, but at the same time it opens up the door for exploring other methods and properties focused specifically on getting information about table fields.

In the next few lines, I’ll pay strong attention to some of these methods, therefore click on the link below and continue reading.

{mospagebreak title=Getting information about table fields: using the “fetch_field()”, field_seek()” methods and the “current_field” property}

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!

[gp-comments width="770" linklove="off" ]

chat