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.
Page 2 of 2 FirstFirst 12
Results 16 to 27 of 27
  1. #16
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by marek_mar
    You sure they're different?
    I think so... Maybe not...

  2. #17
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    To add to this, I hope you wont mind me posting my own personal database class that I use almost all the time.

    PHP Code:
    <?php

    /*

        MySQL Database Class
        --------------------
        Author:  Matthew Wilson
        Site:    http://www.icdsoftware.com
        Contact: admin at icdsoftware.com
        License: Free for non-commercial use.
        
        About
        -----
        Contains MySQL query operations within an object
        as well as simplifying some query types.
        
    */

    define('SQL_EXPRESSION'"--{0009def7886864195af0955f4b92bbb4cc1aaf099b3515e6ff0}--");

    define('SQL_ROW''mysql_fetch_row');
    define('SQL_ASSOC''mysql_fetch_assoc');


    function 
    expr($val ''){
        return array(
            
    SQL_EXPRESSION,
            
    $val
        
    );
    }


    class 
    mysql {

        var 
    $dbc;
        var 
    $cache;
        var 
    $querynum;
        var 
    $exec;
        var 
    $pconnect;
        var 
    $backquote;
        var 
    $serialize;
        
        function 
    __construct($settings = array()) {
        
            list(
    $user$pass$host$db$pconnect$bq$serialize) = $settings;
            
    $this->pconnect $pconnect;
            
    $this->sqlconnect($user$pass$db$host);
            
            
    $this->exec 0;
            
    $this->cache false;
            
    $this->dbc false;
            
    $this->querynum 0;
            
    $this->backquote $bq;
            
    $this->serialize $serialize;
        
        }
        
        
        function 
    mysql$settings = array() ){
            if( 
    $this->dbc == NULL ){
                
    $this->__construct($settings);
            }
        }
        
        
        function 
    sqlconnect($user$pass$db$host 'localhost'){
        
            
    $connection_function 'mysql_connect';
            if(
    $this->pconnect == 1) {
                
    $connection_function 'mysql_pconnect';
            }
            
            if(
    $this->dbc = @$connection_function($host$user$pass)){
                if(@
    mysql_select_db($db$this->dbc)){
                    return 
    true;
                } else {
                    return 
    false;
                }
            } else {
                return 
    false;
            }
        
        }
        
        
        
        function 
    query($sql ''){
        
            
    $timer explode(' 'microtime());
            if(
    $this->cache = @mysql_query($sql)){
                
                
    $end_timer explode(' 'microtime());
                
    $difference = ($end_timer[0]-$timer[0])+($end_timer[1]-$timer[1]);
                
    $this->exec += $difference;
                
                ++
    $this->querynum;
                return 
    $this->cache;
                
            } else {
            
                return 
    false;
            
            }
        
        }
        
        
        
        function 
    fetch($result NULL$method 'mysql_fetch_assoc'){
        
            if(
    $result === NULL){
                
    $result $this->cache;
            }
            
            if(
    $result !== false){
                if(
    $result $method($result)){
                    return 
    $result;
                } else {
                    return 
    false;
                }
            } else {
                return 
    false;
            }
        
        }
        
        
        
        function 
    numrows($result NULL){
        
            if(
    $result == NULL){
                
    $result $this->cache;
            }
            
            if(
    $rows = @mysql_num_rows($result)){
                return 
    $rows;
            } else {
                return 
    false;
            }
        
        }
        
        
        function 
    insertid(){
            return @
    mysql_insert_id();
        }
        
        
        function 
    affected(){
            return @
    mysql_affected_rows();
        }
        
        
        function 
    escape($str ''){
            return @
    mysql_escape_string($str);
        }
        
        
        function 
    free($result NULL){
        
            if(
    $result === NULL){
                
    $result $this->result;
            }
            
            return @
    mysql_free_result($result);
        
        }
        
        
        function 
    close(){
            return @
    mysql_close($this->dbc);
        }
        
        
        function 
    getExec($round 5){
            return array(
                
    'query_count' => $this->querynum,
                
    'execution_time' => $this->exec
            
    );
        }
        
        
        function 
    insert($table$key_value_array){
            return 
    $this->makeQuery($key_value_array$table'insert');
        }
        
        
        function 
    update($table$key_value_array$clause ''){
            return 
    $this->makeQuery($key_value_array$table'update'$clause);
        }
        
        
        function 
    makeQuery($key_to_value_data = array(), $table ''$type 'insert'$clause ''){
        
            
    $query '';
            
    $bq = ($this->backquote == 1) ? '`' '';
            
    $table "$bq$table$bq";
            
            
    $_keys = array();
            
    $_values = array();
            
            foreach(
    $key_to_value_data as $key => $value){
            
                switch(
    gettype($value)){
                
                    case 
    'integer': case 'double': case 'boolean':
                    
    $_values[] = $value;
                    break;
                    
                    case 
    'string': case 'resource': default:
                    
    $_values[] = "'".$this->escape($value)."'";
                    break;
                    
                    case 
    'NULL':
                    
    $_values[] = 'NULL';
                    break;
                    
                    case 
    'array':
                    if(isset(
    $value[0]) && $value[0] == SQL_EXPRESSION){
                        
    $_values[] = $value[1];
                        break;
                    }
                    
                    case 
    'object':
                    if(
    $this->serialize == 1){
                        
    $_values[] = serialize($value);
                    } else {
                        
    settype($value'string');
                        
    $_values[] = $value;
                    }
                    break;
                
                }
                
                
    $_keys[] = $key;
            
            }
            
            
            if(
    $type == 'insert'){
            
                
    // Yes, this is the coolest variable name ever
                
    $implosion "$bq,$bq";
                
    $fields implode($implosion$_keys);
                
    $fields "($bq$fields$bq)";
                
                
    $values implode(','$_values);
                
                if(
    $values != ''){
                
                    
    $query "INSERT INTO $table $fields VALUES ( $values )";
                
                } else {
                
                    return 
    false;
                
                }
            
            } else {
            
                
    $update '';
                
    $count_keys count($_keys);
                
                for(
    $i=0;$i<$count_keys;$i++){
                    
    $update .= "$bq{$_keys[$i]}$bq = {$_values[$i]}, ";
                }
                
                
    $update substr_replace($update"", -2);
                
                if(
    $update != ''){
                
                    
    $query "UPDATE $table SET $update";
                    if(
    $clause != ''){
                        
    $query .= " WHERE $clause";
                    }
                
                } else {
                
                    return 
    false;
                
                }
            
            }
            
            if(
    $result $this->query($query)){
                return 
    $result;
            } else {
                return 
    false;
            }
            
        
        }
        

    }

    ?>
    Some Examples:

    PHP Code:
    <?php

    $settings 
    = array(
      
    'username'// Database Username
      
    'password'// Database Password
      
    'host'// Database Host
      
    'database'// Database Name
      
    'pconnect'// Use a permanent connection ? (1 or 0)
      
    'backquotes'// Whether or not to use backquotes in generated queries ( 1 or 0 )
      
    'serialiaze' // When building queries using the easy methods (insert() & update()), should arrays/objects be automatically serialized?
    );

    // My Usual settings:
    $mysql = array( 'root''rootpass''localhost''database'01);

    $db = new mysql$settings );

    // You can then call all of the MySQL methods, such as:

    $insert = array( 'field' => 'value' );
    $db->insert'table_name'$insert );

    // It will automatically escape values passed, so you dont have to run an escape on every value in the insert/update array.
    // You might want to update:

    $db->update'table_name'$insert'id=5' );

    // If you need to use something like "fieldname = fieldname+x", use the expr() function:

    $update = array( 'field'expr'field+7' ) );
    $db->update'table_name'$update );

    // Example query and result set:

    $result $db->query"SELECT * FROM table" );
    while( 
    $row $db->fetch$result ) ){
      
    // Code
    }

    Alsoyou can specify a function to use when fetching like so:

    $db->fetch$resultSQL_ROW ); // As set at the top of the page, or:
    $db->fetch$result'mysql_fetch_object' ); // Passing a string

    ?>

  3. #18
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by missing-score
    To add to this, I hope you wont mind me posting my own personal database class that I use almost all the time.
    I don't even dare mind it.
    I used adoDB but it seemed a bit too much for me. Since now we have the "ultimate" PHP5.1(.1) with PDO I think I'll check that out and if it's good I'll use it instead of anything I use now (with the exception of some neat functions ).
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  4. #19
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by marek_mar
    Since now we have the "ultimate" PHP5.1(.1) with PDO I think I'll check that out and if it's good I'll use it instead of anything I use now (with the exception of some neat functions ).
    yeah definatley, I cant wait till 5.1.1 is mainstream, but at the moment with so many servers running 4.3.0 - 4.4.1 (I think i got the version numbers right) its no good using PDO.

  5. #20
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    [ Edited by missing-score: I just removed the very long quote of the earlier post ]

    Edit: Thanks, thats what I'm doing in the edit page now. xD


    Thats pretty sexy, missing-score. I like this, it almost feels like using MySQL with the same freedom.
    Last edited by Element; 12-19-2005 at 11:50 PM.

  6. #21
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Element

  7. #22
    Senior Coder
    Join Date
    Apr 2005
    Location
    Colorado, United States
    Posts
    1,208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is a nice db class. Mind if I use a few of the ideas (mainly the cool variable name, and the makeQuery())? Mine is internal...I doubt I'll ever post it in any way. Just curious (and probably missing something due to lack of sleep), but what purpose does the SQL_EXPRESSION serve?
    "$question = ( to() ) ? be() : ~be();"

  8. #23
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah of course you are free to use the ideas

    The reason i have an SQL_EXPRESSION constant is to allow things like "var = var+1" as an update query. When you call the update() method, it takes an associative array. If you want to use an expression (eg: var + 1 ) you would do:

    PHP Code:
    $update = array(
      
    'var' => expr'var+1' )
    ); 
    The expr() function creates an array of the SQL_EXPRESSION constant, and the expression you entered. When it comes to building the query, datatypes are checked and escaped appropriately. 'var+1' would be treated as a string, and not give the desired effect.

    If you look into the makeQuery function, you see when the switch loop gets to processing arrays, the first thing it does is checks fort the existance of the SQL_EXPRESSION constant as the first value. If it finds it, then it puts the value into the query as an expression rather than a string, and doesn't go on to potentially serialize the array (one of the options that you can set at the start -- automatically serialize arrays)

  9. #24
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    So I've invented it in almost the same way... that means that it was a good choice
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  10. #25
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    missing-score, your class looks generally good.. one comment is to use mysql_real_escape_string. I was writing a similar abstraction for the Oracle/oci functions today although I was using objects and extending the base class then checking the class name instead of using constants like you did with expr() .. not sure if I'll convert over to your approach when I head in tomorrow but I have a feeling I will.
    also, insert and update can have extremely similar syntax:
    Code:
    INSERT INTO table SET `col1`= 'blah';
    UPDATE table SET `col1`= 'blah' WHERE `col2`='blah';
    it would allow you to shed a couple lines (syntax reference)

  11. #26
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ahh cool, I didnt realise that. If I ever get round to updating the class I will most likley change it.

  12. #27
    fci
    fci is offline
    Senior Coder
    Join Date
    Aug 2004
    Location
    Twin Cities
    Posts
    1,345
    Thanks
    0
    Thanked 0 Times in 0 Posts
    one thing that could be done (for missing-score's):
    Code:
    class mysql_query_resource {
        var $_query;
        var $_rs;
        var $_db;
        function mysql_query_resource($query, $db) {
            $this->_query = $query;
            $this->_db = $db;
            $this->_rs      = @mysql_query($query, $db)
                $this->is_error = !($this->_rs);
            if (MYSQL_DEBUG && $this->isError()) { $this->getError(); }
        }
        function isError() {
            return($this->is_error);
        }
        function getError() {
            /* some error handling here */
        }
        function getResult($ field) {
            return mysql_result($rs, 0, $field);
        }
        function getAssoc() {
            return mysql_fetch_assoc($rs);
        }
        /* and so on ..*/
    }
    class mysql {
        var $_db;
        /* blah blah */
        function query($query) {
            return new mysql_query_resource($query, $this->_db);
        }
        /* blah blah */
    }
    // so..
    $db = new mysql($connect);
    $rs = $db->query("SELECT * From..");
    while ($row=$rs->getAssoc()) {
        print_r($row);
    }
    // and .. you get the idea.
    Last edited by fci; 01-28-2006 at 12:51 AM.


 
Page 2 of 2 FirstFirst 12

Posting Permissions

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