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