SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 87
  1. #1
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    OOP Mysql Function Class (Check Code)

    Ok I wanted to show a simple class I created for mysql connection and the normal functions I use with mysql. I tested it and it worked just fine. Very simple class, but I wanted to ask you guys does it look like I am atleast on the right path in using OOP correctly?

    PHP Code:
    <?php
        
        
    /****************************************************\
        * Author: Sillysoft
        * 
        * Create Date: 02/13/03
        * 
        * Function: Connect Class To Mysql Backend Database
        * 
        * Version: **
        /*****************************************************/


        
    class MysqlConnectClass {
        
            var 
    $dblocation '***';
            var 
    $dbuser '***';
            var 
    $dbpass '****';
            var 
    $dbname '**';
        
        
            function 
    MysqlConnectClass() {
            
                
    $connect mysql_connect($this->dblocation,$this->dbuser,$this->dbpass) or die('Error! Database Unreachable. Please Contact Administrator');
                
    $selectdb mysql_select_db($this->dbname,$connect);        
            
            }
            
            function 
    GetResult($query) {
            
                return 
    mysql_query($query);
            
            }
            
            function 
    GetNumRows($result) {
            
                return 
    mysql_num_rows($result);
            
            }
            
            function 
    GetRowInfo($info) {
            
                return 
    mysql_fetch_row($info);
                
            }
            
            function 
    FreeMysqlMem($id) {
            
                return 
    mysql_free_result($id);
                
            }
            
            function 
    CloseMysqlConn($conn) {
            
                return 
    mysql_close($conn);
                
            }
            
        }            

    ?>
    Silly

  2. #2
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The class seems wrong to me only because the functions dont do anything really. But as I think about it and I hope this is "oop thinking" I am breaking out the functions to talk to the db therefor I can use these functions anywhere I use mysql as the backend. I can even take one out and put it somewhere else since it is not dependant on each other.

    I hope this is correct. If so then I think I got the hang of this

    Silly

  3. #3
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone? If I am doing this the wrong way, can anyone point me to some tutorials?

    Silly

  4. #4
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  5. #5
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey man, great post!

    I have never considered wrapping queries into a Statement class, but it makes perfect sense to me now. It seems really useful for escaping quotes in strings and perhaps it can even be used to LIMIT results and stuff like that. Could even be used to support prepared statements on some databases.

    Going off to 'refactor' my db classes

  6. #6
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is an awesome post! Thank you SO much!! Put this to my favorites.

    Silly

  7. #7
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Top One - use Nouns and Verbs I flunked my Systems Data Analysis at college simply because I didn't know my Nouns and Verbs.

    I agree with you about the naming conventions with classes (either Java or PHP it doesn't matter the langauge) though I doubt it if I follow it myself though.

  8. #8
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you examine the database classes in the Eclipse library you'll see that they work in this exact same way.
    THE INSTRUCTIONS BELOW ARE OLD AND MAY BE INACCURATE.
    THIS INSTALL METHOD IS NOT RECOMMENDED, IT MAY RUN
    OVER YOUR DOG. <-- MediaWiki installation guide

  9. #9
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then you might want to take a look at the Eclipse API docs again, cause they work differently. Eclipse does not have something like a createStatement() method, all it offers is a query($sql) method for executing SQL. Iteration of a result set is decoupled from the result set itself, too.

  10. #10
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I meant by passing the resultset to a seperate class.....
    THE INSTRUCTIONS BELOW ARE OLD AND MAY BE INACCURATE.
    THIS INSTALL METHOD IS NOT RECOMMENDED, IT MAY RUN
    OVER YOUR DOG. <-- MediaWiki installation guide

  11. #11
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey man, great post!
    Thanks! I'm a fan of your posts too, CP, so I appreciate that.

    I have never considered wrapping queries into a Statement class, but it makes perfect sense to me now. It seems really useful for escaping quotes in strings and perhaps it can even be used to LIMIT results and stuff like that. Could even be used to support prepared statements on some databases.
    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 and the post was way to long anyway.

    And you're right about LIMIT clauses too. A Statement is a great place to abstract them, since every database handles them differently. I'm currently working on tweaking the classes I presented to be wrappers for ABOdb (http://php.weblogs.com/ADODB), which supports LIMITs among other things -- I like ADOdb as a library, but I don't like everything about its interface.

  12. #12
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That is an awesome post! Thank you SO much!! Put this to my favorites.
    Thanks!

  13. #13
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe been reading it over and over and over again. I think Im starting to understand. Originally when I was writing functions they where specific to the task and cluttered when they should of been broken out. I see the functions together are "dumb" and only take in the info needed for that task at hand. This is obvious now, but not before. I was thinking classes where this mass file of functions to do specific jobs and called to just do that. They never even talked to each as well.

    The term "dumb" im saying doesnt know whats going on except for the info it gets passed. I use your example as reference whenever I start up a class now. I understanding the term "fleshing it out" now as well. Though I still dont grasp it 100 percent you had definetly shot me in the right direction and as with learning anything in life, it just takes time. (Besides Superman of course)

    Silly

  14. #14
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you examine the database classes in the Eclipse library you'll see that they work in this exact same way.

    I meant by passing the resultset to a seperate class.....
    There are many similarities to Eclipse, but there are just as many subtle differences. When it comes to database abstraction, the problem has been solved already. So the various implementations of the solution will naturally be quite similar. Differences are mostly due to personal preference.

  15. #15
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hehe been reading it over and over and over again. I think Im starting to understand. Originally when I was writing functions they where specific to the task and cluttered when they should of been broken out. I see the functions together are "dumb" and only take in the info needed for that task at hand. This is obvious now, but not before. I was thinking classes where this mass file of functions to do specific jobs and called to just do that. They never even talked to each as well.

    The term "dumb" im saying doesnt know whats going on except for the info it gets passed. I use your example as reference whenever I start up a class now. I understanding the term "fleshing it out" now as well. Though I still dont grasp it 100 percent you had definetly shot me in the right direction and as with learning anything in life, it just takes time. (Besides Superman of course)
    One of the most important aspects of OOP, is that the objects interact with each other. I personally found this to be the hardest aspect to understand. I know what you mean about "dumb" functions. Many of the classes I wrote when I was first learning this stuff (not that long ago) were basically just containers for a bunch of functions. I think the key is to think of objects as "things" that do useful stuff. They're a combination of data (properties) and behavior (methods). Look to examples in real life. In fact, that's the main point of OOP in the first place -- OOP objects are (generally) meant to be similar to real world objects. For example, a toaster. All you do is put bread in it and push the button down, then you get toast:

    PHP Code:
    $bread =& new BreadSlice();
    $toaster =& new Toaster();
    $toast =& $toaster->toast($bread); 
    That's it. As a user of a toaster you don't care about how it makes the toast. All you care about is that is when you put in bread, toast comes out a minute later. However, if you make toasters (i.e. you're the author of the Toaster class), then you do care about the toast() method and how it works -- but no one else should. Does that make any sense? I was hoping this would explain things, but I'm not sure if this is a good example, so I'll stop now.

    The point I'm trying (poorly) to make is that objects are much more than just a place to put functions. Think about the data (properties) that an object stores (e.g. a mysql connection resource). Then think about what actions (methods) the object should be able to perform with that data (e.g. opening and closing a connection).
    Last edited by codezilla; Feb 16, 2003 at 18:04.

  16. #16
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm currently working on tweaking the classes I presented to be wrappers for ABOdb (http://php.weblogs.com/ADODB), which supports LIMITs among other things -- I like ADOdb as a library, but I don't like everything about its interface.
    I agree with you. I checked out ADODB's manual today and although it's better than PEAR, it still seems bloated.

    It's not bloated in the functionality it offers, but the class interfaces are bloated. There are too many functions in one class, which IMO makes the library more difficult to understand for users than neccessary. Spreading the functionality over a couple of classes / interfaces would make the library more transparant.
    The term "dumb" im saying doesnt know whats going on except for the info it gets passed. I use your example as reference whenever I start up a class now. I understanding the term "fleshing it out" now as well. Though I still dont grasp it 100 percent you had definetly shot me in the right direction and as with learning anything in life, it just takes time. (Besides Superman of course)
    Like codezilla says, the true power of OOP lies in combining objects. But learning how to develop your classes in a way that makes reuse possible is difficult, and I think it takes a lot of studying and mostly experience.

    It's really too bad that tutorials on OOP in PHP fail to demonstrate how to combine objects. Most of the example classes I see in those tutorials are either utility classes or they operate in a very 'procedural' fashion.

  17. #17
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then you might want to take a look at the Eclipse API docs again, cause they work differently. Eclipse does not have something like a createStatement() method, all it offers is a query($sql) method for executing SQL. Iteration of a result set is decoupled from the result set itself, too.
    I didn't decouple iteration from the ResultSet on purpose. There was some talk about this kind of thing in the PEAR QuickForm thread (http://www.sitepointforums.com/showt...threadid=93442). I agree with what Selkirk was saying (or what I think he was saying) that there is no need to decouple iteration from a ResultSet. The reasoning (as I understand it) is that one of the points of an Iterator class is that you can have multiple Iterator instances iterating over a collection simultaneously. There's really no need for this with a ResultSet, within my experience. Maybe someone else needs this functionality, but I don't, so for me it's not worth including it.

    However, the main reason that my ResultSet class provides its own form of iteration is because that's how Java does it. The people who created Java are much smarter than me, so I try to emulate Java's behavior. I understand the argument to decouple the iteration -- in fact, I used to have them separated -- but I decided to merge them because it makes sense to me.

    Captain Proton, this relates somewhat to your "Layering software & control flow" thread so it will probably be of interest to you. In a layered system, the only time I iterate through a ResultSet is to populate some Model objects with data. To do this I only need to iterate over the ResultSet once, and NEVER need the ability to have muliple simultaneous iterators since only one Mapper/DAO is accessing the ResultSet and only for the purpose of instantiating Model objects. The Controller decides what Model objects it needs. It loads them by first instantiating the appropriate Mapper(s) and calling one of its find*() methods. The find*() methods access the appropriate Connection (actually my Mappers do this upon instantiation), execute the appropriate sql Statement, then iterate over the returned ResultSet and instantiates the appropriate Model object(s). That's it. No other objects have (or need) access to the ResultSet.

    The other issue is that an Iterator abstracts an iteration. This is useful if you want to substitute the type (class) of the object you're iterating over for a different type. But again, this isn't needed with a ResultSet. A Mapper will always interact with a Connection. And that Connection will always create a Statement that will be execute()'d and return a ResultSet (okay, INSERTs, UPDATEs, and DELETEs don't return ResultSets but that's beside the point). If you change the type of Connection (for example, from MySQL to Oracle) you're still going to end up with a ResultSet. If you change the datasource from a SQL database to something else like LDAP, or a flat File, you're not going to be working with a ResultSet, you'll be working with some other type of object. HOwever, SQL database are so ubitquitous that you can safely assume that you will always be using one for data persistance (at least for the foreseeable future) -- so you will never need to worry substituting a ResultSet for some other type of object. Therefore there's really no need to abstract its method of iteration.

    And, like I said before, Java does things this way and I trust that they thought things out far better that I can or will. WWGD? -- What Would Gosling Do?

  18. #18
    SitePoint Zealot Sork's Avatar
    Join Date
    Jul 2002
    Location
    Portugal
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Codezilla, great post!!

    But there's something that is 'confusing' me.
    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.
    My opinion is that the SqlStatement class shouldn't have nothing to do with the database connection.
    I would use it, for example, in a dao model...
    PHP Code:
    class LinkDao extends Dao
    {
      var 
    $mSqlStat;

      function 
    LinkDao
      
    {
        
    $this->mSqlStat =& new SqlStatement();
      }

      function 
    searchById($id)
      {
        
    //Now we build our sql
        //with SqlStatement
      
    }
    ...

    This way we separate this job only for SqlStatement, this is how i think it should be ...

    Please clear my mind ...

  19. #19
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sork, I think the confusion is because we're defining the Statement class a little differently. I think you're using your SqlStatement class to build a text SQL statement. If so, it probably has some methods like: addWhereClause(), addOrderByClause(), etc. I'm using my Statement class to store a (mostly) built, text SQL statement and allow it to be executed on a connection resource. If this is the source of the confusion, then you are correct that a class used to build a SQL statement should have nothing at all to do with a database connection. [Although, it may need to know the type of database you're connecting to so it can generate the proper vendor-specific SQL (e.g. for LIMIT clauses).]

    In the Connection class I posted there is a createStatement() method that accepts a single argument. The method expects the argument to be a string containing a text SQL statement. To combine your idea and mine, you could modify this method to accept an object (i.e. a SqlStatement object). Hopefully your SqlStatement class has a toString() method so, for instance, createStatement() could call it before instantiating the Statement object. Something like this:

    PHP Code:
    class MysqlConnection
    {

        
    // ...
        
        
    function createStatement(&$sqlStatement)
        {
            return new 
    MysqlStatement($sqlStatement->toString(), $this);
        }


    It may look like my Statement class is used to build a text SQL statement, but it's really only allowing you to pass it a pre-built, parameterized SQL statement. One could argue that your SqlStatement class (assuming I understand it correctly) should be responsible for this kind of thing. I didn't do things that way for the sake of simplicity (among other reasons).
    Last edited by codezilla; Feb 16, 2003 at 17:39.

  20. #20
    SitePoint Zealot Sork's Avatar
    Join Date
    Jul 2002
    Location
    Portugal
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks! that's where was my confusion!
    I didn't do things that way for the sake of simplicity (among other reasons).
    Can you comment your words, if there isn't any prob of course

  21. #21
    SitePoint Addict mr tinkles's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry to horn in, but:

    codezilla,

    seems we both know how to make toast and perhaps the relevance to OO...

    http://www.sitepointforums.com/showt...threadid=94571

    my example:

    I want to make toast???

    object bread = new object(wheat, NOCRUST);
    object toaster = new object(standard);

    toaster.toast(bread);
    bread.add(butter, cucumber, jelly);
    //user eat
    done;end;}...
    encapsulation, a foundation of OO.
    And you hit on a very big point of OO.

    "a big point of OO, is not the individual objects themselves, but their interactions with other objects"

    few people get that, and focus on individual objects...

  22. #22
    SitePoint Zealot codezilla's Avatar
    Join Date
    Nov 2002
    Location
    upstairs
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    seems we both know how to make toast and perhaps the relevance to OO...
    LOL. That's hilarious. I pulled that example out of my ***. What are the chances that two posts within 10 days of each other use a toaster example to explain OOP? Freaky.

  23. #23
    SitePoint Addict mr tinkles's Avatar
    Join Date
    Jan 2003
    Posts
    262
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not really, if you consider toaster/bread excellent example of OO. Who doesn't know toaster/bread and how they 'work'.

    Most people struggle with OO concepts, and I wonder why, OO is how we react with the physical world. Everyone knows OO, just can't seem to apply to programming...

    Maybe I'll switch to garage door opener example...

  24. #24
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think one of the big problems is that many peoples first encounter with programming concepts is in the form of the "flowchart", which is just a simple way of displaying procedural steps. It doesnt really lend itself to OO......


    Maybe UML should be taught in primary schools...
    THE INSTRUCTIONS BELOW ARE OLD AND MAY BE INACCURATE.
    THIS INSTALL METHOD IS NOT RECOMMENDED, IT MAY RUN
    OVER YOUR DOG. <-- MediaWiki installation guide

  25. #25
    SitePoint Wizard Sillysoft's Avatar
    Join Date
    May 2002
    Location
    United States :)
    Posts
    1,691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I guess I am learning more about OOP (In php at least). I have to admit when I first saw the post I understood it slightly but knew it was in the right direction for me. Now coming back to the example given to me by Codezilla it really makes sense now. I still have a ways to go but getting there. Codezilla the only question I had about the example is in the sql command you have ? in there and then passed on the variables seperately.
    PHP Code:
    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) . "'"

    So when you run execute you run getPrepareSql method and explode the sql statement passed using the ? as the seperator. Then in the code itself you passed the parameters to it after you start the method.

    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 


    Im assuming the variables passed to the method are perhaps from a form or some other input way? Why not have the sql statement itself have the variables in it?

    Did you just do that to prepare the variables being passed? I mean what if it was numeric? Would you need to do a is_numeric check first? Or am I way off base? Will re-read to make sure.

    Silly
    Last edited by Sillysoft; Apr 2, 2003 at 13:24.


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
  •