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 getRecord($options)
{
global $VIEWER_NAME, $TABLE_PREFIX;
$VIEWER_NAME = "Page Viewer ({$options['tableName']})";
// error checking
$requiredOptions = array('tableName');
$validOptions = array('tableName', 'recordNum', 'where', 'titleField', 'orderBy');
$errors = _getOptionErrors($requiredOptions, $validOptions, $options);
if ($errors) {
die("{$VIEWER_NAME} errors<br/>\n{$errors}");
}
// set defaults
$schema = loadSchema($options['tableName']);
if (!@$options['recordNum']) {
$options['recordNum'] = getLastNumberInUrl();
}
if (@$schema['menuType'] == 'single') {
$options['recordNum'] = "1";
}
// always load record 1 for single menus
// get where condition
$whereConditions = '';
$escapedRecordNum = mysql_escape((int) $options['recordNum']);
if ($options['where']) {
$whereConditions = $options['where'];
} elseif ($options['recordNum']) {
$whereConditions = "num = '{$escapedRecordNum}'";
}
// get record
$fullTableName = getTableNameWithPrefix($options['tableName']);
$escapedTableName = mysql_escape($fullTableName);
$where = _addWhereConditionsForSpecialFields($schema, $whereConditions, $options);
$orderBy = @$options['orderBy'] ? "ORDER BY {$options['orderBy']}" : '';
$query = "SELECT * FROM `{$escapedTableName}` {$where} {$orderBy} LIMIT 0, 1";
$result = mysql_query($query) or die("{$VIEWER_NAME}: MySQL Error: " . htmlencode(mysql_error()) . "\n");
$record = mysql_fetch_assoc($result);
// add _link field
if ($record) {
$filenameValue = getFilenameFieldValue($record, @$options['titleField']);
$record['_link'] = _getLink($_SERVER['SCRIPT_NAME'], $filenameValue, $record['num'], @$options['useSeoUrls']);
}
// define upload fields
if ($record) {
foreach ($schema as $fieldname => $fieldSchema) {
if (!is_array($fieldSchema)) {
continue;
}
// not a field definition, table metadata field
if (@$fieldSchema['type'] != 'upload') {
continue;
}
// skip all but upload fields
$record[$fieldname] = "Use getUploads() function to list uploads (See code generator).\n";
}
}
//
return $record;
}
