<?php
/***********************************************************************
* FILENAME : FindNames.php
*
* DESCRIPTION :
* Simple PHP PDO ODBC example to find and count the number
* of records in a table where the first letter of the name
* matches a given letter.
*
* ODBC USAGE :
* Creates an associative array of letters against which names
* are to be matched.
* Connects to Data Source using a Data Source Name
* Prepares a SQL statement which performs 2 SELECTs. One takes
* an array value to match names against and the second returns
* the SQL Server global variable '@@ROWCOUNT'. This causes two
* rowsets to be returned (in associative arrays), one with the
* records found (if any) and one with the count of records
* returned by the first.
* For each name to match against
* Calls $stmt->execute()
* Loops using $stmt->fetchAll() and $stmt->nextRowset()
* output array key values in results returned
* Closes statement and database connection
*
*/
// Datasource name
$dsn ="odbc:DATASOURCE";
// Random list of strings for name searches.
// Could be full alphabet
$firstNames = array(
array("fn"=>"a%"),
array("fn"=>"e%"),
array("fn"=>"i%"),
array("fn"=>"o%"),
array("fn"=>"u%")
);
try {
// Connect to the data source
$dbh = new PDO($dsn);
// Prepare the statement with 2 Select statements.
// Note the first SELECT uses the array key 'fn' as the select criteria
// in the PDO form ':fn'.
$stmt = $dbh->prepare('SELECT * FROM TestTBL1
WHERE FirstName LIKE :fn
ORDER BY FirstName;SELECT @@ROWCOUNT as Rows');
// Execute the prepared statement for each name in the array
foreach($firstNames as $name) {
printf ("\nNames starting with '%.1s'", $name['fn']);
$stmt->execute($name);
// Print results for each rowset returned. We will get 1 rowset for
// each SELECT. The first will be any records found (which may be empty)
// and the second with be a count of the records returned by the first
// (which may be zero). Note this relies on SQL Server returning this
// value after a SELECT.
do {
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach($result as $rst)
{
// These keys will exists if this rowset is a record
if (array_key_exists('PersonID', $rst)) printf ("\n %d", $rst['PersonID']);
if (array_key_exists('FirstName', $rst)) printf ("\n %.20s", $rst['FirstName']);
if (array_key_exists('LastName', $rst)) printf ("\n %.20s", $rst['LastName']);
if (array_key_exists('Address', $rst)) printf ("\n %.20s", $rst['Address']);
if (array_key_exists('City', $rst)) printf ("\n %.20s",$rst['City']);
// This key will exist if this rowset is the record count set
if (array_key_exists('Rows', $rst)) printf ("\n Records Found : %d\n",$rst['Rows']);
}
} while ($stmt->nextRowset());
}
// Close statement and database connection
$stmt = NULL;
$dbh = NULL;
}
catch(PDOException $e) {
echo $e->getMessage();
}
?>
Further information
