Database Query Service [AngularJS]

api.php

<?php

$tables = array('settings', 'people', 'ratings', 'emails', 'comments', 'accounts');
define('ENTRY_LIMIT', 50);

/**
 * method
 * The operation you are about to perform
 * 
 * table
 * The table you are about to perform the actions on
 * 
 * columns (optional)
 * An array of column names for use in select queries
 * Not passing it in will result to all columns (*)
 * 
 * where (optional)
 * An array of conditions
 * Required for delete
 * 
 * values
 * An array of row values for use in insert/update queries
 */

// Requires medoo framework (http://medoo.in)
require_once 'medoo.min.php';

// Connect to db
global $db;
$db = new medoo(array(
	'database_type' => 'mysql',
	'database_name' => '',
	'server' => 'localhost',
	'username' => '',
	'password' => '',
	'charset' => 'utf8'
));

$methods = array('select', 'get', 'insert', 'update', 'delete', 'count');

// Get the method
if (!isset($_REQUEST['method'])) {
	die(json_encode(array('status' => 'error', 'message' => 'No method set')));
}
$method = $_REQUEST['method'];
if (!in_array($method, $methods)) {
	die(json_encode(array('status' => 'error', 'message' => 'Invalid method')));
}

// Get the table
if (!isset($_REQUEST['table'])) {
	die(json_encode(array('status' => 'error', 'message' => 'No table set')));
}
$table = $_REQUEST['table'];
if (!in_array($table, $tables)) {
	die(json_encode(array('status' => 'error', 'message' => 'Invalid table')));
}

// Get the columns to select
$columns = '*';
if (($method == 'select' || $method == 'get') && isset($_REQUEST['columns'])) {
	if (!is_array($_REQUEST['columns'])) {
		die(json_encode(array('status' => 'error', 'message' => 'Invalid column format')));
	}
	$columns = $_REQUEST['columns'];
}

// Get the where query
$where = false;
if ($method != 'insert' && isset($_REQUEST['where'])) {
	if (!is_array($_REQUEST['where'])) {
		die(json_encode(array('status' => 'error', 'message' => 'Invalid where format')));
	}
	$where = array('AND' => $_REQUEST['where']);
}

// Get the values to insert/update
if (($method == 'insert' || $method == 'update')) {
	if (!isset($_REQUEST['values'])) {
		die(json_encode(array('status' => 'error', 'message' => 'No values set')));
	}
	$values = $_REQUEST['values'];
}

// Perform query
$now = new DateTime();
$now = $now->format('Y-m-d H:i:s');
switch ($method) {
	case 'select':
		if ($where) {
			$where['LIMIT'] = ENTRY_LIMIT;
			$result = $db->select($table, $columns, $where);
		} else {
			$result = $db->select($table, $columns);
		}
		break;

	case 'get':
		$result = $where ? $db->get($table, $columns, $where) : $db->get($table, $columns);
		break;

	case 'insert':
		$result = $db->insert($table, $values);
		break;

	case 'update':
		$result = $where ? $db->update($table, $values, $where) : $db->update($table, $values);
		break;

	case 'delete':
		if (!$where) {
			die(json_encode(array('status' => 'error', 'message' => 'No where query set')));
		}
		$result = $db->delete($table, $where);
		break;

	case 'count':
		$result = $where ? $db->count($table, $where) : $db->count($table);
		break;

	default:
		$result = null;
}

$error = $db->error();
if (!empty($error[2])) {
	die(json_encode(array('status' => 'error', 'data' => $error[2])));
}

die(json_encode(array('status' => 'ok', 'data' => $result)));

query.js

app.factory('Query', function($http)
{
	var Query = function()
	{
		this.data = {};
		this.apiUrl = 'http://' + window.location.host + '/api.php';
	}

	Query.prototype.from = function(table) {
		this.data.table = table;
		return this;
	}

	Query.prototype.into = function(table) {
		return this.from(table);
	}

	Query.prototype.select = function(columns) {
		this.data.method = 'select';
		if (columns !== undefined) {
			this.data.columns = columns;
		}
		return this;
	}

	Query.prototype.get = function(columns) {
		this.data.method = 'get';
		if (columns !== undefined) {
			this.data.columns = columns;
		}
		return this;
	}

	Query.prototype.insert = function(values) {
		this.data.method = 'insert';
		this.data.values = values;
		return this;
	}

	Query.prototype.update = function(values) {
		this.data.method = 'update';
		this.data.values = values;
		return this;
	}

	Query.prototype.delete = function() {
		this.data.method = 'delete';
		return this;
	}

	Query.prototype.count = function() {
		this.data.method = 'count';
		return this;
	}

	Query.prototype.where = function(where) {
		this.data.where = where;
		return this;
	}

	Query.prototype.perform = function()
	{
		if (this.data.columns !== undefined) {
			var columns = new Array();
			for (key in this.data.columns) {
				columns.push('columns[]=' + encodeURIComponent(this.data.columns[key]))
			}
			delete this.data.columns;
		}

		if (this.data.where !== undefined) {
			var where = new Array();
			for (key in this.data.where) {
				where.push('where[' + key + ']=' + encodeURIComponent(this.data.where[key]))
			}
			delete this.data.where;
		}

		if (this.data.values !== undefined) {
			var values = new Array();
			for (key in this.data.values) {
				values.push('values[' + key + ']=' + encodeURIComponent(this.data.values[key]))
			}
			delete this.data.values;
		}

		var request = new Array();
		for (key in this.data) {
			request.push(key + '=' + encodeURIComponent(this.data[key]));
		}

		if (columns !== undefined) {
			request = request.concat(columns);
		}

		if (where !== undefined) {
			request = request.concat(where);
		}

		if (values !== undefined) {
			request = request.concat(values);
		}

		this.data = {};

		return $http({
			method: 'POST',
			url: this.apiUrl,
			data: request.join('&'),
			headers: {'Content-Type': 'application/x-www-form-urlencoded'}
		});
	}

	return Query;
});

This looks great! Could you kindly provide a quick demo on how to use factory Query, particularly WHERE clauses with LIKE? Much appreciated!

@zhiboz - I don't think this actually handles the LIKE operator. You'd have to hack in a bit here to support it in the WHERE call. You'll also have to look into medoo's where API.