Jump to content

[PHP] SQL Class

- - - - -
Started by Affix, Feb 22 2009 11:55 PM

This topic has been archived. This means that you cannot reply to this topic.
2 replies to this topic

#1
Affix
Posted 22 February 2009 - 11:55 PM

Affix

    Learning Programmer

  • Members
  • PipPipPip
  • 47 posts 
<?php


class sql

{

	//-----------------------------------------------------------------

	//  Take care of vars

	//-----------------------------------------------------------------


	var $debugtext = array();

	var $failed = FALSE;

	var $debug = FALSE;

	var $logging = FALSE;

	var $persistent = FALSE;

	var $results;

	var $link_id;

	var $link_time;

	var $query_time;

	var $queries_executed;

	var $config2;


	////////////////////////////////////////////////////////

	// Function:         DBLayer

	// Description: Constructer


	function sql($persistent = false)

	{

		$this->persistent = $persistent;

	}


	////////////////////////////////////////////////////////

	// Function:         record_message

	// Description: Record the message for debug


	function record_message($message, $mode = 0)

	{

		$this->failed = TRUE;

		if($this->debug)

		{

			if($mode == "WARNING") $this->debugtext[] = array("query" => "<span style='color:orange'><b>WARNING:</b></span> ".$message, "time" => "---------", "status" => "warning");

			else

			{

				$max = count($this->debugtext);

				$this->debugtext[$max - 1] = array("query" => "<span style='color:red'><b>ERROR:</b></span> ".$message, "time" => "---------", "status" => "error");

			}


		}

	}


	////////////////////////////////////////////////////////

	// Function:         record_log

	// Description: A fail-safe of all monitorized queries


	function record_log($query, $refer, $date, $ip)

	{

		if($query == "SET NAMES utf8") return FALSE;

		if($query == "SET collation_connection = 'utf8_unicode_ci'") return FALSE;

		if($query == "SHOW TABLES")  return FALSE;

		if(!(strpos($query,'OPTIMIZE TABLE') === false)) return FALSE;


		$date_format = date('d-M-Y');

		$date_log = date('d/m/y h:i:s A', $date);


		$fp = @fopen ("logs/$date_format.log", 'at');

		if($fp == FALSE) return FALSE;


		@flock($fp, LOCK_EX);

		@fwrite($fp, "[$date_log] [$ip] [$refer] --- $query\n");

		@fclose($fp);

		

		return TRUE;

	}


	////////////////////////////////////////////////////////

	// Function:         record_mysql

	// Description: Only monitor certan queries


	function record_mysql($query, $log, $ip)

	{

		$user_id = 0;

		$user_name = "NULL";

	

		if(isset($_SESSION['user']['id']) && isset($_SESSION['user']['name']))

		{

			$user_id = $_SESSION['user']['id'];

			$user_name = $_SESSION['user']['name']; 

		}


		$info['user_id'] = $user_id;

		$info['user_name'] = $user_name;

		$info['description'] = $log;

		$info['query'] = $query;

		$info['refer'] = stripslashes(@$_SERVER['HTTP_REFERER']);

		$info['date'] = time();

		$info['ip'] = $ip;


		$this->insertRow("logs", $info, FALSE);

	}


	////////////////////////////////////////////////////////

	// Function:         escape

	// Description: Anti-SQL Inject Function :)


	function escape($string)

	{

		if (function_exists('mysql_real_escape_string')) return mysql_real_escape_string($string);

		else return mysql_escape_string($string);

	}


	////////////////////////////////////////////////////////

	// Function:         connect

	// Description: Connect the database


	function connect($debug = false, $logging = false)

	{

		$this->failed = FALSE;

		$this->debug = $debug;

		$this->logging = $logging;


		if($this->persistent == true)

		{

			$this->link_id = @mysql_pconnect($this->config['host'], $this->config['username'], $this->config['password']);

				if($this->link_id == FALSE){

					$this->persistent = false;

				}

		}


		if($this->persistent == false){

			$this->link_id = @mysql_connect($this->config['host'], $this->config['username'], $this->config['password']) or $this->record_message(NULL, "ERROR");

		}

	

		if($this->failed) return FALSE;


		mysql_select_db($this->config['database'], $this->link_id) or $this->record_message(NULL, "ERROR");

		if($this->failed) return FALSE;

	

		if($this->persistent == FALSE){		

			$this->record_message("CMS is not using a persistant connection with the database.", "WARNING");

		}

		

		//$this->query("SET NAMES utf8");

		//$this->query("SET collation_connection = 'utf8_unicode_ci'");

		

		return TRUE;

	}



