Counting fields and retrieving rows in a faster way: using the “fetch_assoc()” method and the “field_count” property - MySQL

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.