PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Tue Jul 07, 2020 11:20 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 11 posts ] 
Author Message
 Post subject: MySQL Wrapper Class
PostPosted: Tue Apr 26, 2005 7:20 pm 
Offline
Forum Regular
User avatar

Joined: Thu Nov 25, 2004 10:53 pm
Posts: 708
Location: U Michigan
Syntax: [ Download ] [ Hide ]
<?



        /******************************

        *       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 );

                }

       

        }



?>


Top
 Profile  
 
 Post subject:
PostPosted: Tue Apr 26, 2005 10:10 pm 
Offline
Forum Contributor
User avatar

Joined: Fri Jun 21, 2002 7:00 pm
Posts: 353
Location: Cleveland, OH
This is a decent start, but it would be beneficial for you to now provide methods which can iterate over the data.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 27, 2005 4:27 pm 
Offline
Forum Regular
User avatar

Joined: Thu Nov 25, 2004 10:53 pm
Posts: 708
Location: U Michigan
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


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 27, 2005 5:25 pm 
Offline
BeerMod
User avatar

Joined: Tue Jan 13, 2004 5:58 pm
Posts: 2170
Location: Jax FL & Spokane WA USA
Syntax: [ Download ] [ Hide ]
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.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 27, 2005 5:28 pm 
Offline
Forum Regular
User avatar

Joined: Thu Nov 25, 2004 10:53 pm
Posts: 708
Location: U Michigan
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

Syntax: [ Download ] [ Hide ]
if ( isHarmful($sql) )

  logSQL( $sql );


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 27, 2005 9:11 pm 
Offline
Forum Contributor
User avatar

Joined: Fri Jun 21, 2002 7:00 pm
Posts: 353
Location: Cleveland, OH


Top
 Profile  
 
 Post subject:
PostPosted: Wed Apr 27, 2005 9:36 pm 
Offline
Forum Regular
User avatar

Joined: Thu Nov 25, 2004 10:53 pm
Posts: 708
Location: U Michigan
fair enough - Throw this in there

Syntax: [ Download ] [ Hide ]
var $res;

       

function nextObject ( $sql ) {

  return mysql_fetch_object( $this->res );

}

               

function nextArray ( $sql ) {

  return mysql_fetch_array( $this->res );

}


Top
 Profile  
 
 Post subject:
PostPosted: Thu Apr 28, 2005 3:41 pm 
Offline
DevNet Resident

Joined: Thu Mar 10, 2005 6:27 pm
Posts: 1027
Location: Southern Ontario
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)...
Syntax: [ Download ] [ Hide ]
<?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;

                }

        }

}

?>


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 01, 2005 4:45 pm 
Offline
Forum Contributor
User avatar

Joined: Fri Jun 21, 2002 7:00 pm
Posts: 353
Location: Cleveland, OH
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.




Top
 Profile  
 
 Post subject:
PostPosted: Sun May 01, 2005 7:34 pm 
Offline
DevNet Master
User avatar

Joined: Mon Oct 25, 2004 9:29 pm
Posts: 3698
Location: New Jersey, US


Top
 Profile  
 
 Post subject:
PostPosted: Sun May 01, 2005 9:42 pm 
Offline
DevNet Resident

Joined: Thu Mar 10, 2005 6:27 pm
Posts: 1027
Location: Southern Ontario


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 11 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
Powered by phpBB® Forum Software © phpBB Group