Fetching rows, finding IDs and moving result set pointers: implementing the “fetch_array()” and “data_seek()” methods - MySQL

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.