function searchMultipleTables($searchTables, $searchOptions)
{
global $VIEWER_NAME, $TABLE_PREFIX;
$VIEWER_NAME = "Search Multiple Tables";
# error checking
if (!@$searchOptions['perPage']) {
die("{$VIEWER_NAME} : No perPage option specified!\n");
}
### create subqueries
$subqueries = array();
foreach ($searchTables as $tablename => $tableOptions) {
foreach (array('viewerUrl', 'searchFields', 'titleField') as $optionName) {
if (!@$tableOptions[$optionName]) {
die("{$VIEWER_NAME} : No '{$optionName}' option specified for searchTable '" . htmlencode($tablename) . "'!\n");
}
}
// get search fields
$searchFieldsCSV = '';
foreach ($tableOptions['searchFields'] as $fieldname) {
if ($searchFieldsCSV) {
$searchFieldsCSV .= ", ";
}
$searchFieldsCSV .= "`" . mysql_escape($fieldname) . "`";
}
// create query
$fullEscapedTable = mysql_escape($TABLE_PREFIX . $tablename);
$schema = loadSchema($tablename);
$where = _addWhereConditionsForSpecialFields($schema, '', $searchOptions);
$subquery = "SELECT '{$tablename}' as `tablename`, num, `{$tableOptions['titleField']}` as `_title`, ";
if (@$tableOptions['summaryField']) {
$subquery .= "`{$tableOptions['summaryField']}` as `_summary`, ";
} else {
$subquery .= "'' as `_summary`, ";
}
foreach (range(1, 10) as $num) {
$fieldname = "field{$num}";
if (@$tableOptions["field{$num}"]) {
$subquery .= "`{$tableOptions[$fieldname]}` as `{$fieldname}`, ";
} else {
$subquery .= "'' as `{$fieldname}`, ";
}
}
$subquery .= "CONCAT_WS('\\t', {$searchFieldsCSV}) as _content FROM `{$fullEscapedTable}` {$where}\n";
$subqueries[] = $subquery;
}
# create query
$schema = array('_content' => array());
// allow this field to be searched in _getWhereForSearchQuery
$where = _getWhereForSearchQuery($searchOptions['keywords'], array('_content'), $schema);
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM (\n " . implode(' UNION ', $subqueries) . ") as combinedTable\n";
if ($where) {
$query .= "WHERE {$where}";
}
if (@$searchOptions['orderBy']) {
$query .= " ORDER BY " . $searchOptions['orderBy'] . " ";
}
$query .= mysql_limit($searchOptions['perPage'], @$_REQUEST['page']);
#
## execute query
$rows = array();
if ($searchOptions['keywords'] && $where) {
if (@$searchOptions['debugSql']) {
print "<xmp>{$query}</xmp>";
}
$result = mysql_query($query) or die("{$VIEWER_NAME}: MySQL Error: " . htmlencode(mysql_error()) . "\n");
while ($record = mysql_fetch_assoc($result)) {
$detailUrl = $searchTables[$record['tablename']]['viewerUrl'];
$filenameValue = '';
// not working yet... getFilenameFieldValue($record, $searchTables[$record['tablename']]['filenameFields']);
$useSeoUrls = false;
$link = _getLink($detailUrl, $filenameValue, $record['num'], $useSeoUrls);
$record['_title'] = $record['_title'];
$record['_summary'] = strip_tags($record['_summary']);
$record['_link'] = $link;
array_push($rows, $record);
}
}
$searchOptions['pageNum'] = @$_REQUEST['page'];
$listDetails = _getListDetails($searchOptions, count($rows));
//
return array($rows, $listDetails);
}
function getMysqlLimit($perPage, $pageNum)
{
return mysql_limit($perPage, $pageNum);
}
