MySQL Wrapper Class

Small, short code snippets that other people may find useful. Do you have a good regex that you would like to share? Share it! Even better, the code can be commented on, and improved.

Moderator: General Moderators

Post Reply
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

MySQL Wrapper Class

Post by Todd_Z »

Code: Select all

<?

	/******************************
	*	MySQL Wrapper Class
	*	   author: Todd_Z
	*	   http://www.acdrifter.com
	******************************/
	
	class MySQL {
	
		var $db;
		
		function MySQL ( $username, $password ) {
			$this->db = mysql_connect( "localhost", $username, $password );
		}
		
		function selectDatabase ( $db ) {
			return mysql_select_db( $db, $this->db );
		}
		
		function execSQL ( $sql ) {
			$this->logSQL( $sql );
			$res = mysql_query( $sql );
			if ( !$res )
				return new Error( "{MySQL} #".mysql_errno().": ".mysql_error()." ($sql)" );
			return $res;
		}
		
		function logSQL ( $sql ) {
			$hnd = fopen( "Logs/MySQL/".date("m-d-y").".log", "a" );
			fwrite( $hnd, "[".date("n.j.y H:i:s")."] $sql\n" );
			fclose( $hnd );
		}
	
	}

?>
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

This is a decent start, but it would be beneficial for you to now provide methods which can iterate over the data.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

Iterate how... the execSQL() returns the res, then mysql_fetch_(object|array) can be used to iterate, i dont see how a function in this class would help
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Code: Select all

function execSQL ( $sql ) {
            $this->logSQL( $sql );
            $res = mysql_query( $sql );
            if ( !$res )
                return new Error( "{MySQL} #".mysql_errno().": ".mysql_error()." ($sql)" );
            return $res;
        }
        
        function logSQL ( $sql ) {
            $hnd = fopen( "Logs/MySQL/".date("m-d-y").".log", "a" );
            fwrite( $hnd, "[".date("n.j.y H:i:s")."] $sql\n" );
            fclose( $hnd );
        }
A couple comments/questions regarding the above two functions.

1. You log every sql query in a text file. Not really recommended.
2. Your sql error event calls class "Error"
2a. How is the Error class instantiated inside the current class without being extended?
2b. What is the Error Class?
3. I recommend putting @ before mysql_connect,mysql_select_db,mysql_query and fopen with error catching code.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

The error class is just a class that writes to a log the string that i sent it, not very important.

Are there any checks on sql strings that people have created to see if the sql is possibly harmful to the database? Cuz I would like to add

Code: Select all

if ( isHarmful($sql) )
  logSQL( $sql );
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

Todd_Z wrote:Iterate how... the execSQL() returns the res, then mysql_fetch_(object|array) can be used to iterate, i dont see how a function in this class would help
Well, since you are wrapping the mysql functions, you are essentially hiding the details. So it actually makes perfect sense to wrap the fetch functions. I don't see the point of using this class if you aren't going to abstract out all the mysql functions that you need to use. It would be pointless not to.
User avatar
Todd_Z
Forum Regular
Posts: 708
Joined: Thu Nov 25, 2004 9:53 pm
Location: U Michigan

Post by Todd_Z »

fair enough - Throw this in there

Code: Select all

var $res;
	
function nextObject ( $sql ) {
  return mysql_fetch_object( $this->res );
}
		
function nextArray ( $sql ) {
  return mysql_fetch_array( $this->res );
}
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

As an example of what you can/should do with a mysql abstraction class, below is the class that I use for all my db stuff (I borrowed most of the code from another class I found online, this my or may not be the most recent version that I use, I've added a whack of debug information to it)...

Code: Select all

<?php
class mySQL {

   var $_DEBUG;
   var $_DEBUGFULL = FALSE;
	var $connection;
	var $selectedDb;
   var $result;
	var $isConnected;
	var $queryType;
   var $arrayType;

	/* constructor */
   function mySQL($dbuser, $dbpass, $dbdb, $dbhost="", $dbg=FALSE, $dbgFull=FALSE) {
      if (!$dbhost) $dbhost="localhost";
      $this->db = $dbdb;
      $this->user  = $dbuser;
      $this->pass  = $dbpass;
      $this->server = $dbhost;
      $this->_DEBUG = $dbg;
      $this->_DEBUGFULL = $dbgFull;
      $this->arrayType= "assoc";
      $this->connect();
   }

   function debug(){$this->_DEBUG = TRUE;}
   function nodebug(){$this->_DEBUG = FALSE;}

	function connect() {
		if($this->getConnected()) {
			$this->closeConnection();
		}
		if ($this->_DEBUG) {
         $this->printDebug("Connection", "Connecting to host: [$this->server] with user: [$this->user] and pass: [$this->pass].");
      }
		if($this->connection = (@mysql_pconnect($this->server, $this->user, $this->pass))) {
			$this->setConnected(true);
			if ($this->_DEBUG) $this->printDebug("Connection", "Connection Success -- Mysql Returned: [$this->connection]");
         if($this->db) {
				$this->setDb($this->db);
			}
			return true;
		}
		else {
			$this->setConnected(false);
			$this->printError("Connection", $this->getMysqlError());
			return false;
		}
	}

	/* destructor */
	function disconnect() {
		if($this->result) {
			if ($this->_DEBUG) $this->printDebug("Disconnect","Freeing Result");
         $this->freeResult();
		}
		if($this->getConnected()) {
			if ($this->_DEBUG) $this->printDebug("Disconnect","Closing Connection");
			$this->closeConnection();
		}
	}

	function setDb($dbName) {
		if(!$this->getConnected()) {
			$this->printError("Db Set", "Not connected in function setDb()");
			return false;
		}
		if($this->selectedDb = mysql_select_db($dbName, $this->connection)) {
			if ($this->_DEBUG) $this->printDebug("Db Set","Setting of DB [$dbName] Successful!");
         return true;
		}
		if ($this->_DEBUG) $this->printDebug("Db Set","Setting of DB [$dbName] Failed! -- ".mysql_error());
		return false;
	}

	function getConnected() {
		return $this->isConnected;
	}

	function setConnected($status) {
		$this->isConnected = $status;
	}

	function closeConnection() {
		if($this->getConnected()) {
			mysql_close($this->connection);
			$this->setConnected(false);
		}
	}

	function freeResult() {
		if($this->result) {
			if ($this->_DEBUG) $this->printDebug("Free Result","Freeing Resultset");
			@mysql_free_result($this->result);
		}
	}

	function query($sql, $return_type='array') {
		if(strlen(trim($sql)) == 0) {
			if($this->_DEBUG==true) {
				$this->printError("Query", "No SQL was passed to query()");
			}
			return false;
		}
		if(!$this->getConnected()) {
			if($this->_DEBUG==true) {
				$this->printError("Query", $this->getMysqlError());
			}
			return false;
		}

		$queryType = substr(trim($sql), 0, strpos($sql, " "));
      $this->setQueryType($queryType);
		$this->prepare_vars($sql);
		if ($this->_DEBUG) $this->printDebug("Query","Querying Database -- [$sql]");
      $this->result = mysql_query($sql, $this->connection);
		if($this->result) {
			if ($this->_DEBUG) $this->printDebug("Query","Query Successful -- Mysql Returned: [$this->result]");
			if ($this->_DEBUG) $this->printDebug("Query","Results returned as [$return_type]");
			switch($return_type) {
				case 'row':
					return $this->fetchRow();
				break;

				case 'obj':
					return $this->fetchObject();
				break;

				case 'index':
					return $this->fetchIndex0();
				break;

				case 'none':
					return true;
				break;

				default:
		         $res_array = $this->result_to_array();
               return($res_array);
				break;
			}
			return true;
		}
		else {
   		$this->printError("Query", $this->getMysqlError());
			return false;
		}
	}

	function setQueryType($type) {
		$this->queryType = strtoupper($type);
	}

	function getQueryType() {
		return $this->queryType;
	}

	function getNumRows() {
		if($this->result) {
			return mysql_affected_rows($this->connection);
		}
		return false;
	}

	function getQueryResult() {
		return $this->result;
	}
	function setArrayType($type) {
	  $this->arrayType=$type;
	}
	function getArrayType() {
	  return $this->arrayType;
	}
	function fetchArray() {
		if($this->result) {
         switch($this->arrayType) {
            case "assoc":
               $type = MYSQL_ASSOC;
            break;
            case "num":
               $type = MYSQL_NUM;
            break;
            default:
               $type = MYSQL_BOTH;
            break;
         }
         return $this->prepare_vars(mysql_fetch_array($this->result, $type));
		}
		return false;
	}

	function fetchObject() {
		if($this->result) {
			$temp = mysql_fetch_object($this->result);
		   if ($this->_DEBUGFULL) echo "<pre style=\"font-size:7pt\">".print_r($temp, TRUE)."</pre>";
         return $temp;
      }
		return false;
	}

	function fetchRow() {
		if($this->result) {
		   $temp = $this->prepare_vars(mysql_fetch_row($this->result));
         if ($this->_DEBUGFULL) echo "<pre style=\"font-size:7pt\">".print_r($temp, TRUE)."</pre>";
         return $temp;
		}
		return false;
	}

	function fetchIndex0() {
		if($this->result) {
			return $this->prepare_vars(mysql_result($this->result, 0));
		}
		return false;
	}

	function fetchInsertID() {
		if($this->result) {
			return mysql_insert_id();
		}
		else {
         $this->printDebug("Fetch Insert ID", "No Insert ID From : [$this->result]");
		}
      return false;
	}

	function result_to_array($array=null) {
		if(!empty($array)) {
			$this->result = $array;
		}
		$res_array = array();
   	for ($count=0; $row = @$this->fetchArray(); $count++) {
          $res_array[$count] = $this->prepare_vars($row);
   	}
      if ($this->_DEBUGFULL) echo "<pre style=\"font-size:7pt\">".print_r($res_array, TRUE)."</pre>";
      return $res_array;
	}

	function formatdate($date) {
	    //Reformat a mySQL date into a more readable one
		list($this->yyyy, $this->mm, $this->dd) = explode('-',$date);
	    $this->date = date('j F Y', mktime(0,0,0,$this->mm,$this->dd,$this->yyyy));
	    return $this->date;
	}

	function printError($loc, $text, $killApp=false) {
		if($text) {
			print("Error: [<span style=\"color:#F00;\">$loc</span>] : <span style=\"text-weight:bold;\">".$text."</span><br/>");
		}
		if($killApp) {
			exit();
		}
	}
   function printDebug($loc, $text) {
      if ($text) {
         echo "Debug: [<span style=\"color:#F00;\">$loc</span>] : <span style=\"text-weight:bold;\">$text</span><br/>";
      }
	}
   function getMysqlError() {
		if(mysql_error()) {
			return "<span style=\"font-weight:bold;\">Mysql Error Number [".mysql_errno()."]</span> -- [<span style=\"text-decoration:underline;\">".mysql_error()."</span>]";
		}
		return false;
	}

	function prepare_vars($input) {
		switch($this->getQueryType()) {
			case 'SELECT':
				if (is_array($input)) {
					foreach ($input as $this->key => $this->value) {
				   		$output[$this->key] = trim(stripslashes($this->value));
					}
				}
				else {
					$output = trim(stripslashes($input));
				}
				return $output;
			break;

			case 'UPDATE':
			case 'INSERT':
				if (is_array($input)) {
					foreach ($input as $key => $value) {
				   		$output[$key] = trim(addslashes($value));
					}
				}
				else {
					$output = $input;
				}
				return $output;
			break;
		}
	}
}
?>
User avatar
protokol
Forum Contributor
Posts: 353
Joined: Fri Jun 21, 2002 7:00 pm
Location: Cleveland, OH
Contact:

Post by protokol »

I know that many people write their own database abstraction layer. However, I challenge you to use existing layers, such as PEAR::DB or Creole. To be honest, there's no reason not to use either of these. They both provide everything you need and much more. While it is good practice to write your own and learn from your mistakes, etc., it is much more reasonable to use these great packages in production-level sites where you just need it to work and don't have the time or the resources to develop your own DBAL from scratch.

If you need customization, extend the classes! It is OK and a "Good Thing" to do this. You maintain the functionality of the base classes, but you also gain additional functionality from the extension.

Anyway, I just wanted to let this out because I see so many people writing code over and over and over again that already exists and is designed much better than what they are creating. By using this stuff, it prevents you from "Reinventing the wheel", so-to-speak.

PEAR DB
Creole
User avatar
Ambush Commander
DevNet Master
Posts: 3698
Joined: Mon Oct 25, 2004 9:29 pm
Location: New Jersey, US

Post by Ambush Commander »

Actually, I've heard an interesting counterargument for this:

In both this book and my own programming practice, I often prefer to build my own components. Especially in preformance-critical applications, it is often easiest to design a solution that fits your exact needs and is not overburdened by extra fluff. However, it can sometimes be much easier to use an existing solution that to reinvent the wheel.
Of course, if you're not a good enough programmer to design a much faster class that exactly fits your needs, PEAR is great.
nickvd
DevNet Resident
Posts: 1027
Joined: Thu Mar 10, 2005 5:27 pm
Location: Southern Ontario
Contact:

Post by nickvd »

Ambush Commander wrote:Actually, I've heard an interesting counterargument for this:

In both this book and my own programming practice, I often prefer to build my own components. Especially in preformance-critical applications, it is often easiest to design a solution that fits your exact needs and is not overburdened by extra fluff. However, it can sometimes be much easier to use an existing solution that to reinvent the wheel.
Of course, if you're not a good enough programmer to design a much faster class that exactly fits your needs, PEAR is great.
This is the precise reason why i code most of the tools i use in my sites. Most of the sites that I do are for clients, and most of the pre-made scripts out there are well written, they would just take way too much time to integrate with other utilities that are being included on their site (custom made authentication, news, etc...) writing my own (or heavily modifying an existing script in my off time) is just so much easier.
Post Reply