/**
* Main function
*/
function processQuery($sql, $sql_append)
{
$startyear = intval($_GET['startyear']);
$endyear = intval($_GET['endyear']);
// No end year, give it enough
if ($endyear == 0) {
$endyear = 3000;
}
// make array and type
$params = [&$startyear, &$endyear];
$type = "ii";
$debug = false;
foreach ($_GET as $key => $val) {
if ($key == "startyear" || $key == 'endyear') {
continue;
}
if ($key == 'debug') {
$debug = true;
continue;
}
if ($val == "") {
continue;
}
$sql .= " AND " . $key . "=? ";
$type .= "s";
// need array element here, since we need a reference
$decoded_val[$key] = urldecode($val);
$params[] =& $decoded_val[$key];
}
// add the last part
$sql .= $sql_append;
if ($debug) {
print_r($params);
echo $sql;
echo $type;
}
$conn = DBConn($debug);
$stmt = $conn->prepare($sql);
if (!$stmt) {
if ($debug) {
echo "Prepare {$sql} failed: ({$conn->errno}) {$conn->error}";
}
exit(0);
}
// http://stackoverflow.com/questions/16236395/bind-param-with-array-of-parameters
call_user_func_array(array($stmt, "bind_param"), array_merge(array($type), $params));
$stmt->execute();
// Need to install
// sudo apt-get install php5-mysqlnd
$result = $stmt->get_result();
$rows = array();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$rows[] = $row;
}
// JSON_PRETTY_PRINT|
//http://php.net/manual/de/function.gzencode.php
//print gzencode(json_encode($rows,JSON_UNESCAPED_UNICODE));
print json_encode($rows, JSON_UNESCAPED_UNICODE);
$conn->close();
}
function test()
{
$conn = DBConn();
// select a collection (analogous to a relational database's table)
$colnames = ['housesale', 'aptsale', 'flatsale', 'houserent', 'aptrent', 'flatrent', 'officetelrent', 'officetelsale', 'aptlots', 'landsale'];
foreach ($colnames as $tname) {
mkagg($conn, $tname, 2015, 12);
}
}
/**
*/
function processQuery($sql, $sql_append, $simple)
{
$startyear = intval($_GET['startyear']);
$endyear = intval($_GET['endyear']);
if ($endyear == 0) {
$endyear = 3000;
}
$params = [&$startyear, &$endyear];
$type = "ii";
$searchKey = "";
$i = 0;
$debug = false;
$hasMonthlyType = false;
foreach ($_GET as $key => $val) {
if ($key == 'startyear' || $key == 'endyear') {
continue;
}
if ($key == 'debug') {
$debug = true;
continue;
}
if ($val == "") {
continue;
}
if ($i++ > 0) {
$searchKey .= "::";
}
if ($key == 'monthlyType') {
$hasMonthlyType = true;
}
$sql .= " AND " . $key . "=? ";
$type .= "s";
// need array element here, since we need a new variable
$decodedvar = urldecode($val);
$decoded_val[$key] = $decodedvar;
$params[] =& $decoded_val[$key];
$searchKey .= $decodedvar;
}
// Add SQL
$sql .= $sql_append;
// only three, let's use simple sql
if ($i <= 3 && !$hasMonthlyType) {
$sql = $simple;
$params = [&$searchKey, &$startyear, &$endyear];
$type = "sii";
}
if ($debug) {
print_r($params);
echo $sql;
echo $type;
}
$conn = DBConn($debug);
$stmt = $conn->prepare($sql);
if (!$stmt) {
if ($debug) {
echo "Prepare {$sql} failed: ({$conn->errno}) {$conn->error}";
}
exit(0);
}
// http://stackoverflow.com/questions/16236395/bind-param-with-array-of-parameters
call_user_func_array(array($stmt, "bind_param"), array_merge(array($type), $params));
$stmt->execute();
// Need to install
// sudo apt-get install php5-mysqlnd
$result = $stmt->get_result();
$rows = array();
while ($row = $result->fetch_array(MYSQLI_ASSOC)) {
$rows[] = $row;
}
// JSON_PRETTY_PRINT|
print json_encode($rows, JSON_UNESCAPED_UNICODE);
$conn->close();
}
<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
include_once 'dbconn.php';
$conn = DBConn();
$limit = "";
if (isset($_GET['limit'])) {
$limit = "limit " . $_GET['limit'];
}
$sql = "select type, loc, count(*) as c from log group by type, loc order by c desc {$limit}";
$result = $conn->query($sql);
$rows = [];
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
$rows[] = $row;
}
}
//
print json_encode($rows, JSON_PRETTY_PRINT | JSON_UNESCAPED_UNICODE);
$conn->close();
if ($key == 'debug') {
$debug = true;
continue;
}
if ($key == 'query') {
continue;
}
if ($i++ !== 0) {
$k .= "::";
}
$k .= urldecode($val);
}
if ($debug) {
echo "Query: {$k}";
}
$conn = DBConn($debug);
$escapedK = $conn->real_escape_string($k);
$sql = "select v from {$tname} where k='" . $escapedK . "'";
if ($debug) {
echo $sql;
}
$result = $conn->query($sql);
$rows = [];
if ($result->num_rows > 0) {
// output data of each row
while ($row = $result->fetch_assoc()) {
$rows[] = $row['v'];
}
}
// JSON_PRETTY_PRINT|
print json_encode($rows, JSON_UNESCAPED_UNICODE);