<?php
require_once('mysql_connection.php');
/**
* mySQL_Controller
* extends mySQL_Connection
*
* This class allows you to easily control database queries.
*
* Public Functions: retrieve, insert, update, delete
* Private Functions: _get, _combine
*
* @author Patrik Freij
**/
class mySQL_Controller extends mySQL_Connection {
// Used to retrieve table data in a database.
// Input: tablename,
// params as array (ex: "'x' => 'y'" gives "WHERE x=y"),
// options as array (ex. "'order' => 'x'" gives "ORDER BY x")
public function retrieve($table, array $params = NULL, array $options = NULL) {
if(!isset($options['columns'])) {
$options['columns'] = '*';
}
$query_string = "SELECT {$options['columns']} FROM {$table}";
if(!empty($params)) {
$query_string .= " WHERE {$this->_combine($params, ' AND ')}";
}
if(isset($options['order'])) {
$query_string .= " ORDER BY {$options['order']} ";
}
if(isset($options['desc'])) {
$query_string .= " DESC ";
}
if(isset($options['limit'])) {
$query_string .= " LIMIT {$options['limit']} ";
}
return $this->query($query_string);
}
// Used to insert data into a table.
// Input: tablename,
// values as array (ex. "x => y" adds value "y" to field "x")
// params as array (ex: "'x' => 'y'" gives "WHERE 'x'='y'"),
// options as array (ex. "'limit' => 'x'" gives "LIMIT x")
public function insert($table, array $values, array $params = NULL, array $options = NULL) {
$query_string = "INSERT INTO {$table}({$this->_get($values, false)}) VALUES({$this->_get($values, true)})";
if(!empty($params)) {
$query_string .= " WHERE {$this->_combine($params, ' AND ')}";
}
if(isset($options['limit'])) {
$query_string .= " LIMIT {$options['limit']} ";
}
return $this->query($query_string);
}
// Used to update data in a table.
// Input: tablename,
// values as array (ex. "x => y" updates field "x" to "y"),
// params as array (ex: "'x' => 'y'" gives "WHERE 'x'='y'"),
// options as array (ex. "'limit' => 'x'" gives "LIMIT x")
public function update($table, array $values, array $params = NULL, array $options = NULL) {
$query_string = "UPDATE {$table} SET {$this->_combine($values)}";
if(!empty($params)) {
$query_string .= " WHERE {$this->_combine($params, ' AND ')}";
}
if(isset($options['limit'])) {
$query_string .= " LIMIT {$options['limit']} ";
}
return $this->query($query_string);
}
// Used to remove data from a table.
// Input: tablename,
// params as array (ex: "'x' => 'y'" gives "WHERE 'x'='y'"),
// options as array (ex. "'limit' => 'x'" gives "LIMIT x")
public function delete($table, array $params = NULL, array $options = NULL) {
$query_string = "DELETE FROM {$table}";
if(!empty($params)) {
$query_string .= " WHERE {$this->_combine($params, ' AND ')}";
}
if(isset($options['limit'])) {
$query_string .= " LIMIT {$options['limit']} ";
}
return $this->query($query_string);
}
// Used to combine values in an array into a long string. Separate with a , by default.
// Input: values as array,
// separator (default: ,)
// Example output: "x_1 = y_1, x_2 = y_2, ..., x_n = y_n"
private function _combine(array $values, $separator = ',', $str = NULL) {
for($i = 0; $i < sizeof($values); $i++) {
if($i > 0 && each($values)) {
$str .= $separator;
}
$str .= $this->sanitize(key($values))." = '".$this->sanitize(current($values))."'";
}
return $str;
}
// Used to get parent or child values of an array
// (You want to take out the parent or the child from an array? ex. "'x' => 'y'" where as x=parent, y=child)
// Input: values as array,
// child as boolean (default: false)
// Example output: "x_1,x_2,...,x_n"
private function _get(array $values, $child = false, $separator = ',', $str = NULL) {
for($i = 0; $i < sizeof($values); $i++) {
if($i > 0 && each($values)) {
$str .= $separator;
}
$str .= ($child) ? "'".$this->sanitize(current($values))."'" : $this->sanitize(key($values));
}
return $str;
}
}
// Example code
/*$_db = new mySQL_Controller('localhost','root','','test','mysalt');
//$tests = $_db->insert('test',array('i' => '1'));
//$tests = $_db->update('test',array('i' => '2'),array('i' => '1'),array('limit' => '1'));
//$tests = $_db->delete('test',array('i' => '2'),array('limit' => '1'));
$tests = $_db->retrieve('test',NULL,array('order' => 'i'));
while($test = mysql_fetch_assoc($tests))
{
echo $test['i'];
}*/
?>