<?php
/**
* define user credentials
*/
define('SERVER', 'server');
define('USER', 'mysql_user');
define('PASSWORD', 'user_password');
define('DATABASE', 'database');
/**
*
* @package: EasyQuery
* @author: a77icu5
* @link hide@address.com
*/
class EasyQuery
{
/**
* handler of the conection
*
* @access private
* @var integer $_con;
*/
private $_con = NULL;
/**
* sql statenment
*
* @access private
* @var string $_query
*/
private $_query = NULL;
/**
* string of the last query ejecuted
*
* @access private
* @var string $_myQquery
*/
private $_myQuery = NULL;
/**
* values to return
*
* @access private
* @var string $_values;
*/
private $_values = '*';
/**
* sql where condition
*
* @access private
* @var string $_where;
*/
private $_where = NULL;
/**
* sql order by option
*
* @access private
* @var string $_order;
*/
private $_order = NULL;
/**
* sql group by option
*
* @access private
* @var string $_group
*/
private $_group = NULL;
/**
* sql limit option
*
* @access private
* @var string $_limit;
*/
private $_limit = NULL;
/**
* sql inner join option
*
* @access private
* @var string $_join;
*/
private $_tableJoin = NULL;
/**
* result of the sql statenment
*
* @access private
* @var string $_result
*/
private $_result = NULL;
/**
* rows fetched returned by the query
*
* @access private
* @var array $_fetched;
*/
private $_fetched = array();
/**
* Start the conection with the credentials
* defined in the user config.php file
*
* @access public
* @return void
*/
public function __construct() {
$this->_con = $this->connect();
}
/**
* connect with mysql and select the databse to use
*
* @access public
* @return object
*/
public function connect() {
if (!$this->_con = mysql_connect(SERVER, USER, PASSWORD)) {
exit('We can\'t connect using provided credentials.');
}
if (!mysql_select_db(DATABASE, $this->_con)) {
exit('We can\'t select the database.');
}
return $this->_con;
}
/**
* initialize custom query
*
* @access public
* @param string $query
* @return object
*/
public function setQuery($query) {
$this->_query = trim($query);
return $this;
}
/**
* insert new rows in the database
*
* @access public
* @param string $table
* @param array $values
* @see method::scape
* @return void
*/
public function insert($table, $values) {
$this->_query = 'INSERT INTO ' . $table . ' ('
. implode(', ', array_keys($values))
.') VALUES(';
foreach ($values as $key => $value) {
$value = $value;
if(is_numeric($value)) {
$this->_query .= ', ' . $this->scape($value);
} else {
$this->_query .= ', \''. $this->scape($value) . '\'';
}
}
$this->_query = str_replace('(,', '(', $this->_query);
$this->_query .= ')';
$this->execute();
}
/**
* update records in the database
*
* @access public
* @param string $table
* @param array $values
* @see method::getWhere()
* @return void
*/
public function update($table, $values) {
$this->_query = 'UPDATE '. $table . ' SET ';
foreach ($values as $key => $value) {
$data .= $key . ' = ';
if(!is_numeric($value)) {
$data .= '\'' . $this->scape($value) . '\', ';
} else {
$data .= $this->scape($value) . ', ';
}
}
$this->_query .= $data;
$this->_query = substr($this->_query, 0, strlen($this->_query)-2) . $this->getWhere() . ' ' . $this->getLimit();
$this->execute();
}
/**
* delete records in the database
*
* @access public
* @param string $table
* @see method::getWhere()
* @return void
*/
public function delete($table) {
$this->_query = 'DELETE FROM ' . $table . ' ' . $this->getWhere() . ' ' . $this->getLimit();
$this->execute();
}
/**
* define the values to return
*
* @access public
* @param string $values
* @return void
*/
public function values($values = '*'){
$this->_values = $values;
return $this;
}
/**
* define the where condition, if the condition
* is already defined then create the 'and' option
* to define a second condition
*
* @access public
* @param string $key
* @param mixed $value
* @return void
*/
public function where($key, $value) {
if($this->_where != ''){
$this->_where .= ' AND ' . $key . ' = ';
if(!is_numeric($value)) {
$this->_where .= '\'' . $this->scape($value) .'\'';
} else {
$this->_where .= $this->scape($value);
}
} else {
$this->_where = ' WHERE ' . $key . ' = ';
if(!is_numeric($value)) {
$this->_where .= '\'' . $this->scape($value) . '\'';
} else {
$this->_where .= $this->scape($value);
}
}
return $this;
}
/**
* define the order by option
*
* @access public
* @param string $values
* @param string $type[optional]
* @return void
*/
public function order($values, $type = 'desc'){
$this->_order = ' ORDER BY ' . $values . ' ' . $type;
return $this;
}
/**
* define the group by option
*
* @access public
* @param string $values
* @return void
*/
public function group($values){
$this->_group = ' GROUP BY ' . $values;
return $this;
}
/**
* define the join option, if join is already defined
* then create another join in the query
*
* @access public
* @param string $table
* @param string $condition
* @param string $type
* @return void
*/
public function tableJoin($table, $condition, $type) {
if($this->_tableJoin != '') {
$this->_tableJoin .= ' ' . strtoupper($type) . ' JOIN ' . $table . ' ON ' . $condition;
} else {
$this->_tableJoin = ' ' . strtoupper($type) . ' JOIN ' . $table . ' ON ' . $condition;
}
return $this;
}
/**
* define the limit of rows returned
*
* @access public
* @param string $offset
* @param string $numrows
* @return
*/
public function limit($offset = 0, $numrows = 0) {
$this->_limit = ' LIMIT ' . $offset;
if($numrows != 0) {
$this->_limit .= ', ' . $numrows;
}
return $this;
}
/**
* return the values defined
*
* @access public
* @see method::values()
* @return string;
*/
public function getValues(){
return $this->_values;
}
/**
* return 'where' contidion of the query
*
* @access public
* @see method::where()
* @return string
*/
public function getWhere(){
return $this->_where;
}
/**
* return 'order by' option of the query
*
* @access public
* @see method::order()
* @return string
*/
public function getOrder() {
return $this->_order;
}
/**
* return 'group by' option of the query
*
* @access public
* @see method::group()
* @return string
*/
public function getGroup(){
return $this->_group;
}
/**
* return all 'the inner join' options of the query
*
* @access public
* @see method::getTableJoin()
* @return string
*/
public function getTableJoin(){
return $this->_tableJoin;
}
/**
* return 'limit' option of the query
*
* @access public
* @see method::limit
* @return string
*/
public function getLimit(){
return $this->_limit;
}
/**
* return all the rows fetched in the query
*
* @access public
* @param string $table
* @see method::getValues()
* @see method::getTableJoin()
* @see method::getWhere()
* @see method::getGroup()
* @see method::getOrder()
* @see method::getLimit()
* @return array
*/
public function get($table, $singleRow = FALSE){
$this->_query = 'SELECT ' . $this->getValues() . ' FROM ' . $table
. $this->getTableJoin()
. $this->getWhere()
. $this->getGroup()
. $this->getOrder()
. $this->getLimit();
$this->execute();
return $this->fetchQuery($singleRow);
}
/**
* set to empty all the atributes used
* after the query has been executed
*
* @access private
* @return void
*/
private function _reset() {
$this->_query = NULL;
$this->_values = '*';
$this->_where = NULL;
$this->_group = NULL;
$this->_order = NULL;
$this->_tableJoin = NULL;
$this->_limit = NULL;
$this->_fetched = array();
}
/**
* check if exist rows on specific table
*
* @access public
* @param string $table
* @see method::numRows()
* @see method::_execute()
* @return boolean
*/
public function haveRows($table) {
$this->_query = 'SELECT * FROM ' . $table;
$this->execute();
if($this->numRows() > 0) {
return TRUE;
}
return FALSE;
}
/**
* count number rows in the current
* query executed
*
* @access public
* @return integer
*/
public function numRows() {
return mysql_num_rows($this->_result);
}
/**
* return the last ID generated by
* the current query executed
*
* @access public
* @return integer
*/
public function insertId() {
return mysql_insert_id($this->_con);
}
/**
* return number of rows afected by
* the last sql statenment
*
* @access public
* @return integer
*/
public function rowsAfected() {
return mysql_affected_rows($this->_con);
}
/**
* free mysql memory used in the current
* select sql statenment
*
* @access public
* @return void
*/
public function freeResult() {
return mysql_free_result($this->_result);
}
/**
* close the current conection
*
* @access public
* @return void
*/
public function disconnect() {
mysql_close($this->_con);
}
/**
* return the current sql statenment
* generated by the user
*
* @access public
* @return string
*/
public function myQuery() {
return $this->_myQuery;
}
/**
* execute sql statenments
*
* @access public
* @return boolean
*/
public function execute() {
$this->_myQuery = $this->_query;
$this->_result = mysql_query($this->_query, $this->_con) or die(mysql_error($this->_con));
$this->_reset();
return $this->_result;
}
/**
* create an array with the fetched rows
* produced by the query
*
* @access public
* @return array
*/
public function fetchQuery($singleRow){
$this->_fetched = array();
if($singleRow == TRUE) {
$this->_fetched = mysql_fetch_object($this->_result);
} else {
while($row = mysql_fetch_object($this->_result)) {
$this->_fetched[] = $row;
}
}
return $this->_fetched;
}
/**
* scape variables before insert or select
* in the database
*
* @access public
* @param mixed $value
* @return mixed
*/
public function scape($value){
if(is_numeric($value)) {
return stripslashes($value);
}
return mysql_real_escape_string($value);
}
}