Source: sqlreport.phps - download
<?php
/*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* sqlreport.phps 0.1
* by daif alotaibi (http://daif.net)
* daif@daif.net
*
* Link:
* http://daif.net/script/sqlreport.phps
*
* Examples:
* $reports = [
* 'report1' => 'SELECT * FROM `table1` LIMIT 10',
* 'report2' => 'SELECT * FROM `table2` LIMIT 10',
* ];
* sqlreport.php?report=report1
* sqlreport.php?report=report2
*
*/
// Configurations
$config = [
// Database
'db_dsn'=> 'mysql:host=localhost;dbname=wordpress',
'db_usr'=> 'root',
'db_pwd'=> '',
'db_opt'=> [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"],
// Caching
'cache_path'=> __DIR__.'/cache/', // full path
'cache_expire'=> 60, // in seconds
];
// A list of SQL statements to generate reports
$reports = [
'wp_users' => 'SELECT * FROM `wp_users` ORDER BY `wp_users`.`ID` DESC LIMIT 10',
'wp_options' => 'SELECT * FROM `wp_options` ORDER BY `wp_options`.`option_id` ASC LIMIT 10',
];
// Connecting to database
$conn = new PDO($config['db_dsn'], $config['db_usr'], $config['db_pwd'], $config['db_opt']);
if(isset($_GET['report'])) {
if(isset($reports[$_GET['report']])){
$sql = $reports[$_GET['report']];
// if the the report data in the cache don't query the database
if(($table_rows = CacheFile($sql)) == NULL){
$sth = $conn->prepare($sql);
$sth->execute();
$table_rows = $sth->fetchAll(PDO::FETCH_ASSOC);
CacheFile($sql, $table_rows);
}
$table_head = array_keys($table_rows[0]);
} else {
$error = 'Requested report is not available.';
}
}
// Tiny file based caching system
function CacheFile($id, $data=NULL, $expire=NULL) {
global $config;
$cache_path = $config['cache_path'];
$cache_expire = $config['cache_expire'];
if($expire) $cache_expire = $expire;
if (!file_exists($cache_path)) {
mkdir($cache_path, 0775, true);
}
$cache_file = $cache_path.md5($id).'.cache';
if($data) {
return file_put_contents($cache_file, serialize($data));
} elseif(file_exists($cache_file)) {
if(filemtime($cache_file) + $cache_expire > time()) {
return unserialize(file_get_contents($cache_file));
}
unlink($cache_file);
}
return NULL;
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>SQLReport</title>
<style type="text/css">
body{
margin: 0.5em;
font-family: Arial, Tahoma, sans-serif;
color: #222;
}
table{
border-collapse: collapse;
border-spacing: 0;
background-color: white;
width: 100%;
border: 1px solid #c3c9cc;
}
caption{
text-align: left;
padding: 8px 0px;
}
th, td{
padding: 1em 0.75em;
text-align: left;
border: 1px solid #c3c9cc;
}
th{
background-color: #f5f5f5;
white-space: nowrap;
}
.buttons{
border-bottom:1px solid #ccc;
margin-bottom: 15px;
padding-bottom: 8px;
}
.container{
overflow: auto;
-webkit-overflow-scrolling: touch;
}
</style>
</head>
<body>
<div class="buttons">
<?php foreach ($reports as $name => $var) { ?>
[<a href="sqlreport.php?report=<?php echo $name?>"><?php echo $name?></a>]
<?php } ?>
</div>
<?php if(isset($error)) { ?>
<?php echo $error?>
<?php } ?>
<?php if(isset($sql)) { ?>
<div class="container">
<table>
<caption><?php echo $_GET['report']?></caption>
<thead>
<tr>
<?php foreach ($table_head as $key => $thead) { ?>
<th scope="col"><?php echo $thead?></th>
<?php } ?>
</tr>
</thead>
<tbody>
<?php foreach ($table_rows as $key => $row) { ?>
<tr>
<?php foreach ($table_head as $key => $thead) { ?>
<td><?php echo $row[$thead]?></td>
<?php } ?>
</tr>
<?php } ?>
</tbody>
</table>
</div>
<?php } ?>
</body>
</html>