I want to share a recent work request that made me have to think for a bit. My customer would like to upload a text file containing one integer per line. The PHP script that reads the file should then use the integers as lookup keys to a SQLite database. The order in which they appear is how the customer wants to see the recordset displayed on the webpage.

Ok, so my boilerplate PHP class that I like to use looks like this

class MyList {
  private $dbh;
  function __construct() {
     this->dbh=PDO('sqlite:/path/to/my/sqlite.db');
  }
  // and its one useful method
  function get($file) {
     // read the raw file
     $aFileData = file($file);
     // now screen for lines that just contain integers
     $aFilteredData = array(); 
     foreach ($aFileData as $line) {
        if (preg_match('/^\s*\d+\s*$/,$line)) {
           $rec_key = preg_replace('/^\s*(\d+)\s*$/,'$1',$line);
           array_push($aFilteredData,$rec_key);
        }
      }
  ...
  }
}

This got me an array of the lookup keys.

From here, I wanted to query the SQLite database with a single call rather than making one call per key. I did that by making an WHERE key IN clause using my filtered array.

   $key_csv = join(",", $aFilteredData);
   $query = 'SELECT key,name,description,value,timestamp FROM mytable WHERE key IN ('.$key_csv.')';
   $sth = $this->dbh->prepare($query);
   $sth->execute();
   ...

Here is where I had to think about this. What mode should I use to fetch my recordsets? Ideally, I wanted an associative array that has ‘key’ as the index whose value is an associative array that has the field names as the keys.

Let’s say I get that, how would I tie it back to my original list while preserving the order? I would need to map the key to its ordering index to do that. But what if there is more than one instance of the key in the original list? Eh, I will cross that bridge if the customer needs it and jot it down under caveats.

For now I’ll perform a quick mapping using array_flip:

   $aKeyToIndex = array_flip($aFilteredData);  // thus if $aFilteredData[3] = 123 then  $aKeyToIndex[123] = 3

Back to the fetch mode selection. Originally I thought fetchAll with a mode of FETCH_COLUMN|FETCH_GROUP would be the right choice to create the array of associative arrays. Unfortunately, that mode only returns one other column as the value of each associative array — the second column, in my case ‘name’. So my result set just looked like
$aRecords[key]=name
instead of
$aRecords[key]=('name'=>name,'description'=>description, etc)

So instead I decided to with FETCH_CLASS and create another class object that would encase my return values:

      $aDRResults=$sth->fetchAll(PDO::FETCH_CLASS,"MyRec");
      foreach ($aDBResults as $rec) {
         // create the return array of records in proper order
         $aReturn[$aKeyToIndex[$rec->key]]=$rec;
      }
      return $aReturn;
   }
} // end of class MyList
class MyRec {
   public $key;
   public $name;
   public $description;
   public $value;
   public $timestamp;
}

I suppose I could have also used FETCH_OBJ to be even lazier to return an array of anonymous objects, but this way I now have the foundation to perform post query processing if needed.