function getListRows($options) { global $VIEWER_NAME, $TABLE_PREFIX; $VIEWER_NAME = "List Viewer ({$options['tableName']})"; // error checking $requiredOptions = array('tableName'); $validOptions = array('tableName', 'titleField', 'perPage', 'where', 'orderBy', 'viewerUrl', 'pageNum', 'useSeoUrls'); $errors = _getOptionErrors($requiredOptions, $validOptions, $options); if ($errors) { die("{$VIEWER_NAME} errors<br/>\n{$errors}"); } // set defaults if (!@$options['pageNum']) { $options['pageNum'] = @$_REQUEST['page']; } if (!@$options['pageNum']) { $options['pageNum'] = "1"; } // default to page 1 if (!@$options['perPage']) { $options['perPage'] = 10; } if (!@$options['viewerUrl']) { $options['viewerUrl'] = "No_viewerUrl_value_specified_in_options"; } // get absolute url for viewer if (@$options['useSeoUrls'] && @$options['viewerUrl'] && !preg_match("|[/]|", $options['viewerUrl'])) { $options['viewerUrl'] = dirname($_SERVER['SCRIPT_NAME']) . "/" . $options['viewerUrl']; $options['viewerUrl'] = preg_replace("|^[\\\\/]+|", "/", $options['viewerUrl']); // remove multiple leading slashes (and replace \ returned by dirname on windows in root) } # create query $schema = loadSchema($options['tableName']); $fullTableName = getTableNameWithPrefix($options['tableName']); $escapedTableName = mysql_escape($fullTableName); if (@$options['where'] != '') { $where = @$options['where']; } else { $where = _createDefaultWhereWithFormInput($schema, @$options['where'], $options); } $where = _addWhereConditionsForSpecialFields($schema, $where); $orderBy = @$options['orderBy'] ? "ORDER BY {$options['orderBy']}" : ''; $offset = ($options['pageNum'] - 1) * $options['perPage']; $limit = "LIMIT " . mysql_escape($options['perPage']) . " OFFSET " . mysql_escape($offset); $query = "SELECT SQL_CALC_FOUND_ROWS * FROM `{$escapedTableName}` {$where} {$orderBy} {$limit}"; # execute query $result = mysql_query($query) or die("{$VIEWER_NAME}: MySQL Error: " . htmlencode(mysql_error()) . "\n"); $rows = array(); while ($record = mysql_fetch_assoc($result)) { $filenameValue = getFilenameFieldValue($record, @$options['titleField']); $record['_link'] = _getLink($options['viewerUrl'], $filenameValue, $record['num'], @$options['useSeoUrls']); array_push($rows, $record); } $listDetails = _getListDetails($options, count($rows)); // return array($rows, $listDetails); }
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); }