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

Fetching rows, finding IDs and moving result set pointers: implementing the “fetch_array()” and “data_seek()” methods - 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

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.



 
 
>>> 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: