SitePoint Sponsor

User Tag List

Page 3 of 4 FirstFirst 1234 LastLast
Results 51 to 75 of 87
  1. #51
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by shad0w
    What does the '&' sign mean in your examples? I searched through the manual, the forums, and google and can't find anything.
    http://uk.php.net/manual/en/language.references.php

  2. #52
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Ukraine
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Another code ...

    Nice thread ... so, community, what will you say about this?

    PHP Code:

    <?php

    /*
    +--------------------------------------------------------------------------
    |   FlightDesign Internet Development Department
    |   =========================================
    |   by Cyrill Polikarpov
    |   (c) 2002-2003 FlightDesign
    |   [email]cyrill@f-design.com.ua[/email]
    |   =========================================
    |   Visit: [url]http://f-design.com.ua[/url]
    |   Email: [email]cyrill@f-design.com.ua[/email]
    |   Email: [email]clio@nix.zp.ua[/email]
    |   ICQ #: 99885395
    +---------------------------------------------------------------------------
    |
    |   > $$MYSQ.PHP
    |
    |    > Version: 1.0.5
    +--------------------------------------------------------------------------
    */

    class mysql {

        var 
    $obj = array ( "sql_database"   => ""         ,
                           
    "sql_user"       => "root"     ,
                           
    "sql_pass"       => ""         ,
                           
    "sql_host"       => "localhost",
                           
    "sql_port"       => ""         ,
                           
    "persistent"     => "0"        ,
                           
    "sql_tbl_prefix" => "fd_"    ,
                           
    "cached_queries" => array()    ,
                         );
                         
         var 
    $query_id      "";
         var 
    $connection_id "";
         var 
    $query_count   0;
         var 
    $record_row    = array();
         var 
    $return_die    0;
         var 
    $error         "";
                      
        
    /*========================================================================*/
        // Connect to the database                 
        /*========================================================================*/  
                       
        
    function connect() {
        
            if (
    $this->obj['persistent'])
            {
                
    $this->connection_id mysql_pconnect$this->obj['sql_host'] ,
                               
    $this->obj['sql_user'] ,
                               
    $this->obj['sql_pass'
                             );
            }
            else
            {
                
    $this->connection_id mysql_connect
                                                      
    $this->obj['sql_host'] ,
                                             
    $this->obj['sql_user'] ,
                                      
    $this->obj['sql_pass'
                                    );
            }
            
            if ( !
    mysql_select_db($this->obj['sql_database'], $this->connection_id) )
            {
                echo (
    "ERROR: Cannot find database ".$this->obj['sql_database']);
            }
        }
        
        
        
        
    /*========================================================================*/
        // Process a query
        /*========================================================================*/
        
        
    function query($the_query$bypass=0) {
            
            
    //--------------------------------------
            // Change the table prefix if needed
            //--------------------------------------
            
            
    if ($bypass != 1)
            {
                if (
    $this->obj['sql_tbl_prefix'] != "fd_")
                {
                   
    $the_query preg_replace("/fd_(\S+?)([\s\.,]|$)/"$this->obj['sql_tbl_prefix']."\\1\\2"$the_query);
                }
            }
            
            
    $this->query_id mysql_query($the_query$this->connection_id);
          
            if (! 
    $this->query_id )
            {
                
    $this->fatal_error("MySQL query error: $the_query");
            }
            
            
    $this->query_count++;
            
            
    $this->obj['cached_queries'][] = $the_query;
            
            return 
    $this->query_id;
        }
        
        
        
    /*========================================================================*/
        // Fetch a row based on the last query
        /*========================================================================*/
        
        
    function fetch_row($query_id "") {
        
            if (
    $query_id == "")
            {
                
    $query_id $this->query_id;
            }
            
            
    $this->record_row mysql_fetch_array($query_idMYSQL_ASSOC);
            
            return 
    $this->record_row;
            
        }

        
    /*========================================================================*/
        // Fetch the number of rows affected by the last query
        /*========================================================================*/
        
        
    function get_affected_rows() {
            return 
    mysql_affected_rows($this->connection_id);
        }
        
        
    /*========================================================================*/
        // Fetch the number of rows in a result set
        /*========================================================================*/
        
        
    function get_num_rows() {
            return 
    mysql_num_rows($this->query_id);
        }
        
        
    /*========================================================================*/
        // Return the amount of queries used
        /*========================================================================*/
        
        
    function get_query_cnt() {
            return 
    $this->query_count;
        }
        
        
    /*========================================================================*/
        // Free the result set from mySQLs memory
        /*========================================================================*/
        
        
    function free_result($query_id="") {
        
               if (
    $query_id == "") {
                
    $query_id $this->query_id;
            }
            
            @
    mysql_free_result($query_id);
        }
        
        
    /*========================================================================*/
        // Shut down the database
        /*========================================================================*/
        
        
    function close_db() { 
            return 
    mysql_close($this->connection_id);
        }
        
        
    /*========================================================================*/
        // Basic error handler
        /*========================================================================*/
        
        
    function fatal_error($the_error) {
            global 
    $INFO;
            
            
            
    // Are we simply returning the error?
            
            
    if ($this->return_die == 1)
            {
                
    $this->error mysql_error();
                return 
    TRUE;
            }
            
            
    $the_error .= "\n\nmySQL error: ".mysql_error()."\n";
            
    $the_error .= "mySQL error code: ".mysql_errno()."\n";
            
    $the_error .= "Date: ".date("l dS of F Y h:i:s A");
            
            
    $out "<html><head><title>Database Error</title>
                   <style>P,BODY{ font-family:arial,sans-serif; font-size:11px; }</style></head><body>
                   &nbsp;<br><br><blockquote><b>There appears to be an error with the database.</b><br>
                   You can try to refresh the page by clicking <a href=\"javascript:window.location=window.location;\">here</a>, if this
                   does not fix the error, you can contact the site administrator by clicking <a href='mailto:
    {$INFO['EMAIL_IN']}?subject=SQL+Error'>here</a>
                   <br><br><b>Error Returned</b><br>
                   <form name='mysql'><textarea rows=\"15\" cols=\"60\">"
    .htmlspecialchars($the_error)."</textarea></form><br>We apologise for any inconvenience</blockquote></body></html>";
                   
        
            echo(
    $out);
            die(
    "");
        }
        
        
    /*========================================================================*/
        // Create an array from a multidimensional array returning formatted
        // strings ready to use in an INSERT query, saves having to manually format
        // the (INSERT INTO table) ('field', 'field', 'field') VALUES ('val', 'val')
        /*========================================================================*/
        
        
    function compile_db_insert_string($data) {
        
            
    $field_names  "";
            
    $field_values "";
            
            foreach (
    $data as $k => $v) {
                
    $v preg_replace"/'/""\\'"$v );
                
    //$v = preg_replace( "/#/", "\\#", $v );
                
    $field_names  .= "$k,";
                
    $field_values .= "'$v',";
            }
            
            
    $field_names  preg_replace"/,$/" "" $field_names  );
            
    $field_values preg_replace"/,$/" "" $field_values );
            
            return array( 
    'FIELD_NAMES'  => $field_names,
                          
    'FIELD_VALUES' => $field_values,
                        );
        }
        
        
    /*========================================================================*/
        // Create an array from a multidimensional array returning a formatted
        // string ready to use in an UPDATE query, saves having to manually format
        // the FIELD='val', FIELD='val', FIELD='val'
        /*========================================================================*/
        
        
    function compile_db_update_string($data) {
            
            
    $return_string "";
            
            foreach (
    $data as $k => $v) {
                
    $v preg_replace"/'/""\\'"$v );
                
    $return_string .= $k "='".$v."',";
            }
            
            
    $return_string preg_replace"/,$/" "" $return_string );
            
            return 
    $return_string;
        }
        
    // end class


    ?>

  3. #53
    SitePoint Wizard Mike Borozdin's Avatar
    Join Date
    Oct 2002
    Location
    Edinburgh, UK
    Posts
    1,743
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    And what about separating connection class and result set class, like this:
    PHP Code:
    <?
    class Database
    {
      function Database ( $Host, $User, $Password, $Database )
      {
      }
      function Query ( $Sql )
      {
      }
      function Close ()
      {
      }
    }
    class Query
    {
      var $_Sql;
      var $_Res;
      var $_CurrentRow = 0;
      var $_Error;
      var $_IsSuccess = true;
      function Query ( $Database, $Sql )
      {
      }
      function _handleError ()
      {
         $this->_IsSuccess = false;
         $this->_Error = mysql_error ();
         print ( $this->getError () );
      }
      function getError ()
      {
         return $this->_Error . "<br />" . $this->_Sql . "<br />";
      }
      function getNumRows ()
      {
      }
      function setRow ()
      {
      }
      function getRow ()
      {
      }
    }
    //----------------------------------------------------------------------------------------------------------------
    class MySql extends Database
    {
      function MySql ( $Host, $User, $Password, $Database )
      {
         mysql_connect ( $Host, $User, $Password );
         mysql_select_db ( $Database );
      }
      function Query ( $Sql )
      {
         return new MySqlQuery ( $this, $Sql );
      }
    }
    class MySqlQuery extends Query
    {
      function MySqlQuery ( $Database, $Sql )
      {
         $this->_Sql = $Sql;
         $this->_Res = mysql_query ( $Sql ) or $this->_handleError ();
      }
      function getNumRows ()
      {
         return mysql_num_rows ( $this->_Res );
      }
      function setRow ( $Row )
      {
         return mysql_data_seek ( $this->_Res, $Row );
      }
      function getRow ()
      {
         if ( $this->_CurrentRow == $this->getNumRows () ) {
            return false;
         }
         $row = mysql_fetch_array ( $this->_Res );
         $this->_CurrentRow++;
         return ( $row );
      }
      function Close ()
      {
         mysql_close ();
      }
    }
    ?>

  4. #54
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Clio
    Nice thread ... so, community, what will you say about this?
    I say that if you're going to steal the code from Invisionboard and pass it off as your own then you should do so in a slightly more subtle manner.

  5. #55
    SitePoint Member
    Join Date
    Oct 2003
    Location
    Ukraine
    Posts
    19
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy

    I say that if you're going to steal the code from Invisionboard and pass it off as your own then you should do so in a slightly more subtle manner.
    (((( Sorry, i parsed another file-header. Really, this is InvisionBoard class for MySQL database, but i have my own! database classes for PgSQL, MSSQL and mSQL for IBforums, which wasn't included in original IBforums package, 'cause i don't have MySQL on my hosting-server. Sorry for codesample - i had no intention to arrogate to myself somebody else's code, it's just an accidental mistake

  6. #56
    SitePoint Member
    Join Date
    Nov 2003
    Location
    The Netherlands
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    ADODB evolution.

    I've got a question about ADODB. Since codezilla seems to like it. Maybe he or someone else can answer it.

    Browsing some php projects installed on my system, which all use ADODB, I saw the following (to me, shocking) facts.

    The oldest version of adodb on my system is 3.31 which came with phpPgAdmin 3.0rc1

    Lines of code for adodb.inc.php: 625


    The second oldest version on my system is 3.60 which comes with the most recent postnuke version 0.726

    Lines of code for adodb.inc.php: 3354


    The most recent version on my system is 4.01 which I downloaded standalone.

    Lines of code for adodb.inc.php: 3629

    These lines don't include the lines of code for the adodb database drivers or other files which it might include.

    My questions are:
    - What are the reasons for this dramatic increase of code?
    - Has anyone noticed significant increase of overhead / execution time over this evolution?
    - if yes, does it pay (solved bugs/or usefull features) to use newer versions over older ones?

  7. #57
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ADODb contains a very large amount of code designed to emulate various DB-specific functions in order to maintain cross-db compatibility. Which seems a bit silly to me as I don't think anyone in their right mind would rely on emulation of DB features rather than just coding for the DB should they ever migrate from one DB type to another.

  8. #58
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Phil.Roberts, I used to think that way until I had a project that needed to work on Oracle. The problem was that the machine I was developing on did not have Oracle, and since it's extremely expensive my only option was to develop on one DB (MySQL) and deploy on another (Oracle). Using a DB abstraction layer made the deployment process very easy.

  9. #59
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Personally if I were even in that situation I'd prefer to abstrate out the database calls altogether using the DAO pattern rather than mess about with re-writing proprietary SQL on the fly....

  10. #60
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's not about re-writing proprietary SQL, it's about not using database-dependent function calls. Essentially, it's like using DBX instead of having a set of MySQL code and a separate set of Oracle code.

  11. #61
    SitePoint Member
    Join Date
    Nov 2003
    Location
    The Netherlands
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This discussion about ADODB being usefull or not was not really my intention.

    I know there are people who think it's bloated. Although other people prefer it over PEAR::DB or phplib.

    The point to my questions is:

    What do 3000 lines in only a half year contribute to an already fully functional library of approximately 600 lines of code?
    Of course this isn't really true, because I'm only talking about the main file. The only reason I can think of is that a lot of code from the other files was moved to the main file.

  12. #62
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I really don't know much about the specifics of ADOdb. I just know that it works well for my needs. Sorry, I can't help you any more than that.

  13. #63
    SitePoint Enthusiast wogboy's Avatar
    Join Date
    Jun 2002
    Location
    Melbourne
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Between the versions of ADODB mentioned the RecordSet class and Connection classes were broken out into seperate files and then later merged back in again into the main ADODB file.

    I think John Lim (ADODB author) determined that having separate files decreased performance.

    The code size of the core library remained much the same. New optional features (like the awesome query profiling) have been added more recently as well, but they only appear in the main library file as function stubs for the most part, in order load the rest of the code.

  14. #64
    SitePoint Addict moonchild's Avatar
    Join Date
    Nov 2003
    Location
    U$A
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is my MySQL class, i just finished it yesterday:

    PHP Code:
    // Class:    MySQL 
        
    class MySQL 
            
    /* Initialize/Define Class Variables */ 
                
    var $dbh
                var 
    $dbs
                var 
    $dba
                var 
    $query
                var 
    $result
                var 
    $numrows
                var 
    $row = array(); 
            
    // Function: MySQL() 
                
    function MySQL() { 
                    
    $this->dbh mysql_connect(MYSQL_SERVER,MYSQL_UID,MYSQL_PWD) or die(mysql_error()); 
                } 
            
    // Function: Select_DB() 
                
    function Select_DB() { 
                    if(
    $this->dba != NULL) { 
                        
    $this->dbs mysql_select_db($this->dba,$this->dbh) or die(mysql_error()); 
                    } else { 
                        
    $this->dbs mysql_select_db(MYSQL_DBA,$this->dbh) or die(mysql_error()); 
                    } 
                } 
            
    // Function: Disconnect() 
                
    function Disconnect() { 
                    
    mysql_close($this->dbh); 
                } 
            
    // Function: Flush() 
                
    function Flush() { 
                    
    mysql_free_result($this->result); 
                } 
            
    // Function: NumRows() 
                
    function NumRows() { 
                    return 
    $this->numrows mysql_num_rows($this->result); 
                } 
            
    // Function: Query() 
                
    function Query() { 
                    
    $this->result mysql_query($this->query) or die(mysql_error()); 
                } 
            
    // Function: FetchArray() 
                
    function FetchArray() { 
                    return 
    mysql_fetch_array($this->result); 
                } 
            
    // Function: FetchRow() 
                
    function FetchRow() { 
                    return 
    mysql_fetch_row($this->result); 
                } 
        } 

  15. #65
    SitePoint Addict moonchild's Avatar
    Join Date
    Nov 2003
    Location
    U$A
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i would have never thought of doing:

    PHP Code:
    return MysqlStatment($sql,$this); 
    and using
    PHP Code:
    $this->connection->getConnectionId(); 
    This post has helped me a bunch, unfortunately now I'm trying to figure out how to rewrite all of mysql classes. heh

  16. #66
    SitePoint Addict moonchild's Avatar
    Join Date
    Nov 2003
    Location
    U$A
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by shad0w
    What does the '&' sign mean in your examples? I searched through the manual, the forums, and google and can't find anything.
    http://us4.php.net/language.variables ... it took me a while to find it as well.

  17. #67
    SitePoint Enthusiast haaX's Avatar
    Join Date
    Jun 2004
    Location
    Vardø, Norway
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    // Previously MysqlConnectClass()
    class MysqlConnection
    {

       (...)

        function 
    MysqlConnection($host$database$username$password)
        {
            
    $this->host $host;
            
    $this->database $database;
            
    $this->username $username;
            
    $this->password $password;
            
    $this->open();
        }

        (...)


    --

    PHP Code:
    class Sillysoft_MysqlConnection extends MysqlConnection
    {

        var 
    $dblocation '*****';
        var 
    $dbname '****';
        var 
    $dbpass '**';
        var 
    $dbuser '***';

        function 
    Sillysoft_MysqlConnection()
        {
            
    $this->MysqlConnection($dblocation$dbname$dbuser$dbpass);
        }


    My question is concerning the constructors. In java you would want to do a this.super(args) to call the superclass (the class the current class extends from) -- there arent something equal in php? I think I've seen parent:: somewhere?

    Secondly, I wonder what the & infront of functions is - is it simply that returns are passed by reference?

    Great post btw, I'm currently writning my first large OOP application in PHP. Those tips on OOP thinking here was also very good - it's important to think of that when you're stuck in the middle of the coding having a few problems. I've been there a few times, making one big class with many tasks. Bad OOP, but it does the job(...not as efficent as you may want, I believe).

    Concerning performance -- I've heard that OOP is slower than non-OOP in PHP; what makes it slower, and how to reduce this performance loss?


    Regards,
    Haakon

  18. #68
    SitePoint Enthusiast haaX's Avatar
    Join Date
    Jun 2004
    Location
    Vardø, Norway
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I almost forgot.. How to integrate error handling in these classes (from page one)? Do you implement it as one external class for errors, or do you incorportate them into the exsisting classes?

    Regards,
    Haakon

  19. #69
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    Germany
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    class MysqlError
    {
        var 
    $statement;
        var 
    $file;
        var 
    $line;
        var 
    $error;
        
        function 
    MysqlError($statement$file$line) {
            
    $this->statement = & $statement;
            
    $this->file $file;
            
    $this->line $line;
            
    $this->CreateError();
            
    $this->DisplayError();

        }
        
        function 
    CreateError()
        {
            
    $this->error 'MysqlError: 'mysql_error($this->statement->connection->getConnectionId()).'<br />';
            
    $this->error .= 'Statement: '.$this->statement->getPreparedSql().'<br />';
            
    $this->error .= 'File: '.$this->file.'<br />';
            
    $this->error .= 'Line: '.$this->line.'<br />';
        }
        
        function 
    DisplayError()
        {
            echo 
    $this->error;
            exit;
        }

    Just created after i read this excellent Thread

    I used Codezilla's Connection, Statement and ResultSet Classes, only change was in MysqlStatement:

    PHP Code:
    function &execute($file ''$line '')
        {
            if(
    $resultId mysql_query($this->getPreparedSql(), $this->connection->getConnectionId())) {
                return new 
    MysqlResultSet($resultId);
            }else{
        return new 
    MysqlError($this$file$line);
            };
        } 
    And now i better wait for critics on my class befor using it

    Edit:


    One idea i just got:
    What about letting DisplayError decide wether (how is it spelled?) to exit/die the script, or continue with only a Warning/Notice?
    I always let the script die when an sql-error occurs

    Another Point:
    My Error class only handles mysql_query errors, but if an error during the connection occurs (bad password, user or database) i don't want to have the mysql_error text but one like: "Could not connect to Server" or "Could not select Database" How do i solve this the best way?

  20. #70
    SitePoint Evangelist
    Join Date
    May 2004
    Location
    Germany
    Posts
    550
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've kept working on it

    PHP Code:
    class MysqlError
    {
        var 
    $statement;
        var 
    $file;
        var 
    $line;
        var 
    $error;
        var 
    $errortype;
        
        function 
    MysqlError($statement$file$line$type 'statement') {
            
    $this->statement = & $statement;
            
    $this->file $file;
            
    $this->line $line;
            
    $this->errortype $type;
            
    $this->CreateError();
            
    $this->DisplayError();

        }
        
        function 
    CreateError()
        {
            switch (
    $this->errortype) {
                case 
    'statement':
                    
    $this->error 'MysqlError: 'mysql_error($this->statement->connection->getConnectionId()).'<br />';
                    
    $this->error .= 'Statement: '.$this->statement->getPreparedSql().'<br />';
                    
    $this->error .= 'File: '.$this->file.'<br />';
                    
    $this->error .= 'Line: '.$this->line.'<br />';
                    break;
                case 
    'connect':
                    
    $this->error 'An error occured during connection to the server!<br />';
                    break;
                case 
    'select':
                    
    $this->error 'An error occured selecting the database!<br />';
                    break;
            }
        }
        
        function 
    DisplayError()
        {
            echo 
    $this->error;
            exit;
        }

    Thus changed MysqlConnection:

    PHP Code:
    function open()
        {
            if(
    $this->connectionId = @mysql_connect($this->host$this->username$this->password)) {
                if(!@
    mysql_select_db($this->database$this->connectionId)) {
                    return new 
    MysqlError('''''''select');
                };
            }else{
                return new 
    MysqlError('''''''connect');
            }
        } 
    But i'm not really happy with it, because of the empty paramters in MysqlError, i think i should change the order.

    But i want $type to be the last param, because you usually connect once to the server and query it more than once.
    Any suggestions how to solve this?

  21. #71
    SitePoint Guru
    Join Date
    Dec 2003
    Location
    oz
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by codezilla
    Actually, prepared statements are one of the main benefits of a Statement (or more accurately, PreparedStatement, which extends Statement) class. I didn't bother getting into all that because MySQL doesn't support them
    Prepared statements are only of use if the database implements it. The purpose of it is so that if the prepared statement is correct, each time you run the query, the db does not have to parse the sql because all you're passing in is the params for the query - which doesn't need to be checked for syntax (except when creating the prepared statement in the first place). While MySql doesnt support it, it is completely pointless and has no advantage over plain old sql statements.

    Quote Originally Posted by Phil.Roberts
    If you examine the database classes in the Eclipse library you'll see that they work in this exact same way.
    Yes it does, and IMO eclipse's db abstraction is pretty damn close to ideal. I'd say just use that. But Geoff does explain the details very nicely.

    Quote Originally Posted by Phil.Roberts
    Maybe UML should be taught in primary schools...
    Lol ... interesting idea :-P

  22. #72
    SitePoint Addict moonchild's Avatar
    Join Date
    Nov 2003
    Location
    U$A
    Posts
    258
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lazy_yogi
    While MySql doesnt support it, it is completely pointless and has no advantage over plain old sql statements.
    I disagree. Look at the MysqlStatement class again.

  23. #73
    SitePoint Member
    Join Date
    Jun 2004
    Location
    Atlanta, GA
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lazy_yogi
    Prepared statements are only of use if the database implements it. The purpose of it is so that if the prepared statement is correct, each time you run the query, the db does not have to parse the sql because all you're passing in is the params for the query - which doesn't need to be checked for syntax (except when creating the prepared statement in the first place). While MySql doesnt support it, it is completely pointless and has no advantage over plain old sql statements.
    In the interest of completeness: prepared statements are supported by MySQL 4.1 (maybe 4.0?), though you can only use them from PHP with PHP 5's mysqli extension. But I agree, there isn't any advantage from a speed standpoint to emulating prepared statements in PHP 4. In fact, it's probably slower, right?

  24. #74
    SitePoint Enthusiast haaX's Avatar
    Join Date
    Jun 2004
    Location
    Vardø, Norway
    Posts
    68
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Like.... prepared statements? Now, what good do they do?

    http://dev.mysql.com/doc/mysql/en/C_...tatements.html ... however, I dont think I'm quite getting the points. Its quicker when you want to do many of the same query? Now when would you do that?

    I got really confused by this.

    D'oh.

    Regards,
    Haakon

  25. #75
    SitePoint Enthusiast
    Join Date
    Jun 2003
    Location
    Chicago
    Posts
    73
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by codezilla
    You have the right idea -- but there are a lot of improvements that can be made.

    First, think about the class itself (MysqlConnectClass) and what its purpose is.

    The first thing I notice (and this is a relatively minor point) is that you have the word 'Class' in the name of your class. There's nothing wrong with that if you do that to all your classes as a naming convention, but if you really want to be thinking in terms of OO, everything will be a class so there's no point in typing the extra 5 characters.

    Furthermore, it's a widely accepted convention to reserve [initial] capitalization for the names of your classes only -- nothing else (so I also recommend lower-casing the first letter of your method names). Naturally, as with any convention, not everyone agrees, but you certainly can't go wrong with it.

    Ok, so what does that leave us with? MysqlConnect -- which I would rename to MysqlConnection because class names should, generally, be nouns since classes represent 'things'.

    Now that we've refined the name, we need to think about what the purpose of a MysqlConnection class is. That's fairly obvious, the purpose is to handle a connection to a mysql database -- nothing more, nothing less. Keep in mind that "in most cases it is better to build small objects with specific task than to build large objects with many tasks" [p.11 "The Object-Oriented Thought Process" by Matt Weisfeld].

    Next, think about what kinds of behaviors/actions a MysqlConnection object needs to be able to do in order to "handle a connection to a mysql database". These behaviors will become your methods and should be named with verbs since they represent actions. There are two obvious ones: open and close. But is there anything else that a MysqlConnection should do? Why have a MysqlConnection in first place? To execute a SQL statement and possibly do something with the result set. Now, re-read the last sentence. Notice anything? There were two verbs, and one in particular is catching my eye: execute (the other is 'do' but I'll address that later; in fact there were also two nouns: statement and result -- which I'll mention shortly).

    Should we add an 'execute' method to MysqlConnection? Some people would say yes. Personally, I disagree with this -- but it's up for debate so I'll explain my thinking. What do you 'execute'? I already answered this: a statement. Which is a noun and therefore a good candidate for a class. What do you do with a statement? You execute it. So let's say we choose to create a Statement class and place the execute method on it (technically it's a MysqlStatement, but I'll use Statement for brevity). What else do you do with a statement? Not much, which is why some people choose not to implement a separate Statement class -- it doesn't do enough to make it worthwhile. Generally speaking, if a class only has one method, then it probably shouldn't be a class.

    But, a Statement class is useful for than just executing SQL statements. Think about it for a second. What else do you frequently do with SQL statements other than executing them? You probably put the value of a variable in your statements all them time (e.g. "SELECT * FROM table WHERE id = $id"). A lot of times those variables contain data that came from a form submission by an end-user -- which means you can't trust it. That's why we have functions like mysql_escape_string() (which is very similar to addslashes()). Wouldn't it be great if the Statement class was responsible for doing things like addslashes() so you don't have to worry about it? I definitely think so. In fact, I can't think of a better place to put that kind of functionality. For that reason, I think that a Statement class is very worthwhile.

    If we're going to have a separate Statement class with an execute method (instead of putting execute on MysqlConnection), how do we create a statement? See that verb? Why not put a create() method on your MysqlConnection class? Well, we should probably call it createStatement() so its purpose is a little clearer. Sounds good to me. Then we can be done with MysqlConnection, right? Not so fast.

    We have to talk about how MysqlConnection gets its job done. What data does it need to open() a connection to a database? The name of the database server (host), the name of the database, a username, and password. And when we open() the connection we need to store the resource id, so we can later close() the connection (which reminds me, let's also add a getConnectionId() method, so our Statement objects will be able use the connection resource id when execute()'ing sql statements). Guess what, we just figured out all our properties: host, database name, username, password, and resource ID (you had 4 out of 5, Sillysoft). NOW we're done with MysqlConnection, right? Nope, no quite, but almost.

    We need to figure out how a MysqlConnection will get access to the data that those properties store. Sillysoft, you hard coded them in the class, which is okay unless you want the class to be reusable. I would recommend passing them to MysqlConnection when you instantiate the class. That way you could connect to multiple mysql databases using the same class. If you want them hard-coded define some constants in a convenient file that always gets include()'d so you don't have to hunt for them.

    Here's what we have so far (without error handling to keep things simple):

    PHP Code:
    // Previously MysqlConnectClass()
    class MysqlConnection
    {

        var 
    $connectionId;
        var 
    $database// Previously $dbname
        
    var $host;     // Previously $dblocation
        
    var $password// Previously $dbpass
        
    var $username// Previously $dbuser

        
    function MysqlConnection($host$database$username$password)
        {
            
    $this->host $host;
            
    $this->database $database;
            
    $this->username $username;
            
    $this->password $password;
            
    $this->open();
        }

        
    // Previously CloseMysqlConn()
        
    function close()
        {
            
    $this->connectionId mysql_close($this->connectionId);
        }

        function &
    createStatement($sql)
        {
            return new 
    MysqlStatement($sql$this);
        }

        function 
    getConnectionId()
        {
            return 
    $this->connectionId;
        }

        function 
    open()
        {
            
    $this->connectionId mysql_connect($this->host$this->username$this->password);
            
    mysql_select_db($this->database$this->connectionId);
        }


    Now if you still want to hard code the connection parameters in a class you can easily extend the base class:

    PHP Code:
    class Sillysoft_MysqlConnection extends MysqlConnection
    {

        var 
    $dblocation '*****';
        var 
    $dbname '****';
        var 
    $dbpass '**';
        var 
    $dbuser '***';

        function 
    Sillysoft_MysqlConnection()
        {
            
    $this->MysqlConnection($dblocation$dbname$dbuser$dbpass);
        }


    Now let's flesh out the Statement class. We want to be able to pass parameters to the Statement and have it automatically put quotes around it and make sure the string is escaped. Then, when we execute the Statement, we want it to return a ResultSet (actually a MysqlResultSet), which is the other noun/class I mentioned a while ago. Like so:

    PHP Code:
    class MysqlStatement
    {

        var 
    $connection;
        var 
    $parameters;
        var 
    $sql;

        function 
    MysqlStatement($sql, &$connection)
        {
            
    $this->connection =& $connection;
            
    $this->parameters = array();
            
    $this->sql $sql;
        }
        
        function &
    execute()
        {
            
    $resultId mysql_query($this->getPreparedSql(), $this->connection->getConnectionId());
            return new 
    MysqlResultSet($resultId);
        }
        
        function 
    getPreparedSql()
        {
            
    $sql_parts explode('?'$this->sql);
            
    $sql $sql_parts[0];
            for (
    $i 1$max count($sql_parts); $i $max$i++)
            {
                
    $sql .=  $this->parameters[$i] . $sql_parts[$i];
            }
            return 
    $sql;
        }
        
        function 
    setParameter($index$val)
        {
            
    $this->parameters[$index] = "'" mysql_escape_string($val) . "'";
        }

    }

    /*
    // Usage:
    $conn =& new MysqlConnection('localhost', 'my_db', 'my_user', 'my_pass');
    $stmt =& $conn->createStatement(
        'SELECT last_login FROM user WHERE username = ? AND password = ?');
    $stmt->setParameter(1, $username);
    $stmt->setParameter(2, $password);
    $rs =& $stmt->execute();
    */ 
    The ResultSet class is pretty simple too. What behaviors should it have? As a minimum, you should be able to iterate through the rows, get the total number of rows, get the fields within a row, and close/release/free the ResultSet. There may be other stuff that would be useful, but I'm keeping this example bare bones. Take a look:

    PHP Code:
    class MysqlResultSet
    {

        var 
    $record;
        var 
    $resultId;

        function 
    MysqlResultSet($resultId)
        {
            
    $this->record = array();
            
    $this->resultId $resultId;
        }

        
    // Previously FreeMysqlMem()
        
    function close()
        {
            
    mysql_free_result($this->resultId);
            
    $this->record = array();
            
    $this->resultId NULL;
        }

        function 
    getField($field)
        {
            return 
    $this->record[$field];
        }

        
    // previously GetNumRows()
        
    function getRowCount() 
        {
            return 
    mysql_num_rows($this->resultId);
        }

        
    // previously GetRowInfo() - sort of
        
    function next()
        {
            
    $this->record mysql_fetch_assoc($this->resultId);
            return (
    $this->record !== FALSE);
        }


    Putting it all together, here's sample usage of all three classes.

    PHP Code:
    $conn =& new MysqlConnection('localhost''my_db''my_user''my_pass');
    $stmt =& $conn->createStatement(
        
    'SELECT model FROM car WHERE date >= ? AND date <= ? ORDER BY model ASC');
    $stmt->setParameter(1$start_date);
    $stmt->setParameter(2$end_date);
    $rs =& $stmt->execute();
    while (
    $rs->next())
    {
        echo 
    $rs->getField('model') . '<br>';
    }
    $rs->close(); //optional
    $conn->close(); //optional 
    These classes are seriously lacking in the way of error handling, but otherwise they work as is. And you can easily refactor them into a full-blown database abstraction layer, even if it only works with MySQL for now.

    Keep in mind that everyone has there own opinions about what the best way of doing OOP is. For example, if you look at the PEAR database abstraction classes you'll see they have a much different approach than I (granted they do a lot more too -- but are very bloated). As you learn more and more about OOP you'll find better ways of doing things.

    I don't know of any good articles off the top of my head. The vast majority of PHP specific articles, especially those dealing with OOP, are of very low quality. I wouln't look to them for examples of good OOP, I recommend looking to the pros for advice. In fact, when I was developing these classes originally, my main reference was the Java API docs (http://java.sun.com/j2se/1.4.1/docs/api/). Even if you don't know Java (I don't), they're really informative. Of course Java != PHP, but there's a lot to learn by seeing how they do things in Java. Check out the java.sql package, in particular the Connection, PreparedStatement, and ResultSet interfaces. The classes I presented above are basically simplfied versions of them. I refer to those docs all time when I am working on my classes.

    A really great book for learning the foundations of OOP is the book I quoted above, "The Object-Oriented Thought Process" by Matt Weisfeld (ISBN: 0672318539). It's only about 200 pages so it's concise, but very helpful. There's very little code -- it's all about the concepts. I highly recommend it.

    Another fantastic book, especially if you're trying to make the transition from procedural to OO, is Refactoring by Martin Fowler. It's considered a classic (and has been mentioned on these forums many times).

    Well, this post is a tad longer then I thought it would be when I started. Hope it all makes sense and helps you.

    -geoff

    Hi,

    First, that was an amazing post. I'm changing my DB class to reflect some of the things you said... although, I have one question.

    Wouldn't using sprintf in getPreparedStatement be more efficient then the explode/loop method? I think it achieves basically the same... or is it wrong for this purpose? Please help me to udnerstand which method would be better.

    Thanks,
    Phil


Bookmarks

Posting Permissions

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