<?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 data base 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 data base connection
    $stmt = NULL;
    $dbh = NULL;
}

catch(PDOException $e) {
    echo $e->getMessage();
}
?>

See Also


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.