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 _getRecords_getQuery($options, $schema)
{
global $VIEWER_NAME, $TABLE_PREFIX;
// create fieldlist
$selectFields = "`{$options['tableName']}`.*";
// add left joins
$LEFT_JOIN = '';
if (@$options['leftJoin']) {
// Fix $_REQUEST keys containing tablename
__replaceUnderscoresInRequest($options['tableName']);
// add qualified fieldsnames to schema
foreach (array_keys(getSchemaFields($schema)) as $fieldname) {
$schema["{$options['tableName']}.{$fieldname}"] = $schema[$fieldname];
$schema["{$options['tableName']}.{$fieldname}"]['name'] = $fieldname;
}
//
foreach ($options['leftJoin'] as $foreignTable => $foreignKey) {
/* get ON condition
* Modified pregmatch statment:
* \b= match 'ON ' anywhere in string.
* /i= don't match case.
* \s= space
*/
if (preg_match("/\\bON\\s/i", $foreignKey)) {
$ON_CONDITION = $foreignKey;
} else {
$ON_CONDITION = "ON {$options['tableName']}.`{$foreignKey}` = {$foreignTable}.num";
}
// add left join
$LEFT_JOIN .= "LEFT JOIN `{$TABLE_PREFIX}{$foreignTable}` AS `{$foreignTable}` {$ON_CONDITION}\n";
// add fieldnames to SELECT
$foreignSchemaFields = getSchemaFields($foreignTable);
$validFieldTypes = array('textfield', 'textbox', 'wysiwyg', 'date', 'list', 'checkbox');
foreach (array_keys($foreignSchemaFields) as $fieldname) {
if (in_array(@$foreignSchemaFields[$fieldname]['type'], $validFieldTypes) || @$fieldname == 'num') {
$selectFields .= ",\n {$foreignTable}.`{$fieldname}` as `{$foreignTable}.{$fieldname}`";
}
// Fix $_REQUEST keys containing tablename
__replaceUnderscoresInRequest($foreignTable);
// add fieldnames to schema
$schema["{$foreignTable}.{$fieldname}"] = $foreignSchemaFields[$fieldname];
$schema["{$foreignTable}.{$fieldname}"]['name'] = $fieldname;
}
}
}
// create where
$where = @$options['where'];
if ($options['allowSearch']) {
$defaultWhere = _createDefaultWhereWithFormInput($schema, '', $options);
if ($options['requireSearchMatch'] && !$defaultWhere) {
$defaultWhere = "0 = 1";
}
// always false
if (!$where) {
$where = $defaultWhere;
} elseif ($where && $defaultWhere) {
$where = "({$where}) AND ({$defaultWhere})";
}
// v2.51 Fixed potential AND/OR precedence issue by adding () AND ()
}
if (@$schema['createdByUserNum'] && @$schema['_hideRecordsFromDisabledAccounts'] && !@$options['includeDisabledAccounts']) {
if ($where) {
$where .= " AND ";
}
$subquery = "SELECT num FROM `{$TABLE_PREFIX}accounts` WHERE disabled != 1 AND (expiresDate > NOW() OR neverExpires = 1)";
$where .= "{$options['tableName']}.createdByUserNum IN ({$subquery})";
}
$where = _addWhereConditionsForSpecialFields($schema, $where, $options, $options['tableName']);
// adds WHERE to beginning of string, do this LAST
if (@$options['orWhere']) {
$where = preg_replace("/^\\s*WHERE\\s*/i", '', $where);
// remove WHERE keyword
if ($where) {
$where = "({$where}) OR {$options['orWhere']}";
} else {
$where = $options['orWhere'];
}
if ($where) {
$where = "\nWHERE {$where}";
}
}
// add select expr
if (@$options['addSelectExpr']) {
$selectFields .= ", {$options['addSelectExpr']}";
}
// create query
$query = "SELECT SQL_CALC_FOUND_ROWS {$selectFields}\n";
$query .= "FROM `{$TABLE_PREFIX}{$options['tableName']}` as `{$options['tableName']}`\n";
$query .= $LEFT_JOIN;
$query .= "{$where}\n";
$query .= @$options['groupBy'] ? " GROUP BY {$options['groupBy']}" : '';
$query .= @$options['having'] ? " HAVING {$options['having']}" : '';
$query .= @$options['orderBy'] ? " ORDER BY {$options['orderBy']}" : '';
if (@$options['limit']) {
$query .= "\n LIMIT " . (int) $options['limit'];
}
if (@$options['offset']) {
$query .= "\nOFFSET " . (int) $options['offset'];
}
if (@$options['debugSql']) {
print "<xmp>{$query}</xmp>";
}
return $query;
}
