Revision: 24189
Initial Code
Initial URL
Initial Description
Initial Title
Initial Tags
Initial Language
at February 23, 2010 01:43 by jonniespratley
Initial Code
<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', 'fred');
class SuperDatabaseAccess {
/**
* @var
*/
public $apiDatabase;
/**
* @var
*/
public $apiMaxCount = 200;
/**
* @var
*/
public $apiUseKey = false;
/**
* @var
*/
public $apiUseThrottle;
/**
* @var
*/
public $apiThrottleMax;
/**
* @var
*/
public $apiResultFormat;
/**
* @var
*/
public $svcDatabase;
/**
* @var
*/
public $svcTable;
/**
* @var
*/
public $svcType;
/**
* @var
*/
private $dsn = '';
/**
* @var
*/
private $dbh = null;
private $callResultsPerPage = 0;
private $callResultsCurrentPage = 0;
private $callResultsOffset = 0;
private $callResultsTotalPages = 0;
private $callResultsTotal = 0;
/**
*
* @param object $dbType [optional]
* @param object $dbHost [optional]
* @param object $dbPort [optional]
* @param object $dbUser [optional]
* @param object $dbPass [optional]
* @return
*/
public function __construct($dbType = 'mysql', $dbHost = 'localhost', $dbPort = 3306, $dbUser = null, $dbPass = null) {
if ($dbType == 'mysql') {
$this->svcType = 'mysql';
$this->dsn = 'mysql:host='.$dbHost.'';
$this->dsn .= $dbPort != null ? ':'.$dbPort.';' : ';';
} else if ($dbType == 'sqlite') {
$this->svcType = 'sqlite';
$this->dsn = 'sqlite2:'.$dbName.'.db';
}
try {
$this->dbh = new PDO($this->dsn, $dbUser, $dbPass);
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
echo 'Connection failed: '.$e->getMessage();
die();
}
}
/**
*
* @param string $mode
* @param string $database
* @param string $table [optional]
* @param string $sql [optional]
* @param array $vo [optional]
* @return
*/
public function execute($mode, $database, $table = null, $sql = null, $vo = null) {
try {
$result = array();
$return = null;
//Function management variables
$voColumns = '';
$voBindColumns = '';
$voKeyValue = '';
$voValues = array();
$voPrimaryKey = null;
/**
* Check if we are returning data
* @var
*/
if ($mode == 'select' || $mode == 'search') {
$statement = $this->dbh->prepare($sql);
$statement->execute();
$records = array();
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$records[] = $row;
}
$result = array(
'status'=>'success',
'database'=>$database,
'table'=>$table,
'count'=>$statement->rowCount(),
'page'=>$this->callResultsCurrentPage,
'pages' => $this->callResultsTotalPages,
'total' => $this->callResultsTotal,
'data'=>$records);
$return[] = $result;
//Check if its an insert
} else if ($mode == 'insert') {
//Make sure there is assoc array for vo
if ($vo != null) {
//Loop each key=value for the vo
foreach ($vo as $column=>$value) {
$voColumns .= $column.', ';
//build the bind params
$voBindColumns .= '?, ';
//push the values to a value array
array_push($voValues, $value);
}
//Trim the strings
$voColumns = $this->trimSQL($voColumns);
$voBindColumns = $this->trimSQL($voBindColumns);
//Build the sql
$sql = "INSERT INTO $database.$table ( $voColumns ) VALUES ( $voBindColumns )";
//log it
# $this->writeLog('SQL', $sql);
//Prepare database with the ? placeholders
$statement = $this->dbh->prepare($sql);
//If the query executed properly
if ($statement->execute($voValues)) {
//Prep the result to return
$result = array('status'=>'success', 'database'=>$database, 'table'=>$table, 'count' => 1, 'data'=>array('insertid'=>$this->dbh->lastInsertId()));
$return[] = $result;
}
}
/**
* Update
* @var
*/
} else if ($mode == 'update') {
if ($vo != null) {
$voPrimaryKey = $this->getKey($database, $table);
if (array_key_exists($voPrimaryKey, $vo)) {
$voKeyValue = $vo[$voPrimaryKey];
}
foreach ($vo as $column=>$value) {
$voBindColumns .= $column.'= ?, ';
array_push($voValues, $value);
}
array_push($voValues, $voKeyValue);
$voBindColumns = $this->trimSQL($voBindColumns);
$sql = "UPDATE $database.$table SET $voBindColumns WHERE $voPrimaryKey = ?";
$statement = $this->dbh->prepare($sql);
if ($statement->execute($voValues)) {
$result = array('status'=>'success', 'database'=>$database, 'table'=>$table, 'count' => 1, 'data'=>array('updateid'=>$voKeyValue));
$return[] = $result;
}
}
/**
* delete
* @var
*/
} else if ($mode == 'delete') {
if ($vo != null) {
$voPrimaryKey = $this->getKey($database, $table);
$voKeyValue = '';
if (array_key_exists($voPrimaryKey, $vo)) {
$voKeyValue = $vo[$voPrimaryKey];
}
$sql = "DELETE FROM $database.$table WHERE $voPrimaryKey = ?";
# $this->writeLog('SQL', $sql);
$statement = $this->dbh->prepare($sql);
if ($statement->execute(array($voKeyValue))) {
$result = array('status'=>'success', 'database'=>$database, 'table'=>$table, 'count' => 1,'data'=>array('deleteid'=>$voKeyValue));
$return[] = $result;
}
}
}
}
catch(PDOException $e) {
$result = array('status'=>'error', 'message'=>$e->getMessage());
$return[] = $result;
}
return $return;
}
/**
* I make a select statement and return the results.
*
* @param string $database
* @param string $table
* @param string $columns [optional]
* @param number $count [optional]
* @param number $page [optional]
* @param string $sort [optional]
* @return
*/
public function select($database, $table, $columns = "*", $count = 25, $page = 0, $sort = null) {
$this->callResultsTotal = $this->count($database, $table);
$this->callResultsCurrentPage = $page;
$this->callResultsPerPage = $count;
$this->callResultsTotalPages = ceil($this->callResultsTotal / $this->callResultsPerPage);
if ($page > 0){
$this->callResultsOffset = ($this->callResultsPerPage * $this->callResultsCurrentPage - $this->callResultsPerPage );
} else if ($page == 1){
$this->callResultsOffset = 0;
}
$sorting = ($sort != null) ? "ORDER BY $sort" : "";
$count = ($count >= $this->apiMaxCount) ? $this->apiMaxCount : $count;
$counting = 'LIMIT '.$this->callResultsOffset.', '. $this->callResultsPerPage.';';
$sql = "SELECT $columns FROM $database.$table $sorting $counting";
return $this->execute('select', $database, $table, $sql);
}
/**
*
* @param object $database
* @param object $table
* @param object $vo
* @return
*/
public function selectOne($database, $table, $keyvalue) {
$primarykey = $this->getKey($database, $table);
#$primarykeyValue = $vo[$primarykey];
$sql = "SELECT * FROM $database.$table WHERE $primarykey = $keyvalue LIMIT 1";
$this->writeLog('SQL-selectOne', $sql);
return $this->executeAndReturn($sql);
}
/**
*
* @param object $database
* @param object $table
* @param object $columns [optional]
* @param object $where [optional]
* @param object $query [optional]
* @param object $count [optional]
* @param object $page [optional]
* @param object $sort [optional]
* @return
*/
public function search($database, $table, $columns = "*", $where = null, $query = null, $count = 25, $page = 0, $sort = null) {
$this->callResultsTotal = $this->count($database, $table);
$this->callResultsCurrentPage = $page;
$this->callResultsPerPage = $count;
$this->callResultsTotalPages = ceil($this->callResultsTotal / $this->callResultsPerPage);
if ($page > 1){
$this->callResultsOffset = ($this->callResultsPerPage * $this->callResultsCurrentPage - $this->callResultsPerPage );
} else if ($page == 1){
$this->callResultsOffset = 0;
}
$sorting = ($sort != null) ? "ORDER BY $sort" : "";
$counting = 'LIMIT '.$this->callResultsOffset.', '. $this->callResultsPerPage.';';
$sql = "SELECT $columns FROM $database.$table WHERE $where LIKE '%$query%' $sorting $counting";
$this->writeLog('SQL-search', $sql);
return $this->execute('search', $database, $table, $sql);
}
public function count($database, $table){
$sql = "SELECT COUNT(*) FROM $database.$table";
$result = $this->executeAndReturn($sql);
$this->writeLog('SQL-count', $sql);
return $result[0]['COUNT(*)'];
}
/**
*
* @param object $database
* @param object $table
* @param object $vo
* @return
*/
public function save($database, $table, $vo) {
$primarykey = $this->getKey($database, $table);
$primarykeyValue = $vo[$primarykey];
$choice = '';
if ($primarykeyValue == 0 || $primarykeyValue == '') {
$choice = $this->create($database, $table, $vo);
} else {
$choice = $this->update($database, $table, $vo);
}
return $choice;
}
/**
*
* @param object $database
* @param object $table
* @param object $vo
* @return
*/
public function update($database, $table, $vo) {
return $this->execute('update', $database, $table, null, $vo);
}
/**
*
* @param object $database
* @param object $table
* @param object $vo
* @return
*/
public function create($database, $table, $vo) {
return $this->execute('insert', $database, $table, null, $vo);
}
/**
*
* @param object $database
* @param object $table
* @param object $vo
* @return
*/
public function remove($database, $table, $vo) {
return $this->execute('delete', $database, $table, null, $vo);
}
/**
*
* @param object $database
* @param object $table
* @return
*/
public function getKey($database, $table) {
if ($this->svcType == 'mysql') {
$sql = "SHOW INDEX FROM $database.$table";
$keys = $this->executeAndReturn($sql);
$primaryKey = '';
if ($keys) {
//TODO: Find a alt to tables not having a key
foreach ($keys as $key) {
if ($key['Key_name'] == 'PRIMARY') {
$primaryKey = $key['Column_name'];
}
}
$this->writeLog('SQL-getKey', $sql);
return $primaryKey;
}
}
}
/**
* I get the databases in the mysql server
*
* @return [array] - Tree ready array of database, tables, and fields
*/
public function getDatabases() {
if ($this->svcType == 'mysql') {
$sql = "SHOW DATABASES";
$databases = array();
$statement = $this->dbh->prepare($sql);
$statement->execute();
while ($database = $statement->fetch(PDO::FETCH_ASSOC)) {
$tables = array();
foreach ($database as $data) {
$tables = $this->getTables($data);
}
$databases[] = array("label"=>$data, "type"=>"database", "children"=>$tables);
}
$this->writeLog('SQL-getDatabases', $sql);
return $databases;
}
}
/**
* I get the tables, fields, and information about the tables from the database.
*
* @param [string] $database
* @return array
*/
public function getTables($whatDatabase) {
if ($this->svcType == 'mysql') {
$tables = array();
$sql = "SHOW TABLES FROM $whatDatabase";
$statement = $this->dbh->prepare($sql);
$statement->execute();
while ($table = $statement->fetch(PDO::FETCH_ASSOC)) {
$fields = array();
$indexes = array();
foreach ($table as $t_key=>$t_value) {
$fields = $this->describeTable($whatDatabase, $t_value);
$primaryKey = $this->getKey($whatDatabase, $t_value);
}
$tables[] = array("label"=>$t_value, "type"=>'table', "key"=>$primaryKey, "children"=>$fields);
}
sort($tables);
$this->writeLog('SQL-getDatabases', $sql);
return $tables;
}
}
/**
* I describe a table for the getDatabasesAndTables() method
*
* @param [string] $database the database
* @param [string] $table the table
* @return [array]
*/
public function describeTable($whatDatabase, $whatTable) {
$sql = "DESCRIBE $whatDatabase.$whatTable";
$statement = $this->dbh->prepare($sql);
$statement->execute();
$tables = array();
$pattern = '/(\(\d*\))/';
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$field = $row['Field']; //Field Name
$type = $row['Type']; //Field Type
preg_match($pattern, $type, $matches);
#$typeOpts = $matches[0];
#$type = str_replace( $type, $matches[0] );
//TODO: Fix these values
$default = $row['Default']; //Field Default Value
$nullable = ($row['Null'] == 'YES') ? 'true' : 'false';
$tables[] = array('label'=>$field, 'type'=>'field', 'fieldDefault'=>$default, 'fieldType'=>$type, 'fieldLength'=>'', 'fieldNullable'=>$nullable);
}
$this->writeLog('SQL-getDatabases', $sql);
return $tables;
}
/**
*
* @param object $sql
* @return
*/
private function executeAndReturn($sql) {
try {
$statement = $this->dbh->prepare($sql);
$statement->execute();
$result = array();
$records = array();
while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
$records[] = $row;
}
$result = $records;
if (count($result) <= 0) {
$result = false;
}
}
catch(PDOException $e) {
$result = false;
}
return $result;
}
/**
*
* @param object $obj
* @return
*/
private function mapObject($obj) {
require_once "ValueObject.php";
$array = array();
while ($row = mysqli_fetch_assoc($obj)) {
$vo = new ValueObject();
foreach ($row as $key=>$value) {
$vo->__set($key, $value);
}
$array[] = $vo;
}
return $array;
}
/**
*
* @param object $value
* @return
*/
private function escape($value) {
$escaped = $value;
if (!is_numeric($value)) {
$escaped = $this->dbh->quote(trim($value));
}
return $escaped;
}
/**
*
* @param object $sql
* @return
*/
private function trimSQL($sql) {
return substr($sql, 0, strlen($sql) - 2);
}
/**
*
* @param object $type
* @param object $var
* @return
*/
public function writeLog($type, $var) {
$file = "/tmp/SuperDatabaseAccess.log";
$fp = fopen($file, "a+");
$date = date('[D M j Y H:i:s] ', mktime());
$contents = "\n".$date.'['.$type.'] '.$var;
fwrite($fp, $contents);
fclose($fp);
}
/**
*
* @param object $prefix
* @return
*/
public function generateKey($prefix) {
$hash = uniqid($prefix);
return $hash;
}
/**
*
* @param object $key
* @return
*/
public function checkKey($key) {
$return = false;
$sql = "SELECT * FROM ".$this->apiDatabase.".keys WHERE apikey = '$key'";
$result = $this->executeAndReturn($sql);
if ($result) {
$return = true;
}
return $return;
}
/**
*
* @param object $prefix
* @param object $userid
* @return
*/
public function updateKey($prefix, $userid) {
$return = false;
$key = $this->generateKey($prefix);
$sql = "UPDATE ".$this->apiDatabase.".keys SET apikey = '$key' WHERE user_id = $userid";
$result = $this->executeAndReturn($sql);
if ($result) {
$return = true;
}
return $return;
}
}
/* *************************
* Testing
* ************************
echo '<pre>';
$svc = new DatabaseAccess('mysql', 'localhost', null, 'root', 'fred');
$svc->apiDatabase = 'restapi';
$svc->apiMaxCount = 50;
$svc->apiUseKey = false;
$svc->apiResultFormat = 'json';
$prefix = 'api-';
$email = '[email protected]';
$newkey = $svc->generateKey($prefix);
$oldkey = 'api-4ab5f0b7091f5';
$validKey = $svc->checkKey($oldkey);
$updateKey = $svc->updateKey($prefix, '2');
echo '<h3>Insert Record</h3>';
$newrecord = array('id'=>0, 'name'=>'New Work Schedule', 'color'=>'Green', 'user_id'=>7);
$create = $svc->create('test', 'calendars', $newrecord);
print_r($create);
echo "<br/>";
echo '<h3>Update Record</h3>';
$oldrecord = array('id'=>2, 'name'=>'Old Work Schedule', 'color'=>'Blue', 'user_id'=>3);
$update = $svc->update('test', 'calendars', $oldrecord);
print_r($update);
echo "<br/>";
echo '<h3>Delete Record</h3>';
$removeKey = ($create['response']['insertid'] - 1);
$delete = $svc->remove('test', 'calendars', array('id'=>$removeKey));
print_r($delete);
echo "<br/>";
echo '<h3>Get Records</h3>';
$getrecord = $svc->select('test', 'posts', '*', null, 2);
print_r($getrecord);
echo "<br/>";
echo '<h3>Search Records</h3>';
$searchrecord = $svc->search('test', 'addresses', '*', 'state', 'ca', 15, 0, 'id asc');
print_r($searchrecord);
echo "<br/>";
echo '<h3>Old API Key</h3>';
echo $oldkey;
echo "<br/>";
echo '<h3>New API Key</h3>';
echo $newkey;
echo "<br/>";
echo '<h3>Update API Key</h3>';
var_dump($updateKey);
echo "<br/>";
echo '<h3>Valid Key Check</h3>';
var_dump($validKey);
echo "<br/>";
echo '</pre>';
*/
/**
* @author
* @version
* @license
* @classDescription - This class can be used many ways, as a rpc for Flex/Flash application using either http or http remoting
* Here is examples on testing this class to make sure its working.
*
* HTTP:
* url: http://rest/SuperDatabaseService.php?action=select&database=test&table=users&format=xml
*
* Output Formats:
* Here is the supported output formats.
* 1. json - [{"status":"success","database":"test","table":"users","count":1,"page":1,"pages":5000,"total":"5000","data":[{"id":"1","username":"[email protected]","created":"2010-01-11 00:43:58"}]}]
* 2. php - a:1:{i:0;a:8:{s:6:"status";s:7:"success";s:8:"database";s:4:"test";s:5:"table";s:5:"users";s:5:"count";i:1;s:4:"page";i:1;s:5:"pages";d:5000;s:5:"total";s:4:"5000";s:4:"data";a:1:{i:0;a:3:{s:2:"id";s:1:"1";s:8:"username";s:27:"[email protected]";s:7:"created";s:19:"2010-01-11 00:43:58";}}}}
* 3. xml -
<code>
<?xml version="1.0"?>
<results
status="success"
database="test"
table="users"
pages="5000"
page="1"
total="5000"
count="1">
<row number="1">
<id>1</id>
<username>[email protected]</username>
<created>2010-01-11 00:43:58</created>
</row>
</results>
</code>
* 4. byte array
*
*
* @projectDescription
* @copyright
*/
class SuperDatabaseService {
private $service;
public $svcResultFormat;
public $svcDatabase;
public $svcTable;
/**
* I am a simple service class for multiplating a mysql database. Either through amfphp or javascript.
* For returning json just set the resultFormat = 'json'. Then you should be all set. Be sure to
* enter your credentials in the constructor of this class.
* @return
*/
public function __construct() {
//TODO: Credentials here
#$this->service = new SuperDatabaseAccess('mysql', 'localhost', null, 'root', 'fred');
$this->service = new SuperDatabaseAccess('mysql', DB_HOST, null, DB_USER, DB_PASS);
}
/**
* I return all the databases on the server with information about the contents.
* @return [array] - Tree ready array holding all databases with child nodes as tables, and table fields as child nodes.
*/
public function getDatabases() {
$args = array();
return $this->returnResult( 'getDatabases', $args, $this->service->getDatabases());
}
/**
* I return all records from the specified database/table.
* @param [string] - $database - The database name.
* @param [string] - $table - The table name.
* @return [array] - All records from table.
*/
public function select($database, $table, $columns = "*", $count = 25, $page = 0, $sort = null) {
$this->svcDatabase = $database;
$this->svcTable = $table;
$args = array('columns' => $columns, 'count' => $count, 'page' => $page, 'sort' => $sort);
return $this->returnResult( 'get', $args, $this->service->select($database, $table, $columns, $count, $page, $sort));
}
public function one($database, $table, $keyvalue){
return $this->returnResult('get', null, $this->service->selectOne($database, $table, $keyvalue));
}
/**
* I create/update a record in the databases table.
* @param [string] - $database - The database name.
* @param [string] - $table - The table name.
* @param [object] - $vo - Assoc array containing all name/value for the object.
* This can be post fields where the key are the column names and the value is the new/edited values.
* If the table does not have a primary key then there is a problem and will not be saved.
* For testing in amfphp create your object like this. (ex. For updating: {"id":"4", "tag":"Flex"}) (ex. For creating: {"id":"0", "tag":"Flex"} )
* @return [object] - The object that was sent if the call was successfull or false if it was not.
*/
public function save($database, $table, $vo) {
$this->svcDatabase = $database;
$this->svcTable = $table;
$args = $vo;
return $this->returnResult( 'save', $args, $this->service->save($database, $table, $vo));
}
/**
* I create a record in the databases table.
* @param [string] - $database - The database name.
* @param [string] - $table - The table name.
* @param [object] - $vo - Assoc array containing all name/value for the object.
* This can be post fields where the key are the column names and the value is the new/edited values.
* If the table does not have a primary key then there is a problem and will not be saved.
* For testing in amfphp create your object like this. (ex. For creating: {"id":"0", "tag":"Flex"} )
* @return [object] - The object that was sent if the call was successfull or false if it was not.
*/
public function create($database, $table, $vo) {
$this->svcDatabase = $database;
$this->svcTable = $table;
$args = $vo;
return $this->returnResult( 'create', $args, $this->service->create($database, $table, $vo));
}
/**
* I update a record in the databases table.
*
* @param [string] - $database - The database name.
* @param [string] - $table - The table name.
* @param [object] - $vo - Assoc array containing all name/value for the object.
* This can be post fields where the key are the column names and the value is the new/edited values.
* If the table does not have a primary key then there is a problem and will not be saved.
* For testing in amfphp create your object like this. (ex. For updating: {"id":"4", "tag":"Flex"})
* @return [object] - The object that was sent if the call was successfull or false if it was not.
*/
public function update($database, $table, $vo) {
$this->svcDatabase = $database;
$this->svcTable = $table;
$args = $vo;
return $this->returnResult( 'update', $args, $this->service->update($database, $table, $vo));
}
/**
* I return the number of records in the specified table.
*
* @param object $database
* @param object $table
* @return
*/
public function count($database, $table){
$args = array();
return $this->returnResult('count', $args, $this->service->count($database, $table));
}
/**
* I remove a record from the database/table.
*
* @param [string] - $database - The database name.
* @param [string] - $table - The table name.
* @param [object] - $vo - Assoc array containing name/value pairs, for deleting to be successful send the primary key as the key and the record id value
* to delete. (ex. Amfphp: {"id":"4"} )
* @return - [bool] - true or false of the call
*/
public function remove($database, $table, $vo) {
$this->svcDatabase = $database;
$this->svcTable = $table;
$args = $vo;
return $this->returnResult( 'search', $args, $this->service->remove($database, $table, $vo));
}
/**
* I get all of the tables with information about them from the specified database.
*
* @param [string] - $database - The database name.
* @return [array] - All tables with information
*/
public function getTables($database) {
$this->svcDatabase = $database;
$args = array('database' => $database);
return $this->returnResult( 'getTables', $args, $this->service->getTables($database));
}
/**
* I search any table/column that is specified in the paramenters.
*
* @param [string] - $database - the database
* @param [string] - $table - the table
* @param [array] - $columns [optional] - array of columns to select
* @param [string] - $where [optional] - condition to search
* @param [string] - $query [optional]
* @param [number] - $count [optional]
* @param [number] - $page [optional]
* @param [string] - $sort [optional]
* @return
*/
public function search($database, $table, $columns = "*", $where = null, $query = null, $count = 25, $page = 0, $sort = null) {
$this->svcDatabase = $database;
$this->svcTable = $table;
$args = array('columns' => $columns, 'where' => $where, 'query' => $query, 'count' => $count, 'page' => $page, 'sort' => $sort);
return $this->returnResult( 'search', $args, $this->service->search($database, $table, $columns, $where, $query, $count, $page, $sort));
}
/**
*
* @return
*/
public function readLog() {
$args = array();
return $this->returnResult( 'readLog', $args, $this->service->readLog());
}
public function writeLog($title, $var) {
$args = array();
return $this->returnResult( 'writeLog', $args, $this->service->writeLog($title, $var));
}
/**
* I return the result from the call.
* Based on the result format specified in the
* url's extension.
*
* @param object $call - not used?
* @param object $args - array of arguments
* @param object $results - result array
* @return
*/
private function returnResult($call, $args, $results) {
$argCount = count($args);
if ('json' === $this->svcResultFormat) {
#header('Content-type: application/json');
header('Content-type: text/plain');
return json_encode($results);
} else if ('php' === $this->svcResultFormat) {
#header('Content-type: application/x-httpd-php');
header('Content-type: text/plain');
return serialize($results);
} else if ('sql' === $this->svcResultFormat) {
header('Content-type: text/plain');
$sql = '';
foreach($results["data"] as $result) {
$sql .= 'INSERT INTO '.$this->svcDatabase.'.'.$this->svcTable.' VALUES ';
foreach ($result as $key=>$value) {
$sql .= $key.' = '.$value.', ';
}
$sql = substr($sql, 0, strlen($sql) - 2);
$sql .= ";\n";
}
return $sql;
} else if ('compressed' === $this->svcResultFormat){
#$postedData = file_get_contents( 'php://input' );
//uncompress this data
//$uncompressedPostedData = gzuncompress($postedData);
$compressed = gzcompress($results, 9);
return $compressed;
} else if ('xml' === $this->svcResultFormat) {
header('Content-type: text/xml; charset: utf-8;');
$xml = '<?xml version="1.0"?>';
$xml .= "\n";
$xml .= '<results
status="'.$results[0]["status"].'"
database="'.$results[0]["database"].'"
table="'.$results[0]["table"].'"
pages="'.$results[0]["pages"].'"
page="'.$results[0]["page"].'"
total="'.$results[0]["total"].'"
count="'.$results[0]["count"].'">';
if($results[0]["data"]){
$resultsData = $results[0]["data"];
#$xml .= "\n";
if ($call === 'get' || $call === 'search'){
for ($i = 0; $i < count($resultsData); $i++) {
#$xml .= '<'.$results["table"].' row="'.($i + 1).'">';
$xml .= '<row number="'.($i + 1).'">';
#$xml .= "\n";
foreach ($resultsData[$i] as $key=>$value) {
$xml .= '<'.htmlentities(trim($key)).'>'.htmlentities(trim($value)).'</'.htmlentities(trim($key)).'>';
$xml .= "\n";
}
#$xml .= '</'.$results["table"].'>';
$xml .= "</row>";
$xml .= "\n";
}
} else {
foreach ($resultsData as $key=>$value) {
$xml .= '<'.htmlentities(trim($key)).'>'.htmlentities(trim($value)).'</'.htmlentities(trim($key)).'>';
$xml .= "\n";
}
}
}
$xml .= "\n";
$xml .= '</results>';
return $xml;
} else if('zlib' == $this->svcResultFormat){
$out = $results;
$out = 'zlib';
$rawStr = implode(',', $results[0]);
$out = base64_encode($rawStr);
return ;
}
}
}
switch($_SERVER['REQUEST_METHOD'])
{
case 'GET':
if (isset($_GET['action'])){
$database = isset($_GET['database']) ? $_GET['database'] : null; //defaults
$table = isset($_GET['table']) ? $_GET['table'] : null;//defaults
$columns = isset($_GET['columns']) ? $_GET['columns'] : '*';//defaults
$where = isset($_GET['where']) ? $_GET['where'] : null;//defaults
$count = isset($_GET['count']) ? $_GET['count'] : 25;//defaults
$page = isset($_GET['page']) ? $_GET['page'] : 1;//defaults
$sort = isset($_GET['sort']) ? $_GET['sort'] : null;//defaults
$resultFormat = isset($_GET['format']) ? $_GET['format'] : null;//defaults
$query = isset($_GET['q']) ? $_GET['q'] : null;//defaults
$data = isset($_GET) ? $_GET : array();
$svc = new SuperDatabaseService();
$svc->svcResultFormat = $resultFormat;
#print_r( $data );
switch($_GET['action']){
case 'select':
echo $svc->select($database, $table, $columns, $count, $page, $sort);
break;
case 'search':
echo $svc->search($database, $table, $columns, $where, $query, $count, $page, $sort);
break;
case 'getDatabases':
echo $svc->getDatabases();
break;
case 'getTables':
echo $svc->getTables($database);
break;
case 'getLog':
echo $svc->readLog();
break;
case 'count':
echo $svc->count($database, $table);
break;
default:
echo 'Please specify an action';
break;
}
}
break;
case 'POST':
if (isset($_POST['action'])){
$database = isset($_POST['database']) ? $_POST['database'] : '';
$table = isset($_POST['table']) ? $_POST['table'] : '';
$resultFormat = isset($_POST['format']) ? $_POST['format'] : '';
$data = isset($_POST) ? $_POST : array();
unset($data['database']);
unset($data['table']);
unset($data['format']);
unset($data['action']);
$svc = new SuperDatabaseService();
$svc->svcResultFormat = $resultFormat;
#print_r( $data );
switch($_POST['action']){
case 'save':
echo $svc->save($database, $table, $data);
break;
case 'create':
echo $svc->create($database, $table, $data);
break;
case 'update':
echo $svc->update($database, $table, $data);
break;
case 'delete':
echo $svc->remove($database, $table, $data);
break;
default:
echo 'Please specify an action';
break;
}
}
break;
}
?>
Initial URL
Initial Description
Initial Title
Super Database Access/Service
Initial Tags
mysql, php
Initial Language
PHP