Return to Snippet

Revision: 24189
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