Home arrow PHP arrow Page 6 - Database Abstraction With PHP

The Number Game - PHP

One of the nicest things about Perl - the DBI module - finallymakes an appearance in PHP. Take a look at the PEAR database abstractionlayer, by far one of the coolest PHP widgets out there.

TABLE OF CONTENTS:
  1. Database Abstraction With PHP
  2. Alphabet Soup
  3. Sultans Of Swing
  4. Independence Day
  5. Different Strokes
  6. The Number Game
  7. Preparing For The Long Haul
  8. Commitment Issues
  9. No News Is Good News
  10. Catch Me If You Can
  11. Once Again, The Headlines
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 64
February 13, 2002

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
The numRows() and numCols() methods can be used to obtain the number of rows and columns in the returned resultset respectively:

<?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the DB abstraction layer include("DB.php"); // connect to the database $dbh = DB::connect("mysql://john:doe@localhost/db287"); // execute query $query = "SELECT * FROM cds"; $result = $dbh->query($query); // print number of rows and columns in resultset echo "Query returned " . $result->numRows() . " rows of " . $result->numCols() . " columns each"; // close database connection $dbh->disconnect(); ?>
The LimitQuery() method can be used to restrict the number of rows retrieved:

<?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the DB abstraction layer include("DB.php"); // set some variables for limited query $start = 2; $num = 4; // connect to the database $dbh = DB::connect("mysql://john:doe@localhost/db287"); // execute limited query $query = "SELECT track FROM tracks"; $result = $dbh->LimitQuery($query, $start, $num); echo "[Retrieving rows $start through " . ($start+$num) . " of resultset]\n"; // iterate through rows and print column data // in the form TITLE - ARTIST while($row = $result->fetchRow()) { echo "$row[0]\n"; } // close database connection $dbh->disconnect(); ?>
In this case, the LimitQuery() method can be used to obtain a subset of the complete resultset retrieved from the database. The first argument to the method is the query to execute, the second is the row offset from which to begin, and the third is the number of rows required.

If you're feeling voyeuristic, the tableInfo() method can be used to take a quick peek at the structure of the table(s) returned by your query. Consider the following example,

<?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the DB abstraction layer include("DB.php"); // connect to the database $dbh = DB::connect("mysql://john:doe@localhost/db287"); // execute query $query = "SELECT title, artist, track FROM cds, tracks WHERE cds.id = tracks.cd"; $result = $dbh->query($query); // get info on structure of tables used in query // this is returned as an array of arrays // dump it with print_r()! print_r($result->tableInfo()); // close database connection $dbh->disconnect(); ?> And then take a look at the output of the tableInfo() command, as seen through the print_r() function: Array ( [0] => Array ( [table] => cds [name] => title [type] => string [len] => 255 [flags] => not_null ) [1] => Array ( [table] => cds [name] => artist [type] => string [len] => 255 [flags] => not_null ) [2] => Array ( [table] => tracks [name] => track [type] => string [len] => 255 [flags] => not_null ) )
As you can see, information on the table structure - the field names, data types, flags et al - is returned by tableInfo() as an array. Every element of this array corresponds to a column in the resultset, and is itself structured as an associative array. Note that this method only works if your query actually returns a valid resultset - so you can't use it with INSERT or UPDATE queries. Finally, the free() method is used to free the resources associated with a particular resultset.

 <?php // uncomment this to see 
plaintext output in your browser // header("Content-Type: text/plain"); // 
include the DB abstraction layer include("DB.php"); // connect to the database 
$dbh DB::connect("mysql://john:doe@localhost/db287"); // execute query $query 
"SELECT * FROM cds"$result $dbh->query($query); // iterate through rows 
and print column data // in the form TITLE - ARTIST while($row = 
$result->fetchRow()) { echo "$row[1] - $row[2]n"; } // free resultset 
$result->free(); // close database connection $dbh->disconnect(); 



Just out of curiosity, look what happens if you use free() in the wrong place.

<?php // uncomment this to see plaintext output in your browser // header("Content-Type: text/plain"); // include the DB abstraction layer include("DB.php"); // connect to the database $dbh = DB::connect("mysql://john:doe@localhost/db287"); // execute query $query = "SELECT * FROM cds"; $result = $dbh->query($query); // iterate through rows and print column data // in the form TITLE - ARTIST while($row = $result->fetchRow()) { echo "$row[1] - $row[2]\n"; // free resultset $result->free(); } // close database connection $dbh->disconnect(); ?>
In this case, the "while" loop will execute only once, since the resultset gets free()d on the first iteration of the loop. Consequently, only one record will be displayed as output.

This is kinda pointless - after all, if you only wanted to display a single record, you wouldn't need a "while" loop in the first place - but interesting to try out; it serves as both a warning to newbies and a source of amusement to more experienced geeks.

 
 
>>> More PHP Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: