Page MenuHomeCode
Files F947006

data.php
No OneTemporary
Actions

data.php
View Options

<?php
// db settings
$dbserver = 'localhost';
$dbuser = 'root';
$dbpassword = 'root';
error_reporting(E_ALL);
/*
Simple protocol:
- Inputs via POST variables.
- Output is a string that can be evaluated into a JSON
First element of the array contains return status.
This simplified tutorial code should not be deployed without a security review.
*/
@include "json.php";
// set up response encoding
header("Content-Type: text/html; charset=utf-8");
// util
function getPostString($inName) {
// make sure input strings are 'clean'
return mysql_real_escape_string(@$_POST[$inName]);
}
// used for json encoding
$json = new Services_JSON();
function echoJson($inData) {
global $json;
// delay in ms
$delay = getPostString('delay');
if (!empty($delay))
usleep($delay * 1000);
echo '/* ' . $json->encode($inData) . ' */';
}
function error($inMessage) {
$inMessage = str_replace('"', '\\"', $inMessage);
error_log($inMessage);
//echo '/* ({error: true, message: "' . $inMessage . '"}) */';
echoJson(array('error' => true, 'message' => $inMessage));
exit;
}
function getArray($inResult, $inArray="true") {
$o = Array();
while ($row = ($inArray ? mysql_fetch_row($inResult) : mysql_fetch_object($inResult)))
$o[] = $row;
return $o;
}
// connect to DB
mysql_connect($dbserver, $dbuser, $dbpassword);
// select DB
$database = getPostString("database");
$database = ($database ? $database : $db);
if (!mysql_select_db($database))
error('failed to select db: ' . mysql_error());
// select table
$table = getPostString("table");
$table = ($table ? $table : $dbtable);
// cache
$colCache = NULL;
$pkCache = NULL;
// set UTF8 output (MySql > 4.0)
mysql_query("SET NAMES UTF8");
// server, database, table meta data
function getDatabases() {
$result = mysql_query("SHOW DATABASES");
$output = Array();
while ($row = mysql_fetch_row($result)) {
$r = strtolower($row[0]);
if ($r != 'mysql' && $r != 'information_schema')
$output[] = $row[0];
}
return $output;
}
function getTables() {
global $database;
$result = mysql_query("SHOW TABLES FROM $database");
$output = Array();
while ($row = mysql_fetch_row($result))
$output[] = $row[0];
return $output;
}
function getColumns() {
global $table, $colCache;
if (!$colCache) {
$result = mysql_query("SHOW COLUMNS FROM `$table`");
return getArray($result, false);
$colCache = getArray($result, false);
}
return $colCache;
}
// returns object: $this->name, $this->index
function getPk() {
global $pkCache;
if (!$pkCache) {
$k = '';
$columns = getColumns();
for ($i=0; $i < count($columns); $i++) {
$c = $columns[$i];
if ($c->Key == 'PRI') {
$k = $c->Field;
break;
}
}
$pkCache->index = $i;
$pkCache->name = $k;
}
return $pkCache;
}
function getTableInfo() {
global $table, $database;
$c = getColumns();
$r = rowcount();
return array("count" => $r, "columns" => $c, "database" => $database, "table" => $table);
}
function getOldPostPkValue() {
$pk = getPk();
return getPostString('_o' . $pk->index);
}
function getNewPostPkValue() {
$pk = getPk();
return getPostString('_' . $pk->index);
}
function getPostColumns() {
$columns = getColumns();
for ($i=0, $a=array(), $p; (($p=getPostString("_".$i)) != ''); $i++) {
$r = new stdClass();
$r->name = $columns[$i]->Field;
$r->value = $p;
$a[] = $r;
}
return $a;
}
function getOrderBy() {
$ob = getPostString("orderby");
if (is_numeric($ob)) {
$columns = getColumns();
$ob = $columns[intval($ob)-1]->Field;
}
return $ob;
}
function getWhere() {
$w = getPostString("where");
return ($w ? " WHERE $w" : "");
}
// basic operations
function rowcount() {
global $table;
$query = "SELECT COUNT(*) FROM `$table`" . getWhere();
$result = mysql_query($query);
if (!$result)
error("failed to perform query: $query. " . mysql_error());
if ($row = mysql_fetch_row($result))
return $row[0];
else
return 0;
}
function select($inQuery = '') {
global $table;
// built limit clause
$lim = (int)getPostString("limit");
$off = (int)getPostString("offset");
$limit = ($lim || $off ? " LIMIT $off, $lim" : "");
// build order by clause
$desc = (boolean)getPostString("desc");
$ob = getOrderBy();
$orderby = ($ob ? " ORDER BY `" . $ob . "`" . ($desc ? " DESC" : "") : "");
// build query
$query = ($inQuery ? $inQuery : "SELECT * FROM `$table`" . getWhere() . $orderby . $limit);
// execute query
if (!$result = mysql_query($query))
error("failed to perform query: $query. " . mysql_error());
// fetch each result row
return getArray($result);
}
function reflectRow() {
global $table;
$pk = getPk();
$key = getNewPostPkValue();
$where = "`$pk->name`=\"$key\"";
return select("SELECT * FROM `$table` WHERE $where LIMIT 1");
}
function update() {
// build set clause
for ($i=0, $set = array(), $cols = getPostColumns(), $v; ($v=$cols[$i]); $i++)
$set[] = "`$v->name` = '$v->value'";
$set = implode(', ', $set);
// our table
global $table;
// build query
$pk = getPk();
$pkValue = getOldPostPkValue();
$query = "UPDATE `$table` SET $set WHERE `$pk->name` = '$pkValue' LIMIT 1";
// execute query
if (!mysql_query($query))
error("failed to perform query: [$query]. " .
"MySql says: [" . mysql_error() ."]");
else {
return reflectRow();
}
}
function insert() {
global $table;
// build values clause
for ($i=0, $values = array(), $cols = getPostColumns(), $v; ($v=$cols[$i]); $i++)
$values[] = $v->value;
$values = '"' . implode('", "', $values) . '"';
// build query
$query = "INSERT INTO `$table` VALUES($values)";
// execute query
if (!mysql_query($query))
error("failed to perform query: [$query]. " .
"MySql says: [" . mysql_error() ."]");
else {
return reflectRow();
}
}
function delete() {
global $table;
// build query
$n = getPostString("count");
$pk = getPk();
for ($i = 0, $deleted=array(); $i < $n; $i++) {
$key = getPostString("_$i");
array_push($deleted, $key);
$query = "DELETE FROM `$table` WHERE `$pk->name`=\"$key\" LIMIT 1";
// execute query
if (!mysql_query($query) || mysql_affected_rows() != 1)
error("failed to perform query: [$query]. " .
"Affected rows: " . mysql_affected_rows() .". " .
"MySql says: [" . mysql_error() ."]");
}
return $deleted;
}
// find (full text search)
function findData($inFindCol, $inFind, $inOrderBy, $inFullText) {
global $table;
$where = ($inFullText ? "WHERE MATCH(`$inFindCol`) AGAINST ('$inFind')" : "WHERE $inFindCol LIKE '$inFind'");
$query = "SELECT * FROM $table $where $inOrderBy";
$result = mysql_query($query);
// return rows
return getArray($result);
}
// binary search through sorted data, supports start point ($inFindFrom) and direction ($inFindForward)
function findRow($inData, $inFindFrom=-1, $inFindForward) {
$b = -1;
$l = count($inData);
if (!$inData)
return $b;
if (!$inFindFrom==-1 || $l < 2)
$b = 0;
else {
// binary search
$t = $l-1;
$b = 0;
while ($b <= $t) {
$p = floor(($b+$t)/2);
$d = $inData[$p][0];
if ($d < $inFindFrom)
$b = $p + 1;
else if ($d > $inFindFrom)
$t = $p - 1;
else {
$b = $p;
break;
}
}
if ($inFindFrom == $inData[$b][0]) {
// add or subtract 1
$b = ($inFindForward ? ($b+1 > $l-1 ? 0 : $b+1) : ($b-1 < 0 ? $l-1 : $b-1) );
}
else if (!$inFindForward)
// subtract 1
$b = ($b-1 < 0 ? $l-1 : $b-1);
}
return $inData[$b][0];
}
function buildFindWhere($inFindData, $inKey, $inCol) {
$o = Array();
foreach($inFindData as $row)
$o[] = $inCol . "='" . $row[$inKey] . "'";
return (count($o) ? ' WHERE ' . implode(' OR ', $o) : '');
}
function find($inFindCol, $inFind='', $inOb='', $inFindFrom=0, $inFindForward=true, $inFullText=true) {
global $table;
// build order by clause
$desc = (boolean)getPostString("desc");
if (!$inOb)
$inOb = getOrderBy();
if ($inOb)
$inOb = "`" . $inOb . "`" ;
$orderby = ($inOb ? " ORDER BY $inOb " . ($desc ? " DESC" : "") : "");
// update inputs from post
if (!$inFind)
$inFind = getPostString('findText');
if (!$inFindCol)
$inFindCol = getPostString('findCol');
if (empty($inFindFrom))
$inFindFrom = getPostString('findFrom');
$ff = getPostString('findForward');
if ($ff)
$inFindForward = (strtolower($ff) == 'true' ? true : false);
$ft = getPostString('findFullText');
if ($ft)
$inFullText = (strtolower($ft) == 'true' ? true : false);
// get find data
$f = findData($inFindCol, $inFind, $orderby, $inFullText);
$pk = getPk();
// execute query
$where = buildFindWhere($f, $pk->index, 'f');
$query = "SELECT Row, f FROM (SELECT @row := @row + 1 AS Row, $pk->name as f FROM `$table` $orderby) AS tempTable $where";
mysql_query('SET @row = -1;');
if (!$result = mysql_query($query))
error("failed to perform query: $query. " . mysql_error());
// return row number
return findRow(getArray($result), $inFindFrom, $inFindForward);
}
// our command list
$cmds = array(
"count" => "rowcount",
"select" => "select",
"update" => "update",
"insert" => "insert",
"delete" => "delete",
"find" => "find",
"databases" => "getDatabases",
"tables" => "getTables",
"columns" => "getColumns",
"info" => "getTableInfo"
);
// process input params
$cmd = @$_POST["command"];
//$cmd="select";
// dispatch command
$func = @$cmds[$cmd];
if (function_exists($func))
echoJson(call_user_func($func));
else
error("bad command");
?>

File Metadata

Mime Type
text/x-php
Expires
Sat, Apr 26, 09:56 (1 d, 19 h)
Storage Engine
blob
Storage Format
Raw Data
Storage Handle
27547
Default Alt Text
data.php (9 KB)

Event Timeline