Application development with PHP - DB2 Express-C: The Developer for XML, PHP, C/C++, Java, and .NET - page 131

getMember()
This method implements a database convenience method to retrieve member
information from the member table when given the member ID. It uses the
simple dbAdapter function to fetch data in a similar way to the login function.
search()
This method provides some validation and posts an SQL/XML query to get a
member's profile. It provides fuzzy search capability on first and last names.
As you can see, we needed to join on the MEMBER table and XMLDATA and use
SQL/XML function to get the search result we want. Example 3-28 shows an
example of the flexibility of the XCS. If the existing API methods are not
sufficient for your needs, you can take control of the database by writing your
own SQL queries, SQL/XML queries, or XQueries directly.
Example 3-28 Database class: search method
public function search($fname, $lname)
{
$sql = "SELECT m.xmlid, m.fname, m.lname, m.email,";
$sql .= "xmlserialize(xmlquery('\$data/member/org/text()' ";
$sql .= "passing x.data as \"data\") as varchar(120)) as
company,";
$sql .= "xmlserialize(xmlquery('\$data/member/title/text()' ";
$sql .= "passing x.data as \"data\") as varchar(120)) as title ";
$sql .= "from db2admin.member m, db2admin.xmldata x where ";
if ($fname && $lname) {
$param = array();
$sql .= "(upper(m.fname) like ? or upper(m.lname) like ?)
AND ";
$param[]= strtoupper("%$fname%");
$param[]= strtoupper("%$lname%");
} else if ($fname && !$lname) {
$param = array();
$sql .= "(upper(m.fname) like ?) AND ";
$param[]= strtoupper("%$fname%");
} else if (!$fname && $lname) {
$param = array();
$sql .= "(upper(m.lname) like ?) AND ";
$param[]= strtoupper("%$lname%");
}
$sql .= "m.xmlid = x.id";
if ($param && $result = $this → _db → fetchAssoc($sql,
$param)) {
return $result;
}