Tom Muck

Alpha Dog Blues Band
Home page
All articles
All Extensions | Extension News | Extension FAQs | Customer Login
Books authored or co-authored by Tom Muck
Extensions, books, and other products | Customer Login
Your current cart contents
Tom-Muck.com Blog | CMXTraneous Blog | Flash Remoting Blog
About the site

Blog

Tom Muck's BlogTom Muck's Blog

News and Views

Query of query substitute for PHP

Thursday, November 18, 2004 8:45:41 AM

I go back and forth between ColdFusion and PHP quite a bit. One of the things I love about ColdFusion is the Query of query -- the ability to query the database, then drill down further in your recordset for a subset of the original query. This has many uses, and is very fast because it does not make a round trip to the database. Recently when building a PHP site, I had a few instances where query of query would have been really nice. I decided to write a function to perform a simple filter on a recordset. It is not a "query" of an existing recordset, but it is the next best thing. It can match records on one field if they exist in a comma-separated list. It can also be used to return a list of distinct records:

function queryOfQuery($rs, // The recordset to query
  $fields = "*", // optional comma-separated list of fields to return, or * for all fields
  $distinct = false, // optional true for distinct records
  $fieldToMatch = null, // optional database field name to match
  $valueToMatch = null) { // optional value to match in the field, as a comma-separated list

  $newRs = Array();
  $row = Array();
  $valueToMatch = explode(",",$valueToMatch);
  $matched = true;
  mysql_data_seek($rs, 0);
  if($rs) {
    while ($row_rs = mysql_fetch_assoc($rs)){
      if($fields == "*") {
        if($fieldToMatch != null) {
          $matched = false;
          if(is_integer(array_search($row_rs[$fieldToMatch],$valueToMatch))){
            $matched = true;
          }
        }
        if($matched) $row = $row_rs;
      }else{
        $fieldsArray=explode(",",$fields);
        foreach($fields as $field) {
          if($fieldToMatch != null) {
            $matched = false;
            if(is_integer(array_search($row_rs[$fieldToMatch],$valueToMatch))){
              $matched = true;
            }
          }
          if($matched) $row[$field] = $row_rs[$field];
        }
      }
      if($matched)array_push($newRs, $row);
    };
    if($distinct) {
      sort($newRs);
      for($i = count($newRs)-1; $i > 0; $i--) {
        if($newRs[$i] == $newRs[$i-1]) unset($newRs[$i]);
      }
    }
  }
  mysql_data_seek($rs, 0);
  return $newRs;
}

Sample uses:

Create the initial recordset:

<?php
mysql_select_db($database_northwind, $northwind);
$query_rs = "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products";
$rs = mysql_query($query_rs, $northwind) or die(mysql_error());
?>

Then use the function in one of the following ways:

// Return all records from rs where ProductID is one of the list
$blah = queryOfQuery($rs, "*", false, "ProductID", "3,4,6,10,12");

// Return ProductName field from rs where productid is one of the list
$blah = queryOfQuery($rs, "ProductName", false, "ProductID", "3,4,6,10,12");

// Return distinct UnitsInStock counts from rs
$blah = queryOfQuery($rs, "UnitsInStock", true);

After returning the array, you can loop through it:

<table border="1">
  <tr>
    <td>ProductID</td>
    <td>ProductName</td>
    <td>UnitPrice</td>
    <td>UnitsInStock</td>
  </tr>
<?php foreach($blah as $row_rs) { ?>
  <tr>
    <td><?php echo $row_rs['ProductID']; ?></td>
    <td><?php echo $row_rs['ProductName']; ?></td>
    <td><?php echo $row_rs['UnitPrice']; ?></td>
    <td><?php echo $row_rs['UnitsInStock']; ?></td>
  </tr>
<?php } ?>
</table>

The Northwind sample database for MySQL that I use in examples can be downloaded from here. It is based on the MS SQL Server version of the database.

Download a sample PHP page using the function here.

Category tags: Dreamweaver, ColdFusion

Before posting comments or trackbacks, please read the posting policy.

Full Blog Calendar

Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
Pay me securely with your Visa, MasterCard, Discover, or American Express card through PayPal!
About | Privacy Policy | Contact | License Agreement | ©2002-2024 Tom Muck | Dreamweaver Extensions