Temporary Tables in Zend Framework

Use temporary tables when your sql queries are starting to do gymnastics. The following would go in your model:

$config = $this->getAdapter()->getConfig();
$db = Zend_Db::factory('PDO_MYSQL', $config);
$sql = "CREATE TEMPORARY TABLE tmp_datasets (";
$sql .= "`id` INT(11) NOT NULL AUTO_INCREMENT, ";
$sql .= "`primary_date` DATE, ";

$fieldArray = array(0 => 'primary_date');
// looping through existing table dataset to get column names for temp table
foreach ($dataFields as $field) {
// format the field label to use as the column name
// needs to be lowercase and no spaces/spec char
$pattern = '[^a-z0-9]';
$label = strtolower($field->label);
// replace anything that's not a letter or number with an underscore
$label = preg_replace($pattern, '_', $label);
// if label is 'id' or 'primary_date', rename it since we're already using those
if ($label === 'id') {
$label = 'id_1';
}
if ($label === 'primary_date') {
$label = 'primary_date_1';
}

// build the columns
switch ($field->db_type) {
case 'date':
$type = 'DATE';
break;
case 'varchar(50)':
$type = 'VARCHAR(50)';
break;
case 'int(10)':
$type = 'INT(10)';
break;
case 'decimal(10,2)':
$type = 'DECIMAL(10,2)';
break;
default:
$type = 'VARCHAR(255)';
break;
}

$null = 'NOT NULL'; // either NULL or NOT NULL
$sql .= "`{$label}` {$type} {$null}, ";
$fieldArray[$field->id] = $label;

}

$sql .= " PRIMARY KEY (`id`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8"
$executed = $db->query($sql);
ksort($fieldArray); // need to keep everything in the right order
$fieldString = implode('`,`', $fieldArray);

// need to get a string with the same number of '?' as items in $fieldArray -
// seems like an awkward way to do this but couldn't find a php function
// that would do it...
$fieldVariablesArray = array();
foreach ($fieldArray as $fieldItem) {
$fieldVariablesArray[] = '?';
}
$fieldVariables = implode(',', $fieldVariablesArray);

$stmt = $db->prepare("INSERT INTO tmp_datasets (`{$fieldString}`) VALUES ({$fieldVariables})");

At this point we should have the table set up but there’s nothing in it yet. Query your existing table(s) to get the data you want to store.

// get the values
// $fieldIdArray came from data that was passed into the method
$datasetEntries = $this->_datasetEntry->getAllForFields($fieldIdArray);

// Loop through your datasets to build a new array of values to insert into
// the temp table
$valuesArray = array();
foreach ($datasetEntries as $key => $value) {
$valuesArray[$key] = $value->value;
}
ksort($valuesArray); // again, keep things in the right order
// have to remove keys for some reason or it won't execute
$valuesArray = array_values($valuesArray);
$stmt->execute($valuesArray);
unset($stmt);

Now we should have data in our table that we can query as usual.

$select = $db->select();
$select->from('tmp_datasets');
$select->where("primary_date IN ('{$dateString}')"); // or whatever your where clause should be
$select->order(array("primary_date ASC"));
$tmpDatasets = $db->fetchAll($select);

You might also want to check out this post, which does a much better job of explaining temporary tables than me.