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