	////////////////////////////////////////////////////////

	// Function:         query

	// Description: Do the query function + show in debug


	function query($query, $log = FALSE)

	{

		$this->freeResult();


		if($log) $this->record_mysql($query, $log, getIP());


		if($this->logging) $this->record_log(str_replace("`","'",$query), @$_SERVER['HTTP_REFERER'], time(), $ip);

		$this->query_time = microtime(true);


		$this->results = mysql_query($query, $this->link_id) or $this->record_message(mysql_error(), "WARNING");


		if($this->debug) $this->debugtext[] = array("query" => $query, "time" => substr((microtime(true) - $this->query_time), 0, 7), "status" => "ok");

		else $this->debugtext[] = array("query" => $query, "time" => substr((microtime(true) - $this->query_time), 0, 7), "status" => "ok");


		return $this->results;

	}


	////////////////////////////////////////////////////////

	// Function:         getLine

	// Description: Get only 1 row from a table


	function getLine($query, $id = FALSE)

	{

		if($id === FALSE) $this->query($query);

		else $this->query($query." WHERE id='$id'");

		

		if(!is_resource($this->results)) $this->record_message("getLine: ($query)", "ERROR");

		else

		{

			$line = mysql_fetch_assoc($this->results);

			$this->freeResult();

			return $line;

		}

	}


	////////////////////////////////////////////////////////

	// Function:         getTable

	// Description: Get all rows from a table


	function getTable($query, $useid = FALSE)

	{

		$table = array();

		$this->query($query);

		

		if(!is_resource($this->results)) $this->record_message("getTable: ($query)", "ERROR");

		else

		{

			while($line = mysql_fetch_assoc($this->results))

			{

				if($useid) $table[$line[$useid]] = $line;

				else $table[] = $line;

			}

			return $table;

		}

	}


	////////////////////////////////////////////////////////

	// Function:         getInfo

	// Description: Count the rows from a table


	function getInfo($tablename, $condition = false)

	{

		if($condition) $line = $this->getLine("SELECT COUNT(*) FROM ".$this->config['prefix']."$tablename WHERE $condition");

		else $line = $this->getLine("SELECT COUNT(*) FROM ".$this->config['prefix']."$tablename");

		

		return $line['COUNT(*)'];

	}


	////////////////////////////////////////////////////////

	// Function:         insertRow

	// Description: Insert Row in a table


	function insertRow($table, $array, $log = "no description")

	{

		if(count($array) == 0) return;


		if(!isset($comma)) $comma = NULL;

		if(!isset($listofvalues)) $listofvalues = NULL;

		if(!isset($listofelements)) $listofelements = NULL;

		

		foreach($array as $elem=>$value)

		{

			if($value === NULL) $listofvalues .= "{$comma}NULL";

			else $listofvalues .= "$comma'".$this->escape($value)."'";


			$listofelements .= "$comma`$elem`";

			$comma = ',';

		}


		$query = "INSERT HIGH_PRIORITY INTO ".$this->config['prefix']."$table (".$listofelements.") VALUES(".$listofvalues.")";

		$this->query("$query", $log);

		return mysql_insert_id();

	}


	////////////////////////////////////////////////////////

	// Function:         updateRow

	// Description: Update Row in a table


	function updateRow($table, $array, $condition, $log = "no description")

	{

		if(count($array) == 0) return;

		

		$qupdate = "UPDATE ".$this->config['prefix']."$table SET ";

		foreach($array as $index => $value)

		{

			if($value == NULL) $qupdate .= "`$index`=NULL, ";

			else $qupdate .= "`$index`='".$this->escape($value)."', ";

		}

		

		$qupdate = substr($qupdate, 0, -2)." WHERE $condition LIMIT 1";

		$this->query($qupdate, $log);

		return mysql_affected_rows();

	}


	////////////////////////////////////////////////////////

	// Function:         deleteRow

	// Description: Delete Row in a table


	function deleteRow($table, $condition, $log = "no description")

	{

		$this->query("DELETE FROM ".$this->config['prefix']."$table WHERE $condition", $log);

	}


	////////////////////////////////////////////////////////

	// Function:         freeResult

	// Description: Free the result after every query


	function freeResult()

	{

		if(isset($this->results) && is_resource($this->results))

		{

			mysql_free_result($this->results);

			unset($this->results);

		}

	}


	////////////////////////////////////////////////////////

	// Function:         disconnect

	// Description: Disconnect the database


	function disconnect()

	{	

		$this->freeResult();

		if($this->persistent == false)

			mysql_close($this->link_id);


		if($this->debug)

		{

			$queries = 0;

			$queries_failed = 0;

			foreach($this->debugtext as $row)

			{

				if($row['time'] != "---------")

				{

					$this->link_time += $row['time'];

					$queries++;

				}

				if($row['status'] == "error") $queries_failed++;

			}


			$this->queries_executed = $queries + $queries_failed;

			$this->debugtext[] = array("query" => "<span style='color:green'><b>REPORT:</b></span> {$this->queries_executed} queries executed, {$queries} succeded and {$queries_failed} failed in ".substr($this->link_time, 0, 7)." seconds", "time" => "---------", "status" => "ok");

		}

	}


	////////////////////////////////////////////////////////

	// Function:         crash

	// Description: Crashes the site with debugs


	function crash()

	{

		$this->disconnect();

		if($this->debug) ?><table width="928" class="base" align="center" cellspacing='1' cellpadding='4'><col width="1%"><col width="99%"><tr class='head'><td colspan='2'>DB Debug</td></tr><tr class='bottom'><td>Q Time</td><td align="center">Query</td></tr><?php foreach($this->debugtext as $row):?><tr class='con1'><td align="center"><?=$row['time']?></td><td><?=$row['query']?></td></tr><?php endforeach;?></table><br><table width="928" align="center" cellspacing='0' cellpadding='0'><tr><td><?php echo dump($_COOKIE) ?></td></tr></table><table width="928" align="center" cellspacing='0' cellpadding='0'><tr><td><?php echo dump($_SESSION) ?></td></tr></table><table width="928" align="center" cellspacing='0' cellpadding='0'><tr><td><?php echo dump($_POST) ?></td></tr></table><table width="928" align="center" cellspacing='0' cellpadding='0'><tr><td><?php echo dump($this->config2) ?></td></tr></table><?

		//ob_end_flush();

		exit;

	}

	

	function db_settings($field){

		$result = $this->query("SELECT value FROM ".$this->config['prefix']."config WHERE CONVERT( `var` USING utf8 ) = '".$field."'") or $this->record_message("db_settings(1): ".mysql_error(), "WARNING");

		$row = mysql_fetch_array( $result ) or $this->record_message("db_settings(2): ".mysql_error(), "WARNING");

		

		return $row["value"];

	}


	function db_usettings($setting, $value){

		$this->query("UPDATE ".$this->config['prefix']."config SET `value` = '".$value."' WHERE CONVERT(`var` USING utf8) = '".$setting."' LIMIT 1;");

	}

	

	function quick_select($table, $field, $clause=''){

		$clause = $this->escape($clause);

		$query = "SELECT ".$field." FROM ".$this->config['prefix'].$table." ".stripslashes($clause)." LIMIT 1";

		$query2 = $this->query($query) or $this->record_message("quick_select(1): ($query)", "ERROR");

		$result = mysql_fetch_array( $query2 ) or $this->record_message("quick_select(2): ($query)", "ERROR");

		

		return $result[$field];

	}


	function numrows($query){

		$query = $this->query($query);

		$rows = mysql_num_rows( $query );

		

		if (!is_numeric($rows)){

			$this->record_message("numrows: ".mysql_error(), "WARNING");

			return 0;

		}else{

			return $rows;

		}

	}

}


?>

Enjoy xD

#2
Brandon W
Posted 23 February 2009 - 02:54 AM

Brandon W

    Writes binary right handed and hex left handed

  • Members
  • PipPipPipPipPipPipPipPipPip
  • 4,828 posts
Nice work mate, but yet again a description for some newbies. But with the comments it should explain what it is doing ;)
jQuery Selectors Tutorial - jQuery Striped Table tutorial - jQuery Events - jQuery Validation

Sorry if I don't post as often as I did, I'll try to get here as much as possible! I'm working my bum off to get this scholarship and other stuff!

#3
Guest_Jordan_*
Posted 23 February 2009 - 05:35 AM

Guest_Jordan_*
  • Guests
I agree, perhaps a short example of how to use it?
Back to Classes and Code Snippets




  1. CODECALL Programming Forum
  2. → Programming Tutorials
  3. → Classes and Code Snippets
IPB skins by Skinbox