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; }