function sql_query($sql, $cache = false, $fetchrows = -1, $dbstruct = true, $logthis = 2)
{
# sql_query(sql) - execute a query and return the results as an array.
# Database functions are wrapped in this way so supporting a database server other than MySQL is
# easier.
# $cache is not used at this time - it was intended for disk based results caching which may be added in the future.
# If $fetchrows is set we don't have to loop through all the returned rows. We
# just fetch $fetchrows row but pad the array to the full result set size with empty values.
# This has been added retroactively to support large result sets, yet a pager can work as if a full
# result set has been returned as an array (as it was working previously).
# $logthis parameter is only relevant if $mysql_log_transactions is set. 0=don't log, 1=always log, 2=detect logging - i.e. SELECT statements will not be logged
global $db, $config_show_performance_footer, $debug_log, $debug_log_override, $mysql_verbatim_queries, $use_mysqli, $mysql_log_transactions;
if (!isset($debug_log_override)) {
check_debug_log_override();
}
if ($config_show_performance_footer) {
# Stats
# Start measuring query time
$time_start = microtime(true);
global $querycount;
$querycount++;
}
if ($debug_log || $debug_log_override) {
debug("SQL: " . $sql);
}
if ($mysql_log_transactions && !($logthis == 0)) {
global $mysql_log_location, $lang;
$requirelog = true;
if ($logthis == 2) {
// Ignore any SELECTs if the decision to log has not been indicated by function call,
if (strtoupper(substr(trim($sql), 0, 6)) == "SELECT") {
$requirelog = false;
}
}
if ($logthis == 1 || $requirelog) {
# Log this to a transaction log file so it can be replayed after restoring database backup
$mysql_log_dir = dirname($mysql_log_location);
if (!is_dir($mysql_log_dir)) {
@mkdir($mysql_log_dir, 0333, true);
if (!is_dir($mysql_log_dir)) {
exit("ERROR: Unable to create folder for \$mysql_log_location specified in config file: " . $mysql_log_location);
}
}
if (!file_exists($mysql_log_location)) {
global $mysql_db;
$mlf = @fopen($mysql_log_location, "wb");
@fwrite($mlf, "USE " . $mysql_db . ";\r\n");
if (!file_exists($mysql_log_location)) {
exit("ERROR: Invalid \$mysql_log_location specified in config file: " . $mysql_log_location);
}
// Set the permissions if we can to prevent browser access(will not work on Windows)
chmod($mysql_log_location, 0333);
}
$mlf = @fopen($mysql_log_location, "ab");
fwrite($mlf, "/* " . date("Y-m-d H:i:s") . " */ " . $sql . ";\n");
// Append the ';' so the file can be used to replay the changes
fclose($mlf);
}
}
# Execute query
$result = $use_mysqli ? mysqli_query($db, $sql) : mysql_query($sql);
if ($config_show_performance_footer) {
# Stats
# Log performance data
global $querytime, $querylog;
$time_total = microtime(true) - $time_start;
if (isset($querylog[$sql])) {
$querylog[$sql]['dupe'] = $querylog[$sql]['dupe'] + 1;
$querylog[$sql]['time'] = $querylog[$sql]['time'] + $time_total;
} else {
$querylog[$sql]['dupe'] = 1;
$querylog[$sql]['time'] = $time_total;
}
$querytime += $time_total;
}
$error = $use_mysqli ? mysqli_error($db) : mysql_error();
$return_rows = array();
if ($error != "") {
if ($error == "Server shutdown in progress") {
echo "<span class=error>Sorry, but this query would return too many results. Please try refining your query by adding addition keywords or search parameters.<!--{$sql}--></span>";
} elseif (substr($error, 0, 15) == "Too many tables") {
echo "<span class=error>Sorry, but this query contained too many keywords. Please try refining your query by removing any surplus keywords or search parameters.<!--{$sql}--></span>";
} else {
# Check that all database tables and columns exist using the files in the 'dbstruct' folder.
if ($dbstruct) {
check_db_structs();
# Try again (no dbstruct this time to prevent an endless loop)
return sql_query($sql, $cache, $fetchrows, false);
exit;
}
errorhandler("N/A", $error . "<br/><br/>" . $sql, "(database)", "N/A");
}
exit;
} elseif ($result === true) {
return $return_rows;
// no result set, (query was insert, update etc.) - simply return empty array.
}
$return_row_count = 0;
while (($fetchrows == -1 || $return_row_count < $fetchrows) && ($use_mysqli && ($result_row = mysqli_fetch_assoc($result)) || !$use_mysqli && ($result_row = mysql_fetch_assoc($result)))) {
if ($mysql_verbatim_queries) {
$return_rows[$return_row_count] = $result_row;
// simply dump the entire row into the return results set
} else {
while (list($name, $value) = each($result_row)) {
$return_rows[$return_row_count][$name] = str_replace("\\", "", stripslashes($value));
// iterate through each cell cleaning up
}
}
$return_row_count++;
}
if ($fetchrows == -1) {
return $return_rows;
}
# If we haven't returned all the rows ($fetchrows isn't -1) then we need to fill the array so the count
# is still correct (even though these rows won't be shown).
$query_returned_row_count = $use_mysqli ? mysqli_num_rows($result) : mysql_num_rows($result);
// get the number of rows returned from the query
if ($return_row_count < $query_returned_row_count) {
$return_rows = array_pad($return_rows, $query_returned_row_count, 0);
// if short then pad out
}
return $return_rows;
}
function sql_query($sql, $cache = false, $fetchrows = -1, $dbstruct = true)
{
# sql_query(sql) - execute a query and return the results as an array.
# Database functions are wrapped in this way so supporting a database server other than MySQL is
# easier.
# $cache is not used at this time - it was intended for disk based results caching which may be added in the future.
# If $fetchrows is set we don't have to loop through all the returned rows. We
# just fetch $fetchrows row but pad the array to the full result set size with empty values.
# This has been added retroactively to support large result sets, yet a pager can work as if a full
# result set has been returned as an array (as it was working previously).
global $db, $querycount, $querytime, $config_show_performance_footer, $querylog, $debug_log, $mysql_verbatim_queries;
$counter = 0;
if ($config_show_performance_footer) {
# Stats
# Start measuring query time
$time_start = microtime(true);
$querycount++;
}
if ($debug_log) {
debug("SQL: " . $sql);
}
# Execute query
global $use_mysqli;
if ($use_mysqli) {
$result = mysqli_query($db, $sql);
} else {
$result = mysql_query($sql);
}
if ($config_show_performance_footer) {
# Stats
# Log performance data
$time_end = microtime(true);
$time_total = $time_end - $time_start;
if (isset($querylog[$sql])) {
$querylog[$sql]['dupe'] = $querylog[$sql]['dupe'] + 1;
$querylog[$sql]['time'] = $querylog[$sql]['time'] + $time_total;
} else {
$querylog[$sql]['dupe'] = 1;
$querylog[$sql]['time'] = $time_total;
}
$querytime += $time_total;
}
if ($use_mysqli) {
$error = mysqli_error($db);
} else {
$error = mysql_error();
}
if ($error != "") {
if ($error == "Server shutdown in progress") {
echo "<span class=error>Sorry, but this query would return too many results. Please try refining your query by adding addition keywords or search parameters.<!--{$sql}--></span>";
} elseif (substr($error, 0, 15) == "Too many tables") {
echo "<span class=error>Sorry, but this query contained too many keywords. Please try refining your query by removing any surplus keywords or search parameters.<!--{$sql}--></span>";
} else {
# Check that all database tables and columns exist using the files in the 'dbstruct' folder.
if ($dbstruct) {
check_db_structs();
# Try again (no dbstruct this time to prevent an endless loop)
return sql_query($sql, $cache, $fetchrows, false);
exit;
}
errorhandler("N/A", $error . "<br/><br/>" . $sql, "(database)", "N/A");
}
exit;
} elseif ($result === true) {
# no result set, (query was insert, update etc.)
} else {
$row = array();
if ($use_mysqli) {
while (($rs = mysqli_fetch_assoc($result)) && ($counter < $fetchrows || $fetchrows == -1)) {
while (list($name, $value) = each($rs)) {
//if (!is_integer($name)) # do not run for integer values (MSSQL returns two keys for each returned column, a numeric and a text)
// {
$row[$counter][$name] = $mysql_verbatim_queries ? $value : str_replace("\\", "", stripslashes($value));
// }
}
$counter++;
}
} else {
while (($rs = mysql_fetch_assoc($result)) && ($counter < $fetchrows || $fetchrows == -1)) {
while (list($name, $value) = each($rs)) {
//if (!is_integer($name)) # do not run for integer values (MSSQL returns two keys for each returned column, a numeric and a text)
// {
$row[$counter][$name] = $mysql_verbatim_queries ? $value : str_replace("\\", "", stripslashes($value));
// }
}
$counter++;
}
}
# If we haven't returned all the rows ($fetchrows isn't -1) then we need to fill the array so the count
# is still correct (even though these rows won't be shown).
$rows = count($row);
if ($use_mysqli) {
$totalrows = mysqli_num_rows($result);
} else {
$totalrows = mysql_num_rows($result);
}
#echo "-- $rows out of $totalrows --";
if ($fetchrows != -1 && $rows < $totalrows) {
$row = array_pad($row, $totalrows, 0);
}
return $row;
}
}
