<?php
//+-----------------------------------------------+//
//+ Copyright Brian Shawa
//+ mysql.php
//+ Description: A class for doing routine mysql stuff
//+ Dependencies: NONE
//+
//+ LAST UPDATED : 10 July 2009
//+-----------------------------------------------+//
class QuickMysql {
var $last_error;
var $last_query;
public $obj_status = true;
var $result_status = false;//tells us if sql produced any result
var $obj_debug_mode = 0;
var $global_debug_mode = 0;
public $pagination_html = '';
public $pagination_simple_html = '';
var $sql_rows = 0;
var $sql_fetched_array = array();
var $lang_array = array('incomplete_mysq' =>
'Incomplete MysQL database connection information');
//____lets start - Connect to dbase___________________________
function QuickMysql($db_name ='',$db_username='',$db_password='',$db_host='') {//debug is optional
//are we in debug mode
global $quickmysql_debug;
global $config;// >>STAND ALONE USAGE <<, set this in calling file
$this->obj_debug_mode = $quickmysql_debug;
$this->global_debug_mode = $config['debug_mode'];// >>STAND ALONE USAGE <<, set this in calling file
//chcek all needed vars
if($db_name == '' || $db_username == '' || $db_host == '') {
$this->last_error = 'Incomplete Mysql Information';//incomplete mysl information
$this->obj_status = false;
$this->PrintDebug();
return false;
}
//close any mysql connection
@mysql_close();
//open new connection
$new_connection = @mysql_connect($db_host,$db_username,$db_password);
if(!$new_connection) {
$this->last_query = 'mysql_connect';
$this->last_error = @mysql_error();
$this->obj_status = false;
$this->PrintDebug();
return false;
}
else {
@mysql_select_db($db_name,$new_connection);
}
//checks on errors
if(@mysql_error()) {
$this->last_error = @mysql_error();
$this->obj_status = false;
$this->PrintDebug();
return false;
}
else {
return true;
}
}
//____MYSQ INSERT________________________________
/* Insert rows into mysql */
/* returns true on success */
public function InsertRecord($sql_query) {
$this->obj_status = true;//reset
$this->last_query = $sql_query;
//check if any sql has been sent
@mysql_query($sql_query);
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
else {
return true;
}
}
//____MYSQ SELECT ARRAY________________________________
/* Select rows from mysql */
/* returns an array on success */
public function SelectRecord($sql_query) {
$this->last_query = $sql_query;
$this->obj_status = true;
$this->result_status = false;//assume results are false. check later
//___reset array just to be sure___________
$this->sql_fetched_array = ''; //reset results array
$this->sql_fetched_array = array(); //reset results array
//execute sql
$result = @mysql_fetch_array(@mysql_query($sql_query));
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
//if array is returned
if(!empty($result)) {
$this->result_status = true;
$this->sql_fetched_array = $result;
return $this->sql_fetched_array;
}
}
//____MYSQ SELECT ARRAY________________________________
/* Select rows from mysql */
/* returns an array on success */
public function SelectRecordLoop($sql_query) {
$this->last_query = $sql_query;
$this->obj_status = true;
$this->result_status = false;//assume results are false. check later
//___reset array just to be sure___________
$this->sql_fetched_array = ''; //reset results array
$this->sql_fetched_array = array(); //reset results array
//execute sql
$query = @mysql_query($sql_query);
$loop = array();
while($result = @mysql_fetch_array($query)) {
$loop[] = $result;
}
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
//if array is returned
if(!empty($loop)) {
$this->result_status = true;
$this->sql_fetched_array = $loop;
return $this->sql_fetched_array;
}else{
return $this->sql_fetched_array; //an empty array (needed for TBS block merge etc)
}
}
//____PAGINATED MYSQ SELECT ARRAY________________________________
public function PaginatedRecordLoop($sql_query) {
global $paginate, $pagi_addurl_pre, $pagi_addurl_post;//set in calling page as var
//___reset array just to be sure___________
$this->sql_fetched_array = ''; //reset results array
$this->sql_fetched_array = array(); //reset results array
//take all the vars from $paginate array()
$pagi_limit = $paginate[0];
$pagi_current_page = $paginate[1];
$pagi_lang_next = $paginate[2];
$pagi_lang_previous = $paginate[3];
//get pagination limits. if not default to 10.
if($pagi_limit == '' || $pagi_limit <= 0 || !is_numeric($pagi_limit)) {
$pagi_limit = 10;
}
//get current page. if not default to page 1.
if(!is_numeric($pagi_current_page)) {
$pagi_current_page = 1;
}
//Start by counting rows returned by sql
$rows = @mysql_num_rows(mysql_query($sql_query));
//Total page sets
$pagi_total = ceil($rows / $pagi_limit);
//error control
if($pagi_current_page > $pagi_total) {
$pagi_current_page = 1;//default
}
//Sql starting page
$pagi_sql_starting = $pagi_current_page * $pagi_limit - ($pagi_limit);
//Previous pages html
$prev_page = $pagi_current_page - 1;
$html_previous = '<li><a href="'.$pagi_addurl_pre.'page='.$prev_page.$pagi_addurl_post.'">'.$pagi_lang_previous.'</a></li>';
if($prev_page <= 0 || $prev_page == $pagi_total) {
$html_previous = '';
}
//Next pages html
$next_page = $pagi_current_page + 1;
$html_next = '<li><a href="'.$pagi_addurl_pre.'page='.$next_page.$pagi_addurl_post.'">'.$pagi_lang_next.'</a></li>';
if($next_page > $pagi_total) {
$html_next = '';
}
//lets create the rest of the paginatation links
for($count = 1; $count <= $pagi_total; $count += 1) {
$html_pages .= '<li><a href="'.$pagi_addurl_pre.'page='.$count.$pagi_addurl_post.'"> '.$count.' </a></li>';
}
//_____Final build (pagination full)______________
$html_output = $html_previous.$html_pages.$html_next;
$this->pagination_html = $html_output;
//_____Final build (pagination simple)______________
$this->pagination_simple_html = $html_previous.$html_next;
//_____Run actual SQL for results_____________
$sql_query = $sql_query." LIMIT $pagi_sql_starting, $pagi_limit"; //add the pagination stuff to sql
$query = @mysql_query($sql_query);
$loop = array();
while($result = @mysql_fetch_array($query)) {
$loop[] = $result;
}
//debug
$this->last_error = @mysql_error().'<br /><b>Additional OutPut</b> <br /> current page: '.$pagi_current_page.'<br />total rows: '.$rows.'<br />page limits: '.$pagi_limit.'<br />total pages: '.$pagi_total;
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
//if array is returned
if(!empty($loop)) {
$this->result_status = true;
$this->sql_fetched_array = $loop;
return $this->sql_fetched_array;
}else{
return $this->sql_fetched_array; //an empty array (needed for TBS block merge etc)
}
}
//____MYSQ UPDATE________________________________
/* Insert rows into mysql */
/* returns true on success */
public function UpdateRecord($sql_query) {
$this->obj_status = true;//reset
$this->last_query = $sql_query;
//execute sql
@mysql_query($sql_query);
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
else {
return true;
}
}
//____MYSQ DELETE________________________________
/* Insert rows into mysql */
/* returns true on success */
public function DeleteRecord($sql_query) {
$this->obj_status = true;//reset
$this->last_query = $sql_query;
//execute sql
@mysql_query($sql_query);
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
else {
return true;
}
}
//____MYSQ Count Rows_________________________
/* count rows in sql*/
/* returns rows on success / false on fail*/
public function CountRows($sql_query) {
$this->obj_status = true;//reset
//reset rows
$this->sql_rows = 0;
$this->last_query = $sql_query;
$rows = @mysql_num_rows(@mysql_query($sql_query));
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
else {
$this->sql_rows = $rows;
return $this->sql_rows;
}
}
//____MYSQ Record check__________________________________
/* same as count but returns true/false if record found*/
public function RecordExists($sql_query) {
$this->obj_status = true;//reset
//reset rows
$this->last_query = $sql_query;
$rows = @mysql_num_rows(@mysql_query($sql_query));
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//retun true or flase
if($rows > 0 && !@mysql_error()) {
return true;
}
if($rows <= 0 && !@mysql_error()) {
return false;
}
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
}
//____MYSQ GENERAL________________________________
/* general sql query */
/* returns true on success */
public function SqlQuery($sql_query) {
$this->obj_status = true;//reset
$this->last_query = $sql_query;
//execute sql
@mysql_query($sql_query);
//debug
$this->last_error = @mysql_error();
$this->PrintDebug();
//checks on errors
if(@mysql_error()) {
$this->obj_status = false;
return false;
}
else {
return true;
}
}
//Debug function
private function PrintDebug() {
if($this->obj_debug_mode == 1 || $this->global_debug_mode == 1) {
echo 'SQL QUERY: '.$this->last_query.'</br>';
echo 'DEBUG: '.$this->last_error.'</br>';
}//debug mode
}
}//QuickMysql end
/*==================================================================================================================
USAGE:
-------------
(0) //General Sql Query (for any kind of sql query)
$dbase->SqlQuery("SELECT *
FROM table
WHERE foo = '$bar'");
(1) //Connect to a dbase
$quickmysql_debug = 1; (optional)
$dbase = new QuickMysql($config['db_name'], $config['db_user'], $config['db_pass'], $config['db_host']);
(2) //Selection a record
$result = $dbase->SelectRecord("SELECT *
FROM table
WHERE foo = '$bar'");
<++> result_status = TRUE when record found
(3) //Select an array of records
$result = $dbase->SelectRecordLoop("SELECT *
FROM table
WHERE foo = '$bar'
ORDER BY foo DESC");
<++> result_status = TRUE when records found
(4) //Adding a record
$dbase->InsertRecord("INSERT INTO bar (
foo, fooz
) values (
'$bar', '$barz')");
(5) //counting rows
$rows = $dbase->CountRows("SELECT *
FROM table
WHERE foo = '$bar");
($rows == 0)? NULL : $notification = 'A user with that email already exists'; //use it as a check
(6) //checking if a record exists
if(!$dbase->RecordExists("SELECT * FROM table WHERE foo = '$bar'")){
//do something
}
WARNING: It may be safe to use $rows above and check for a "posetive" result..as the above will work even if mysql is down
(7) Updating Record
$dbase->UpdateRecord("UPDATE table SET foo = '$bar' WHERE code = 'xyz'");
<++> result_status = TRUE when record updated
(8) Delete Record
$dbase->DeleteRecord("DELETE FROM table WHERE foo = '$bar'");
<++> result_status = TRUE when record updated
(9) Paginated Result
$current_page = (($_GET['page'])? $_GET['page'] : $_POST['page']);
$paginate = array(30, Sanatize($current_page), ' >>', '<< '); //(limit, current page, next, previous)
$result = $dbase->PaginatedRecordLoop("SELECT *
FROM table
WHERE foo=bar");
//$pagination = $dbase->pagination_html; //Full pagination output - This is <li>'s for used in html with htmlconv=no
$pagination = $dbase->pagination_simple_html; //Simple pagination (<<previous:next>>) - This is <li>'s for used in html with htmlconv=no
//These methods all return true, so if you want to do an action on condition that no mysql errors
//example
if(!$dbase->RecordExists("SELECT user_id FROM member_profile WHERE user_name = '$user_name' OR email_address = '$email_address'")){
//do something if it returns true
}
================================================================================================================*/
?>