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.
/ 57 
