Manuel Pear

Niveau superieur
Sommaire

Results

Results -- Obtaining data from query results

Description

Fetching Individual Rows From Query Results

The MDB2_Result_Common object provides two methods for fetching data from rows of a result set: fetchOne(), fetchRow(), fetchCol() and fetchAll().

fetchRow() and fetchOne() read an entire row or a single field from a column respectively. The result pointer gets moved to the next row each time these methods are called. NULL is returned when the end of the result set is reached.

fetchAll() and fetchCol() read all rows in the result set and therefore move the result pointer to the end. While fetchAll() reads the entire row data, fetchCol() only reads a single column.

MDB2_Error is returned if an error is encountered.

Exemple 39-1. Fetching a result set

  1. <?php
  2. // Create a valid MDB2 object named $mdb2
  3. // at the beginning of your program...
  4. require_once 'MDB2.php';  
  5.  
  6. $mdb2 =& MDB2::connect('pgsql://usr:pw@localhost/dbnam');  
  7. if (PEAR::isError($mdb2)) { 
  8.    die($mdb2->getMessage());  
  9. }  
  10.  
  11. // Proceed with getting some data...
  12. $res =& $mdb2->query('SELECT * FROM mytable');  
  13.  
  14. // Get each row of data on each iteration until
  15. // there are no more rows
  16. while (($row = $res->fetchRow())) { 
  17.    // Assuming MDB2's default fetchmode is MDB2_FETCHMODE_ORDERED
  18.    echo $row[0] . "\n";  
  19. }  
  20.  
  21. // while (($one = $res->fetchOne())) {
  22. //    echo $one . "\n";
  23. // }
  24.  
  25.  
  26. ?> 

Formats of Fetched Rows

The data from the row of a query result can be placed into one of three constructs: an ordered array (with column numbers as keys), an associative array (with column names as keys) or an object (with column names as properties).

MDB2_FETCHMODE_ORDERED (default)

Array
(
    [0] => 28
    [1] => hi
)

MDB2_FETCHMODE_ASSOC

Array
(
    [a] => 28
    [b] => hi
)

MDB2_FETCHMODE_OBJECT

stdClass Object
(
    [a] => 28
    [b] => hi
)

NOTE: When a query contains the same column name more than once (such as when joining tables which have duplicate column names) and the fetch mode is MDB2_FETCHMODE_ASSOC or MDB2_FETCHMODE_OBJECT, the data from the last column with a given name will be the one returned. There are two immediate options to work around this issue:

Use aliases in your query, for example People.Name AS PersonName
Change the fetch mode to MDB2_FETCHMODE_ORDERED

TIP: If you are running into this issue, it likely indicates poor planning of the database schema. Either data is needlessly being duplicated or the same names are being used for different kinds of data.

How to Set Formats

You can set the fetch mode each time you call a fetch method and/or you can set the default fetch mode for the whole MDB2 instance by using the setFetchMode() method.

Exemple 39-2. Determining fetch mode per call

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT * FROM users');  
  4.  
  5. while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) { 
  6.    echo $row['id'] . "\n";  
  7. }  
  8.  
  9. ?> 

Exemple 39-3. Changing default fetch mode

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);  
  4.  
  5. $res =& $mdb2->query('SELECT * FROM users');  
  6.  
  7. while ($row = $res->fetchRow()) { 
  8.    echo $row['id'] . "\n";  
  9. }  
  10.  
  11. ?> 

Fetch Rows by Number

The PEAR MDB2 fetch system also supports an extra parameter to the fetch statement. So you can fetch rows from a result by number. This is especially helpful if you only want to show sets of an entire result (for example in building paginated HTML lists), fetch rows in an special order, etc.

Exemple 39-4. Fetching by number

  1. <?php
  2. // Once you have a valid MDB2_Result object named $res...
  3.  
  4. // the row to start fetching
  5. $from = 50;  
  6.  
  7. // how many results per page
  8. $resPage = 10;  
  9.  
  10. // the last row to fetch for this page
  11. $to = $from + $resPage;  
  12.  
  13. foreach (range($from, $to) as $rowNum) { 
  14.    if (!($row = $res->fetchRow(MDB2_FETCHMODE_ORDERED, $rowNum))) { 
  15.       break; 
  16.    } 
  17.    echo $row[0] . "\n";  
  18. }  
  19.  
  20. ?> 

Getting Entire Result Sets

The MDB2_Result_Common object provides several methods to read entire results sets: fetchCol() and fetchAll().

Freeing Result Sets

Once you finish using a result set, if your script continues for a while, it's a good idea to save memory by freeing the result set via Use free().

Exemple 39-5. Freeing

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT name, address FROM clients');  
  4. while ($row = $res->fetchRow(MDB2_FETCHMODE_ASSOC)) { 
  5.    echo $row['name'] . ', ' . $row['address'] . "\n";  
  6. }  
  7. $res->free();  
  8.  
  9. ?> 

Getting the native result resource

If whatever data you need to read from a result set is not yet implemented in MDB2 you can get the native result resource using the getResource() method and then call the underlying PHP extension directly (though this would of course require that it is now up to you to make this sufficiently portable).

Exemple 39-6. Native result resource

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT name, address FROM clients');  
  4. $native_result = $res->getResource();  
  5.  
  6. ?> 

Getting More Information From Query Results

With MDB2 there are four ways to retrieve useful information about the query result sets themselves:

Exemple 39-7. numRows() tells how many rows are in a SELECT query result

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT * FROM phptest');  
  4. if ($mdb2->getOption('result_buffering')) { 
  5.    echo $res->numRows();  
  6. } else { 
  7.    echo 'cannot get number of rows in the result set when "result_buffering" is disabled';  
  8. }  
  9.  
  10. ?> 

Exemple 39-8. numCols() tells how many columns are in a SELECT query result

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT * FROM phptest');  
  4. echo $res->numCols();  
  5.  
  6. ?> 

Exemple 39-9. rowCount() tells which row number the internal result row pointer currently points to

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT * FROM phptest');  
  4. $res->fetchRow();  
  5. // returns 2 if the result set contains at least 2 rows
  6. echo $res->rowCount();  
  7.  
  8.  
  9. ?> 

Exemple 39-10. getColumnNames() returns an associative array with the names of the column of the result set as keys and the position inside the result set as the values

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT * FROM phptest');  
  4. print_r($res->getColumnNames());  
  5.  
  6.  
  7. ?> 

Exemple 39-11. seek() allows to seek to a specific row inside a result set. Note that seeking to previously read rows is only possible if the 'result_buffering' option is left enabled, otherwise only forward seeking is supported.

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $res =& $mdb2->query('SELECT * FROM phptest');  
  4. // Seek to the 10th row in the result set
  5. $res->seek(10);  
  6.  
  7.  
  8. ?> 

Exemple 39-12. nextResult() allows iterate over multiple results returned by a multi query.

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $multi_query = $this->db->setOption('multi_query', true);  
  4. // check if multi_query can be enabled
  5. if (!PEAR::isError($multi_query)) { 
  6.    $res =& $mdb2->query('SELECT * FROM phptest; SELECT * FROM phptest2;'); 
  7.    $data1 = $res->fetchAll(); 
  8.    // move result pointer to the next result
  9.    $res->nextResult(); 
  10.    $data2 = $res->fetchAll();  
  11. } else { 
  12.    echo 'multi_query option is not supported';  
  13. }  
  14.  
  15.  
  16. ?> 

Exemple 39-13. bindColumn() allows to bind a reference to a user variable to a specific field inside the result set. This means that when fetching the next row, this variable is automatically updated.

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $name = $address = null;  
  4. $res =& $mdb2->query('SELECT id, name, address FROM clients', array('id' => 'integer'));  
  5. $res->bindColumn('id', $id);  
  6. // provide a type for the column not included in the query() call
  7. $res->bindColumn('name', $name, 'text');  
  8. // but specifying a type is as always optional in MDB2
  9. $res->bindColumn('address', $address);  
  10. while ($res->fetchRow()) { 
  11.    echo "The address of '$name' (user id '$id') is '$address'\n";  
  12. }  
  13.  
  14. ?> 

Querying and fetching in one call

All of the fetch methods are also available in a variant that executes a query directly: queryOne(), queryRow(), queryCol() and queryAll().

Exemple 39-14.

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $data = $mdb2->queryAll('SELECT * FROM phptest');  
  4. print_r($data);  
  5.  
  6.  
  7. ?> 

Users that prefer to use prepared statements can make use of the following methods from the Extended module: getOne(), getRow(), getCol(), getAll() and getAssoc().

Exemple 39-15.

  1. <?php
  2. // Once you have a valid MDB2 object named $mdb2...
  3. $mdb2->loadModule('Extended');  
  4. $query = 'SELECT * FROM phptest WHERE id = ?';  
  5. $data = $mdb2->extended->getRow($query, null, array(1), array('integer'));  
  6. print_r($data);  
  7.  
  8.  
  9. ?> 

Data types

MDB2 supports a number of data types across all drivers. These can be set for result sets at query or prepare time or using the setResultTypes() method. You can find an overview of the supported data types and their format here.

Fetching LOBs

To retrieve a Large Object (BLOB or CLOB), you can use streams, as you were reading a file

Exemple 39-16. Fetching LOBs with streams.

  1. <?php
  2. $result =& $mdb2->query('SELECT document, picture FROM files WHERE id = 1', array('clob', 'blob'));  
  3. if (PEAR::isError($result) || !$result->valid()) { 
  4.    //uh-oh
  5. }  
  6. $row = $result->fetchRow();  
  7.  
  8. //fetch the Character LOB into the $clob_value variable
  9. $clob = $row[0];  
  10. if (!PEAR::isError($clob) && is_resource($clob)) { 
  11.    $clob_value = ''; 
  12.    //use streams
  13.    while (!feof($clob)) { 
  14.       $clob_value .= fread($clob, 8192); 
  15.    } 
  16.    $mdb2->datatype->destroyLOB($clob);  
  17. }  
  18.  
  19. //fetch the Binary LOB into the $blob_value variable
  20. $blob = $row[1];  
  21. if (!PEAR::isError($blob) && is_resource($blob)) { 
  22.    $blob_value = ''; 
  23.    while (!feof($blob)) { 
  24.       $blob_value.= fread($blob, 8192); 
  25.    } 
  26.    $mdb2->datatype->destroyLOB($blob);  
  27. }  
  28.  
  29. //free the result
  30. $result->free();  
  31.  
  32. ?> 

Checking for Errors

Don't forget to use isError() to check if your actions return a MDB2_Error object.

Niveau superieur
Sommaire

Remonter Remonter
L'éditeur javascript - CSS - Gentoo - Tutoriaux PHP - Tutoriels PHP - Bretagne - php - Moto - Kit graphique