Database Abstraction With PHP - The Number Game (Page 6 of 11 )
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.
Next: Preparing For The Long Haul >>
More PHP Articles
More By icarus, (c) Melonfire
/ 49 
