Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: Database Class

  1. #1
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,174
    Thanks
    19
    Thanked 66 Times in 65 Posts

    Database Class

    So, I thought it was about time I upgraded my database include from
    PHP Code:
    <?
     $dbc
    =mysql_connect ("host""username""password") or die ('I cannot connect to the database because: ' mysql_error());
    mysql_select_db ("mydatabase") or die(mysql_error());
    ?>
    I designed it to be easy to use and easy to read - it may not be as complex and fancy as some but I find when you try to get too fancy - and in particular include every possible functionality, classes can become difficult to use and remembering parameter lists and orders becomes a nightmare - that happened with a form building class I wrote and I never use it anymore - it was worse than typing html.
    So its designed to have a nice balance between writing SQL and queries being built.
    There are basic functions for select, insert (with replace option), update, delete and a query function that will run any sql you pass it.
    It relies on 2 config files - a global config file (for your entire site) and a module specific config file (just for the dbc class) - part of a templating system I'm building - so for simplicity you can simply take those parts out or modify the constructor to your liking. It also has a destructor for those using PHP 5 (not critical, remove it for php 4)
    The config files specify the values of the class variables.
    It has a debug mode - that will output errors to the screen and a logging mode that logs errors to a file.

    Selects return the resource identifier, other queries return msql_affected_rows and the query() function can return either.

    Enjoy - feedback welcome.

    PHP Code:
    <?
    //database class

    class dbc
    {
     var 
    $host;
     var 
    $user;
     var 
    $password;
     var 
    $database;
     var 
    $conn;
     var 
    $debug;
     var 
    $log;

     function 
    dbc($configOverride="")
     {
       
    //get variables from config file
       
    $expected = array('host''user''password''database''debug''log');
       
    //get global config values first
       
    $config file($_SERVER['DOCUMENT_ROOT'].'/../lib/global.cnf');
       
    $this->loadConfig($config$expected); 
       
    $config file($_SERVER['DOCUMENT_ROOT'].'/../modules/config/dbc.cnf');
       
    $this->loadConfig($config$expected); 
       if(
    $configOverride)
       {
        
    $this->loadConfig($configOverride$expected); 
       }  
       
    $this->connect();
       
    $this->selectDatabase($this->database);
     }
     
     function 
    loadConfig($config$expected)
     {
        foreach(
    $config as $line)
       {
         
    $configOptions explode(":"$line2);
         if(
    in_array(strtolower($configOptions[0]), $expected))
         {
           
    $this->$configOptions[0]=str_replace(array("\n""\r\n","\r""\n\r"), "",$configOptions[1]);
         }
       }
     }
     function 
    connect()
     {
       if(
    $this->host && $this->user && $this->database)
       {
         
    $this->conn mysql_connect($this->host,$this->user,$this->password) or $this->error("Could not connect to server","Connect to $this->host $this->user:$this->password");         
       }
     }

     function 
    selectDatabase($database)
     {
       
    $this->database $database;
       
    mysql_select_db($database$this->conn) or $this->error(mysql_error(), "USE $database");
     }

     function 
    error($error$sql)
     {
       if(
    $this->debug)
       {
         echo 
    "SQL: $sql<br />Error: $error";
       }
       if(
    $this->log)
       {
         
    $handle fopen($_SERVER['DOCUMENT_ROOT'].'/../modules/errorlogs/dbc.log''a');
         
    $errorLog 'DATE:'."\t".date("Y-m-d H:i:s")."\n";
         
    $errorLog .= 'PAGE:'."\t".$_SERVER['PHP_SELF']."?".$_SERVER['argv'][0]."\n";
         
    $errorLog .= 'SQL:'."\t".$sql."\n";
         
    $errorLog .= 'ERROR:'."\t".$error."\n\n";
         
    fwrite($handle$errorLog);
         
    fclose($handle);
       }
     }
     
     function 
    select($fields$tables$where = array(), $order = array(), $group=array(),$limit "")
     {
        
    $fields implode(",",$fields);
        
    $tables implode(","$tables);
        
    $where implode(" AND "$where);
        
    $order implode(",",$order);
        
    $group implode(",",$group);
        
        
    $sql "SELECT $fields FROM $tables ";
        if(!empty(
    $where))
        {
            
    $sql .= ' WHERE '.$where;
        }
        if(!empty(
    $order))
        {
            
    $sql .= ' ORDER BY '.$order;
        }
        if(!empty(
    $group))
        {
            
    $sql .= ' GROUP BY '.$group;
        }
        if(!empty(
    $limit))
        {
            
    $sql .= ' LIMIT '.$limit;
        }
        
        
    $result mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        
        return 
    $result;
     }
      
     function 
    insert($tables$values$fields=array(), $replace false)
     {
          foreach(
    $values as $key => $val)
          {
            
    $values[$key] = $this->protect($val);
        }
        
    $tables implode(","$tables);
        
    $values implode(","$values);
        
    $fields implode(","$fields);
        
        if(
    $replace)
        {
            
    $sql "REPLACE INTO $tables ";
        }
        else
        {
            
    $sql "INSERT INTO $tables ";
        }
        
        
        if(!empty(
    $fields))
        {
            
    $sql .= '('.$fields.') ';
        }
        
        
    $sql .= 'VALUES ('.$values.')';
        
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        return 
    mysql_affected_rows($this->conn);
        
     }
     
     function 
    update($tables$values$fields$where= array(), $limit"")
     {
        foreach(
    $values as $key=>$val)
        {
            
    $pairs[] = $this->$fields[$key].' = '.$this->protect($val);
        }
        
    $pairs implode(",",$pairs);
        
    $tables implode(","$tables);
        
    $where implode(" AND "$where);
        
    $sql "UPDATE $tables SET $pairs ";
        if(!empty(
    $where))
        {
            
    " WHERE $where";
        }
        if(!empty(
    $limit))
        {
            
    $sql .= " LIMIT $limit";
        }
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        return 
    mysql_affected_rows($this->conn);
     }

     function 
    delete($table$where=""$limit="")
     {
        
    $where implode(","$where);
        if(empty(
    $where) && empty($limit))
        {
            
    $sql "TRUNCATE $table";
        }
        else
        {
            
    $sql "DELETE FROM $table ";
            if(!empty(
    $where))
            {
                
    $sql .= ' WHERE '.$where;
            }
            if(!empty(
    $limit))
            {
                
    $sql .= ' LIMIT '.$limit;
            }
        }
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        return 
    mysql_affected_rows($this->conn);
     }

     function 
    query($sql$return="result")
     {
        
    $result mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        switch(
    $return)
        {
            case 
    "result":
                return 
    $result;
                break;
            case 
    "affected_rows":
                return 
    mysql_affected_rows($this->conn);
                break;
        }
     }

     function 
    protect($val)
     {
        if (
    get_magic_quotes_gpc()) 
        {
           
    $value stripslashes($value);
        }
        
       if (!
    is_numeric($value) || $value[0]=='0'
       {
           
    $value "'" mysql_real_escape_string($value) . "'";
       }
       return 
    $value;
     }
     
     function 
    close()
     {
        
    mysql_close($this->conn);
     }

     function 
    __destruct()
     {
        
    $this->close();
     }
    }
    examples of use

    PHP Code:
    $dbc = new dbc();

    $result $dbc->query("My very complicated sql query that selects data");

    $affected $dbc->query("My very complicated sql query that affects some rows""affected_rows");

    $result $dbc->select(array('field1''field2'), 'mytable', array('id=1',"foo='bar'"));

    $dbc->delete('mytable');

    //...etc 

  • #2
    New to the CF scene
    Join Date
    Feb 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bug in the code

    Quote Originally Posted by NancyJ View Post
    So, I thought it was about time I upgraded my database include from
    PHP Code:
    <?
     $dbc
    =mysql_connect ("host""username""password") or die ('I cannot connect to the database because: ' mysql_error());
    mysql_select_db ("mydatabase") or die(mysql_error());
    ?>
    I designed it to be easy to use and easy to read - it may not be as complex and fancy as some but I find when you try to get too fancy - and in particular include every possible functionality, classes can become difficult to use and remembering parameter lists and orders becomes a nightmare - that happened with a form building class I wrote and I never use it anymore - it was worse than typing html.
    So its designed to have a nice balance between writing SQL and queries being built.
    There are basic functions for select, insert (with replace option), update, delete and a query function that will run any sql you pass it.
    It relies on 2 config files - a global config file (for your entire site) and a module specific config file (just for the dbc class) - part of a templating system I'm building - so for simplicity you can simply take those parts out or modify the constructor to your liking. It also has a destructor for those using PHP 5 (not critical, remove it for php 4)
    The config files specify the values of the class variables.
    It has a debug mode - that will output errors to the screen and a logging mode that logs errors to a file.

    Selects return the resource identifier, other queries return msql_affected_rows and the query() function can return either.

    Enjoy - feedback welcome.

    PHP Code:
    <?
    //database class

    class dbc
    {
     var 
    $host;
     var 
    $user;
     var 
    $password;
     var 
    $database;
     var 
    $conn;
     var 
    $debug;
     var 
    $log;

     function 
    dbc($configOverride="")
     {
       
    //get variables from config file
       
    $expected = array('host''user''password''database''debug''log');
       
    //get global config values first
       
    $config file($_SERVER['DOCUMENT_ROOT'].'/../lib/global.cnf');
       
    $this->loadConfig($config$expected); 
       
    $config file($_SERVER['DOCUMENT_ROOT'].'/../modules/config/dbc.cnf');
       
    $this->loadConfig($config$expected); 
       if(
    $configOverride)
       {
        
    $this->loadConfig($configOverride$expected); 
       }  
       
    $this->connect();
       
    $this->selectDatabase($this->database);
     }
     
     function 
    loadConfig($config$expected)
     {
        foreach(
    $config as $line)
       {
         
    $configOptions explode(":"$line2);
         if(
    in_array(strtolower($configOptions[0]), $expected))
         {
           
    $this->$configOptions[0]=str_replace(array("\n""\r\n","\r""\n\r"), "",$configOptions[1]);
         }
       }
     }
     function 
    connect()
     {
       if(
    $this->host && $this->user && $this->database)
       {
         
    $this->conn mysql_connect($this->host,$this->user,$this->password) or $this->error("Could not connect to server","Connect to $this->host $this->user:$this->password");         
       }
     }

     function 
    selectDatabase($database)
     {
       
    $this->database $database;
       
    mysql_select_db($database$this->conn) or $this->error(mysql_error(), "USE $database");
     }

     function 
    error($error$sql)
     {
       if(
    $this->debug)
       {
         echo 
    "SQL: $sql<br />Error: $error";
       }
       if(
    $this->log)
       {
         
    $handle fopen($_SERVER['DOCUMENT_ROOT'].'/../modules/errorlogs/dbc.log''a');
         
    $errorLog 'DATE:'."\t".date("Y-m-d H:i:s")."\n";
         
    $errorLog .= 'PAGE:'."\t".$_SERVER['PHP_SELF']."?".$_SERVER['argv'][0]."\n";
         
    $errorLog .= 'SQL:'."\t".$sql."\n";
         
    $errorLog .= 'ERROR:'."\t".$error."\n\n";
         
    fwrite($handle$errorLog);
         
    fclose($handle);
       }
     }
     
     function 
    select($fields$tables$where = array(), $order = array(), $group=array(),$limit "")
     {
        
    $fields implode(",",$fields);
        
    $tables implode(","$tables);
        
    $where implode(" AND "$where);
        
    $order implode(",",$order);
        
    $group implode(",",$group);
        
        
    $sql "SELECT $fields FROM $tables ";
        if(!empty(
    $where))
        {
            
    $sql .= ' WHERE '.$where;
        }
        if(!empty(
    $order))
        {
            
    $sql .= ' ORDER BY '.$order;
        }
        if(!empty(
    $group))
        {
            
    $sql .= ' GROUP BY '.$group;
        }
        if(!empty(
    $limit))
        {
            
    $sql .= ' LIMIT '.$limit;
        }
        
        
    $result mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        
        return 
    $result;
     }
      
     function 
    insert($tables$values$fields=array(), $replace false)
     {
          foreach(
    $values as $key => $val)
          {
            
    $values[$key] = $this->protect($val);
        }
        
    $tables implode(","$tables);
        
    $values implode(","$values);
        
    $fields implode(","$fields);
        
        if(
    $replace)
        {
            
    $sql "REPLACE INTO $tables ";
        }
        else
        {
            
    $sql "INSERT INTO $tables ";
        }
        
        
        if(!empty(
    $fields))
        {
            
    $sql .= '('.$fields.') ';
        }
        
        
    $sql .= 'VALUES ('.$values.')';
        
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        return 
    mysql_affected_rows($this->conn);
        
     }
     
     function 
    update($tables$values$fields$where= array(), $limit"")
     {
        foreach(
    $values as $key=>$val)
        {
            
    $pairs[] = $this->$fields[$key].' = '.$this->protect($val);
        }
        
    $pairs implode(",",$pairs);
        
    $tables implode(","$tables);
        
    $where implode(" AND "$where);
        
    $sql "UPDATE $tables SET $pairs ";
        if(!empty(
    $where))
        {
            
    " WHERE $where";
        }
        if(!empty(
    $limit))
        {
            
    $sql .= " LIMIT $limit";
        }
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        return 
    mysql_affected_rows($this->conn);
     }

     function 
    delete($table$where=""$limit="")
     {
        
    $where implode(","$where);
        if(empty(
    $where) && empty($limit))
        {
            
    $sql "TRUNCATE $table";
        }
        else
        {
            
    $sql "DELETE FROM $table ";
            if(!empty(
    $where))
            {
                
    $sql .= ' WHERE '.$where;
            }
            if(!empty(
    $limit))
            {
                
    $sql .= ' LIMIT '.$limit;
            }
        }
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        return 
    mysql_affected_rows($this->conn);
     }

     function 
    query($sql$return="result")
     {
        
    $result mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql);
        switch(
    $return)
        {
            case 
    "result":
                return 
    $result;
                break;
            case 
    "affected_rows":
                return 
    mysql_affected_rows($this->conn);
                break;
        }
     }

     function 
    protect($val)
     {
        if (
    get_magic_quotes_gpc()) 
        {
           
    $value stripslashes($value);
        }
        
       if (!
    is_numeric($value) || $value[0]=='0'
       {
           
    $value "'" mysql_real_escape_string($value) . "'";
       }
       return 
    $value;
     }
     
     function 
    close()
     {
        
    mysql_close($this->conn);
     }

     function 
    __destruct()
     {
        
    $this->close();
     }
    }
    examples of use

    PHP Code:
    $dbc = new dbc();

    $result $dbc->query("My very complicated sql query that selects data");

    $affected $dbc->query("My very complicated sql query that affects some rows""affected_rows");

    $result $dbc->select(array('field1''field2'), 'mytable', array('id=1',"foo='bar'"));

    $dbc->delete('mytable');

    //...etc 

    Hello ,

    Thers a bug into code see this
    PHP Code:
     function update($tables$values$fields$where= array(), $limit""
     { 
        foreach(
    $values as $key=>$val
        { 
            
    $pairs[] = $this->$fields[$key].' = '.$this->protect($val); 
        } 
        
    $pairs implode(",",$pairs); 
        
    $tables implode(","$tables); 
        
    $where implode(" AND "$where); 
        
    $sql "UPDATE $tables SET $pairs "
        if(!empty(
    $where)) 
        { 
            
    " WHERE $where"
        } 
        if(!empty(
    $limit)) 
        { 
            
    $sql .= " LIMIT $limit"
        } 
        
    mysql_query($sql$this->conn) or $this->error(mysql_error(), $sql); 
        return 
    mysql_affected_rows($this->conn); 
     } 
    it must have
    PHP Code:
    foreach($values as $key=>$val
        { 
            
    $pairs[] = $fields[$key].' = '.$this->protect($val); 
        } 
    and
    PHP Code:
     if(!empty($where)) 
        { 
           
    $sql .=  " WHERE $where"
        } 
        if(!empty(
    $limit)) 
        { 
            
    $sql .= " LIMIT $limit"
        } 
    Last edited by Inigoesdr; 02-19-2008 at 10:02 AM. Reason: Added [php] tags. Please read the stickies in the PHP forum.

  • #3
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    I notice you are using PHP4 class syntax, but have included a PHP5-style destructor. When used with PHP4, the destructor will never be called.

    Add this line to the end of your constructor if you'd actually like to have the destructor called:
    PHP Code:
    register_shutdown_function( array( & $this'__destruct' ) ); 
    Also, the protect() method is stripping slashes from everything it receives if magic_quotes is enabled, but slashes are only added to $_GET, $_POST, and $_COOKIE values. What if a value is passed that does not come from the those three arrays? You potentially have incorrectly modified data.

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Victoria, BC, Canada
    Posts
    962
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks for sharing the code. I've been working on a somewhat similar class recently, except it does database abstraction as well.

    I have an abstract class with some basic implementation, and a subclass for MySQL to implement functionality specific to MySQL. So if I move my database to, say, PostgreSQL, I can just write a PqSQL implementation of my abstract class. I am still debating certain aspects of my setup, so your code may help me to decide what to do!

    As a suggested enhancement for your protect function, you could add some code to handle arrays as well. If the function receives an array, you can recursively call protect on the array, which will 'clean' each value within. I'm using something like this:

    PHP Code:
    function clean($input) {
        if (
    is_array($input)) {
          return 
    array_map("clean"$input);
        }

        
    // process individual values here ...

    This could save you having to sanitize each item yourself.

  • #5
    Regular Coder
    Join Date
    Jan 2007
    Posts
    137
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Grant Palin View Post
    Thanks for sharing the code. I've been working on a somewhat similar class recently, except it does database abstraction as well.

    I have an abstract class with some basic implementation, and a subclass for MySQL to implement functionality specific to MySQL. So if I move my database to, say, PostgreSQL, I can just write a PqSQL implementation of my abstract class. I am still debating certain aspects of my setup, so your code may help me to decide what to do!

    As a suggested enhancement for your protect function, you could add some code to handle arrays as well. If the function receives an array, you can recursively call protect on the array, which will 'clean' each value within. I'm using something like this:

    PHP Code:
    function clean($input) {
        if (
    is_array($input)) {
          return 
    array_map("clean"$input);
        }

        
    // process individual values here ...

    This could save you having to sanitize each item yourself.
    can u please provide me with ur class


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •