PHP DBConn Examples

Example #1

File: q2.php Project: hunkim/kproperty

/**
* 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();
}

Example #2

File: mkagg.php Project: hunkim/kproperty

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

Example #3

File: s2.php Project: hunkim/kproperty

/**
*/
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();
}

Example #4

File: _log_.php Project: hunkim/kproperty

<?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();

Example #5

File: r2.php Project: hunkim/kproperty

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