SitePoint Sponsor

User Tag List

Page 1 of 4 1234 LastLast
Results 1 to 25 of 95

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Nov 2002
    Location
    here
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question MVC::Normalized_Database(?);

    First of all, I've searched through the existing posts looking for this a number of different ways and can't seem to find it. If you know where the answer already exists, please redirect! Thanks!

    Note: Much of this is directly related to the DAO

    I'm attempting to write my first app using the MVC design pattern, and am having difficulty modeling my data. In order to normalize my database, I've spread user data across the following tables:

    Code:
    mysql> TABLE users;
    +-------+-------+--------+--------+-----+----+----------+-------------+
    |user_id|auth_id|username|password|first|last|date_added|date_modified|
    +-------+-------+--------+--------+-----+----+----------+-------------+
    
    mysql> TABLE authorizations
    +-------+---------+
    |auth_id|auth_name|
    +-------+---------+
    
    mysql> TABLE ip_addresses
    +-----+----------+
    |ip_id|ip_address|
    +-----+----------+
    
    mysql> TABLE user_ips
    +-----+-------+-----+
    |ui_id|user_id|ip_id|
    +-----+-------+-----+
    
    mysql> TABLE groups
    +--------+----------+---------+
    |group_id|group_name|directory|
    +--------+----------+---------+
    
    mysql> TABLE user_groups
    +-----+-------+--------+
    |ug_id|user_id|group_id|
    +-----+-------+--------+
    Now, as I understand things, there should be a DAO for each table in the database, right? Wouldn't that be overkill? If so, and I use only a single "user_DAO", almost all of my stored queries are going to use long/complicated joins. However, if I do use a seperate DAO/model for every table, how would I set things up?

    PHP Code:
    /** Note:
     * The models are passed the database connection and instanciate their own appropriate DAO
     */
    class UserController extends Controller {
        
    // ??????????
        
    var $ip;
        var 
    $group;

        function 
    UserController(&$dbc) {
            
    $this->model =& new UserModel($dbc);
            
    $this->view =& new UserView();
            
    // ??????????
            
    $this->ip =& new ipController($dbc);
            
    $this->group =& new GroupController($dbc);
        }

        function 
    getUser($id$view='list');
            
    $user $this->model->getUser($id);
            
    // then what about ip and groups info?
            
    $ip $this->ip->getIPbyUser($user['id']);
            
    $group $this->group->getGroupByUser($user['id']);
            return 
    $this->view->getView($user$ip$group$view);
        }

        
    // etc...


    Also, how do I avoid this redundancy:

    PHP Code:
    // Controller
    function getUser($id) {
        
    $this->model->getUser($id);
        
    // ...

    // Model
    function getUser($id) {
        
    $this->dao->getUser($id);
        
    // ...

    // DAO
    function getUser($id);
        
    // ... 
    Thanks in advance for your help!

    Peace,
    texdc

  2. #2
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, I'd like to say that DAO's are not necessarily a part of MVC. My guess is that you picked up that idea because so many threads here about MVC also presented code with a DAO in it.

    You are right though, having a DAO for each table in your database is not necessary. Some tables are related to other tables, such as a user_ips table which is related to the users table. I am not exactly sure what the relation between those tables is, but I think that the UserDAO can be responsible for both of them. When there's a one-to-many relation between tables, the 'many' table can be part of the 'one' table DAO's responsibility.

    Martin Fowler's Patterns of Enterprise Application Architecture book explains it better. Take a look at the Object-Relational Structural Patterns here, especially the Dependent Mapping, which I think applies to your situation.

    From looking at your code, I think you understand the MVC pattern (if there is such a thing as 'the' MVC pattern, but that's another story..) a bit wrong. The fact that you're initiating a ipController and a groupController inside the UserController does not make any sense to me. A Controller's job should be to access and manipulate model data and pass it on to View objects for one specific action / task of an application. In your code this 'task' would be to view information about a User.

    Here's some example code how I would structure the classes:

    PHP Code:

    class UserController
    {
        var 
    $user;
        
        function 
    UserController(&$dbc)
        {
            
    // Initiate the DAO
            
    $userDAO = new UserDAO($dbc);
            
            
    // Get an array of User objects
            
    $users $userDAO->getUsers();
            
            
    // Pass them on to the View
            
    $this->view = new UserView($users);
        }
    }

    class 
    UserDAO
    {
        ...
        
        function 
    getUsers()
        {
            
    // This is pseudo-code of course, but I'm sure you understand the meaning
            
    $userRows 'SELECT * FROM users';
            foreach (
    $userRows as $userRow)
            {
                
    $userObject = new User($userRow);
                
                
    // Foreach User object execute a query to get the IP addresses
                
    $userIPs 'SELECT ip_id FROM user_ips WHERE user_id = ' $userObject->getID();
                
    $userObject->setIPs($userIPs);
                
                
    // Do the same for the groups and authorizations
                
    ...
            }
            
            return 
    $userObjects;
        }
    }

    class 
    User
    {
        var 
    $ips;
        
        ...
        
        function 
    setIPs($ipArray)
        {
            
    $this->ips $ipArray;
        }
        
        function 
    getIPs()
        {
            return 
    $this->ips;
        }
    }

    class 
    UserView
    {
        function 
    UserView(&$userObjects)
        {
            foreach (
    $userObjects as $user)
            {
                echo 
    $user->getName() . '<br>';
                
                foreach (
    $user->getIPS() as $ip)
                {
                    echo 
    "IP: $ip<br>";
                }
            }
        }

    You can avoid the redundancy you mention by following this pattern:

    - Use a DAO to load the Model objects
    - Pass the Model objects directly to a View object

    i.e.:
    PHP Code:
    $dao = new UserDAO($databaseConnection);
    $userObject $dao->findByID($modelObjectID);
    $view = new UserView($userObject); 

  3. #3
    SitePoint Member
    Join Date
    Nov 2002
    Location
    here
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Captain Proton,

    Thanks very much for your response. I believe you have pointed me in the right direction to get me past this "road block" I've been fighting. I'll look your code over in more depth later, and perhaps rework it to make sure I understand, especially that last little bit about redundancy.

    I'll check out the Fowler book as well, asap.

    Thanks again!
    -texdc

  4. #4
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the roles (grouped) based authentications I have a Dao for Users, Roles And Privleleges; I have another one for verifying a Log In etc etc...

    In most of the Daos there is about 8 queries each so I wouldn't worry too much about the actual Dao it's self, but more in the way you use them.



    --EDIT--

    Follow this link and you'll see how I work things out...

    http://www.sitepointforums.com/showt...hreadid=116318

    Thanks due to MKRZ for helping out with some scripting

  5. #5
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    EWW! Are you really advocating using PHP to perform the JOIN instead of the DBMS?

    This is why I hate MVCs in general, and especially the pervasive 'OO is the OneTruePath' ideology amongst practitioners.

  6. #6
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'll say it again, DAO (which is a form of object-relational mapping) has *nothing* to do with MVC, which is about separating the concerns of application data, presentation and interface control.

    You're right though, you should leave the JOINing of tables to the database system, don't do it in PHP. That may be the easiest way to implement but it is certainly not the most efficient way. There are better ways to map objects to database tables, like the ones described in Martin Fowler's PoEAA.

  7. #7
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Joins definitly belong on the DB side of the fence. This may be a problem with the "scope" of your model class. In general, you should be modeling somthing from the real world, like a customer or an order, not a database table. The model should be smart enough to run efficient queries within the domain of that model.

    I use Oracle as a database at work, and many of my models simply end up as a mapping of database package functions to class methods, with some possible parameter binding. This keeps the bulk of the "business logic" in the database where it belongs. (I guess you could think of all of my PHP code as a giant "View" with the "Model" being the database )
    Jason Sweat ZCE - jsweat_php@yahoo.com
    Book: PHP Patterns
    Good Stuff: SimpleTest PHPUnit FireFox ADOdb YUI
    Detestable (adjective): software that isn't testable.

  8. #8
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by sweatje
    I use Oracle as a database at work, and many of my models simply end up as a mapping of database package functions to class methods, with some possible parameter binding. This keeps the bulk of the "business logic" in the database where it belongs. (I guess you could think of all of my PHP code as a giant "View" with the "Model" being the database )
    Yay! Someone knows what they are doing! That's exactly how it should work.

  9. #9
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    Yay! Someone knows what they are doing! [img]images/smilies/smile.gif[/img] That's exactly how it should work.
    I too use Oracle at work and also end up doing a lot of mapping like Jason indicates in his message further up this thread.

    However, I would like to add that putting the business logic in the DB is not "exactly how it should work". It is simply one way of approaching a solution. And indeed, I think some people (many?) believe the business logic needs to be moved out of databases and into another tier - there are whole companies founded and doing strong business around the concept of "business rules" or "business intelligence" being its own component in an overall architecture....

    Just my soap-boxy (mmmm...new word) $0.02 on the subject....

    Cheers,
    Keith.

  10. #10
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Taoism
    However, I would like to add that putting the business logic in the DB is not "exactly how it should work". It is simply one way of approaching a solution.
    But it's the right one.

    Quote Originally Posted by Taoism
    And indeed, I think some people (many?) believe the business logic needs to be moved out of databases and into another tier - there are whole companies founded and doing strong business around the concept of "business rules" or "business intelligence" being its own component in an overall architecture....
    It depends, really, on your definition of 'business rules', but these people are wrong.

    As the old saying goes -- what once was old is now new again. Back-in-the-day people had to write their own data storage mechanisms. Lots of individual libraries which were buggy, hard to maintain, etc. Business rules were handled in the application (also buggy) and so data was, unfortunately, more often invalid than it should be.

    Then, the world saw the light and Codd came up with the relational database management system. The whole (well, one of the main ones, I guess) concept of the RDBMS is, in laymans terms, that you encapsulate your business rules, in the form of constraints, into the management system so that you guarantee that the data in your database is logically sound.

    In essence, the RDBMS is the intelligence behind business.

    The best part is that you, typically, don’t have to code how to implement the business rules – you tell the RDBMS what to enforce and it does the rest. This is cross-platform, easy to read and understand, and virtually bug-free (provided you know what the business rules are ).

    If you’re interested in more, check out “What Not How: The Business Rules Approach to Application Development”.

  11. #11
    SitePoint Addict
    Join Date
    Aug 2002
    Location
    Ottawa, Ontario, Canada
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by MattR
    But it's the right one.
    It depends, really, on your definition of 'business rules', but these people are wrong.
    Once again, they aren't wrong. They merely have a different perspective than you do. Applications come in many forms and to make a blanket statement that the insertion of business logic in the database is the only "right" way is not productive.

    Here is a quote from IBM:
    More background about business rules

    A major trend happening in object-oriented application development is the separation of business logic from data access and application logic: the same business logic may be used in multiple applications, and should be changeable rather than being buried and intertwined with data and application specific functions.

    Rules as a way to specify business logic has the advantage of combining automatic executability with a relatively high level of human understandability, i.e., a high conceptual level and a "declarative" (rather than only procedural) semantics. The latter enables non-programmers, especially business-domain experts such as marketing managers, to specify business rules, and to modify them relatively easily and often.
    The quote is part of an overview of their CommonRules Java library.

    Don't get me wrong. I appreciate your perspective on moving the logic/rules into the database, and indeed I do this almost all the time myself. I just don't like the idea of dismissing something like business rules being external to the database as "wrong".

    Cheers,
    Keith.

  12. #12
    ********* Victim lastcraft's Avatar
    Join Date
    Apr 2003
    Location
    London
    Posts
    2,423
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Hi.

    Quote Originally Posted by MattR
    But it's the right one.
    I assume this is a troll...

    This is so old it's becoming boring. When the competition was from Cobol and C, relational databases were an easy choice. Nowadays we write applications an order of magnitude faster.

    The problem with SQL as it currently stands (until they add CREATE TYPE...) is poor encapsulation. In short schema changes cause near panic. You can mitigate this with views and triggers while the apps change, but pretty soon you give in and start building stored procedures and have the apps. talk to them. What you then effectively have is a three tier app, except that the middle tier, rather than being domain objects in a modern OO language, is written in 1970s SQL stored procedures . The procedures themselves are still highly sensitive to the schema. A bad combination for frequent changes to the business rules, disaster if those business rules get tangled.

    Another problem is that your data does not always come from relational sources. We have data from full-text engines, DBM file caches and real time search engine data. How do your stored procs. deal with that?

    If the business rules change little, the bulk of the data is relational and the datamodelling skills are available then RDBMSs are the way to go every time. You will win on performance too. If the rules change frequently (the norm in my game), you get data from different places and you have OO expertise then skip the stored proc. black hole and jump straight to the OO domain. You will in the end anyway.

    You are inadvertently demonstrating a current sickness in the industry, which is lack of communications between the DBAs and OO developers. I don't include you in this of course (you are making positive efforts to go in both directions), but blind parroting of the Pascal and Date bibles is missing an opportunity. At least Scott Ambler and the ThoughtWorks people are making an effort to cross this bridge.

    yours, Marcus.
    Marcus Baker
    Testing: SimpleTest, Cgreen, Fakemail
    Other: Phemto dependency injector
    Books: PHP in Action, 97 things

  13. #13
    SitePoint Member
    Join Date
    Nov 2002
    Location
    here
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Interesting posts.

    My host only offers MySQL, and that without BDB or InnoDB type tables. So, I'm stuck doing my joins in PHP. It may not be the most efficient way, but that's the extent of my capabilities and knowlege. Thanks, though.

    I understand that a DAO is not part of the MVC. I'm simply building off Harry's tutorials at phppatterns.com. Please keep in mind that I'm still trying to get my head around the MVC concept, and the part I'm having the most difficulty with is the model. After that, where to put specific methods, the controller, or the model. So, please bear with me.

    Thanks again!
    -texdc

  14. #14
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by texdc
    My host only offers MySQL, and that without BDB or InnoDB type tables. So, I'm stuck doing my joins in PHP. It may not be the most efficient way, but that's the extent of my capabilities and knowlege. Thanks, though.
    -texdc
    This is a problem that many people have, so it frustrates me when I see some the devs say use Postgres or some other dbtype that natively handles foreign key mapping within the db schema. Lots of us dont have this option

    But, although you cannot explicitly define the table relationships on MYISAM tables, You CAN still do JOINS on the using a join clauses in your select queries. But I guess you know this, since this is exactly the "problem" you are trying to solve. I fear that using PHP to do your "joins" is going to create a large performance hit on your application because you will be running multiple queries to get the related data from different tables, and even if you don't see a huge performance hit now, you will see later on down the road, after your application has grown, and you are still doing things this way.

    I would try and avoid this if I were you. Take a look at the Class Table Inheritance Pattern. This looks like the approach you are taking now.

    On that note, you may see that a class for each table in your database is not really necessary.

    In your situation you would simply use a SQL statement with a join clause in your User table and be done.

    PHP Code:
    class User
    {
     var 
    $database;
     function &
    User($database)
     {
      
    $this->database $database;
     }
     function &
    getUsers()
     {
      
    $sql "insert massiv join string here";
      return 
    $this->database->query($sql);
     }

    If you plan on the other tables having a relationship with something besides users you can simply incorporate another join there as well.

    And if you don't like writing SQL for each class I would look into creating a class that generates the SQL for you (Query Object Pattern on MF's site).

    HTH

  15. #15
    SitePoint Member
    Join Date
    Nov 2002
    Location
    here
    Posts
    16
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    shoebox: you're right on target on where I'm at, and where I'm going with this.

    Here is what I've cooked up so far. I want to avoid passing the model itself to the view completely to keep them ignorant and separate. Currently, this solution does not allow that approach, but it's helping me understand the whole pattern better.

    Note: The model will always instanciate the DAO and the DAO queries will always return a 'result' object.

    PHP Code:
    class UserModel extends Model {
        var    
    $ID;
        var    
    $images;
        var    
    $authoriztion;
        var    
    $groups;
        var    
    $ips;
        
        function 
    UserModel(&$dbc) {
            
    $this->dao =& new UserDao($dbc);
        }
        
        
    // there are a lot of queries here, rather than one huge JOIN
        // good or bad idea?
        // could also be split into specific functions
        
    function _setup($id) {
            
    $this->ID $id;
            
            
    $athr =& $this->dao->getAuthorization($this->ID);
            
    $this->authorization $athr->getRow();
            
            
    $grpr =& $this->dao->getGroups($this->ID);
            while (
    $group $grpr->getRow()) {
                
    $this->groups[] = $group;
            }
            
            
    $ipsr =& $this->dao->getIPs($this->ID);
            while (
    $ip $ipsr->getRow()) {
                
    $this->ips[] = $ip;
            }
            
            
    // alternate way of handling an array?
            
    $imgr =& $this->dao->getImages($this->ID);
            while (
    $image $imgr->getRow()) {
                
    $i_id array_shift($image);
                
    $this->images[$i_id] = $image;
            }
        }
        
        
    // not sure if this is the best way to handle this...
        
    function &get($ref) {
            switch (
    gettype($ref)) {
                case 
    'integer':
                    
    $u =& $this->dao->getByID($ref);
                    
    $this->_setup($ref);
                    break;
                case 
    'string':
                    
    $u =& $this->dao->getByName($ref);
                    
    $this->_setup($u->getRow()['id']);
                    break;
            }
            return 
    $u;
        }

    I started down this path because I was looking at the Composite pattern in the GoF book. It sounded like it would work, but in putting it together, it may not be the best choice for a Model pattern, but I may be wrong. (I know I'm not implementing it in the code above; it was simply inspiration.) What about the Decorator pattern? Any comments or ideas?

    Thanks again!
    -texdc

  16. #16
    SitePoint Zealot
    Join Date
    Oct 2002
    Posts
    158
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by texdc
    I want to avoid passing the model itself to the view completely to keep them ignorant and separate.
    Why do you want to do this? Is it a design requirement? The reason I ask is because it seems to make things much easier when you pass a model to view, or view to model.
    // there are a lot of queries here, rather than one huge JOIN
    // good or bad idea?

    Yes I think one large join would be better. Can someone back me up on this?

    If you keep doing things the way you mentioned you it seems you will end up writing a custom method for each "getBy()" you want to do.
    Perhaps this could be a generic "getById()" method in your parent Model class.

    I'm not familiar with Composite.

    what about something like this instead??
    I'm not familiar with Composite.
    PHP Code:
    <?
     
    class UserModel extends Model
     
    {
      function 
    UserModel(&$dbc)
      {
       
    parent::model($dbc'users''id');
      }
     }
     class 
    Model 
     
    {
      var 
    $dbc;
      var 
    $table;
      var 
    $idField;
      
      function 
    Model(&$dbc$table$idField)
      {
       
    $this->dbc $dbc;
       
    $this->table $table;
       
    $this->idField $idField;
      }
      function 
    getById($id)
      {
       
    $sql "SELECT * FROM $this->table WHERE $this->primaryKey = $id";
       return 
    $this->dbc->query($sql);
      }
      function 
    getList()
      {
       
    $sql "SELECT * FROM $this->table";
       return 
    $this->dbc->query($sql);
      }
      function 
    doInsertById($SqlFields$SqlValues)
      {
       
    $sql "INSERT INTO  $this->table FIELDS($SqlFields->getCommaSeparated()) VALUES($SqlValues->getCommaSeparated())";
       
    $this->dbc->query($sql);
       return 
    $this->dbc->isSuccess();
      }
      function 
    doUpdateById($id$SqlFields$SqlValues)
      {
       
    $sql "UPDATE $this->table SET($SqlFields->getSetClause($SqlValues) WHERE $this->idField = $id";
       
    $this->dbc->query($sql);
       return 
    $this->dbc->isSuccess();
      }
      function 
    doDeleteById($ids)
      {
       if (
    is_array($ids)) {
        
    // handle mutliple deletes
       
    } else {
        
    $sql "DELETE FROM $this->table WHERE $this->idField = $ids";
        
    $this->dbc->query($sql);
        return 
    $this->dbc->isSuccess();
       }
      }
     }
     

    <?
    Last edited by shoebox; Jul 16, 2003 at 11:28.

  17. #17
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by shoebox
    I'm not familiar with Composite.
    But you just described it in action in your prior statement Composite is aggregating objects, so if you pass a model to a view and use them together, you are using a form of the Composite design pattern

  18. #18
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Umm... Using PHP for JOINs ? Never used them myself personally, though for the general interest since the matter has now been brought up, here is the database SQL I use... You should get the general idea of the fields so I won't post the DB schema

    PHP Code:
    function searchAllPriveleges__ByRoleId($id 0) { # privelege id 'null' by default
                
    $sql "SELECT 
                    rolepriveleges.roleid, 
                    rolepriveleges.privid, 
                    priveleges.privid             AS ID, 
                    priveleges.privdescrip        AS DESCRIP 
                    FROM 
                    rolepriveleges, priveleges 
                    WHERE 
                    rolepriveleges.roleid = '"
    $id ."' 
                    AND 
                    rolepriveleges.privid = priveleges.privid ORDER BY priveleges.privid"
    ;
                    
                return 
    $this -> retrieve($sql);
            }

    function & 
    totalRows() {
                
    $sql "SELECT COUNT(*) FROM roles";
                return 
    $this -> retrieve($sql);
            }
            
            function 
    searchAll() {
                
    $sql "SELECT * FROM roles ORDER BY roleid";
                return 
    $this -> retrieve($sql);
            }
            
            function 
    searchAllRoles__ByAuthorId($id 0) { # role id 'null' by default
                
    $sql "SELECT 
                    authorroles.authid, 
                    authorroles.roleid, 
                    roles.roleid             AS ID, 
                    roles.rolelimit         AS CAP, 
                    roles.roledescrip         AS DESCRIP 
                    FROM 
                    authorroles, roles 
                    WHERE 
                    authorroles.authid = '"
    $id ."' 
                    AND 
                    authorroles.roleid = roles.roleid ORDER BY roles.roleid"
    ;
                    
                return 
    $this -> retrieve($sql);
            }


    function 
    searchAll__LogsByCommentId($id) { #
                
    $sql "SELECT 
                    logs.logid            AS LOGID, 
                    logs.logdate        AS LOGDATE, 
                    logs.logtitle        AS LOGTITLE, 
                    
                    authors.authid        AS AUTHID, 
                    authors.authfname    AS AUTHFNAME, 
                    authors.authlname    AS AUTHLNAME, 
                    
                    authorlogs.logid, authorlogs.authid, 
                    usercomments.commentid, usercomments.logid  
                    FROM 
                    logs, authors, authorlogs, usercomments  
                    WHERE 
                    usercomments.commentid = '"
    $id ."' 
                    AND 
                    usercomments.logid = logs.logid 
                    AND 
                    authorlogs.logid = logs.logid 
                    AND 
                    authorlogs.authid = authors.authid"
    ;
                
                return 
    $this -> retrieve($sql);
            }
            
            function 
    searchAll__ByLogDate($param '') { 
                
    $sql "SELECT 
                    logs.logid                     AS LOGID,
                    logs.logdate                AS LOGDATE,
                    logs.logtitle                AS LOGTITLE,
                    logs.logmessage                AS LOGMESSAGE,
                    logs.logstatus                AS LOGSTATUS,
                    logs.logacceptance            AS LOGACCEPT,
                    
                    authors.authid                AS AUTHID,
                    authors.authfname            AS AUTHFNAME,
                    authors.authlname            AS AUTHLNAME,
                    
                    authorlogs.logid, authorlogs.authid 
                    FROM
                    logs, authors, authorlogs 
                    WHERE 
                    authorlogs.authid = authors.authid 
                    AND 
                    authorlogs.logid = logs.logid 
                    ORDER BY logs.logdate DESC" 
    $param;
                    
                return 
    $this -> retrieve($sql);
            }
            
            function 
    searchAll__ByLogId($id 0) { # get all log and author by logid
                
    $sql "SELECT 
                    logs.logid                     AS LOGID,
                    logs.logdate                AS LOGDATE,
                    logs.logtitle                AS LOGTITLE,
                    logs.logmessage                AS LOGMESSAGE,
                    logs.logstatus                AS LOGSTATUS,
                    logs.logacceptance            AS LOGACCEPT,
                    
                    authors.authid                AS AUTHID,
                    authors.authfname            AS AUTHFNAME,
                    authors.authlname            AS AUTHLNAME,
                    
                    authorlogs.logid, authorlogs.authid 
                    FROM
                    logs, authors, authorlogs 
                    WHERE 
                    authorlogs.logid = '"
    $id ."' 
                    AND 
                    authorlogs.authid = authors.authid  
                    AND 
                    authorlogs.logid = logs.logid 
                    ORDER BY logs.logdate DESC"
    ;
                    
                return 
    $this -> retrieve($sql);
            }
            
            function 
    searchAll__ByAllResults($id 0$param '') { # $id (ie all comments based on one logid - not required here)
                
    $sql "SELECT 
                    logs.logid                     AS LOGID,
                    logs.logdate                AS LOGDATE,
                    logs.logtitle                AS LOGTITLE,
                    logs.logmessage                AS LOGMESSAGE,
                    logs.logstatus                AS LOGSTATUS,
                    logs.logacceptance            AS LOGACCEPT,
                    
                    authors.authid                AS AUTHID,
                    authors.authfname            AS AUTHFNAME,
                    authors.authlname            AS AUTHLNAME,
                    
                    authorlogs.logid, authorlogs.authid 
                    FROM
                    logs, authors, authorlogs 
                    WHERE 
                    authorlogs.authid = authors.authid 
                    AND 
                    authorlogs.logid = logs.logid 
                    ORDER BY logs.logdate DESC"
    $param;
                    
                return 
    $this -> retrieve($sql);
            }
            
            function 
    insert__AddNewLog($title$message$notes$status$comments$authid) { #
                
    $sql "INSERT INTO logs SET 
                    logdate = now(), 
                    logtitle = '"
    $title ."', 
                    logmessage = '"
    $message ."', 
                    logstatus = '"
    $status ."', 
                    logacceptance = '"
    $comments ."'";
                
    $this -> update($sql);
                
    $insert = (int) mysql_insert_id(); // logid
                
                
    $sql "INSERT INTO authorlogs SET 
                    logid = '"
    $insert ."',
                    authid = '"
    $authid ."'";
                
    $this -> update($sql);
                
                
    $sql "INSERT INTO authornotes SET 
                    logid = '"
    $insert ."',
                    authid = '"
    $authid ."',
                    authnotes = '"
    $notes ."'";
                
    $this -> update($sql);
            }
        } 
    ...

    Anyway, does anyone know of a decent article on JOINs if the above isn't the best path to walk down... ?

    Thanks

  19. #19
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    At some point think I need to write version 2 of the articles on phppatterns (perhaps when PHP 5 hits the streets).

    Where DAOs are concerned, think I need to emphasise the point that they're prime purpose is to limit the number of times you need to repeat the same SQL statement to as few as possible as well as "centralizing" queries in an easy to find / maintain layer.

    Think it's not worth trying to use them too hard as a mechanism to be database independent (although it helps not to have queries repeated). Sun promote this as a reason for DAO's here but in practice, most databases have very varying SQL support and often the "temptation" to use the non standard stuff is overwhelming. Think that's really a Sun vs. Oracle issue.

    The other thing about DAOs is, implemented as I suggested, they won't entirely limit the impact of, say, a change in a column name to the DAO classes. In my example I had column names turning up in the "view". Another layer is needed to represent a single row from a query which provides accessor methods which remain the same while the underlying fields they fetch may vary. The example of Articles -> Article in the Factory method might help you there. Articles could be a DAO itself or perhaps a class which uses a DAO.

    Also it's worth looking at what "persistence layers" like DB_DataObject are doing.

  20. #20
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Harry - Looking at...

    http://www.phppatterns.com/index.php...leview/49/1/1/

    I seem to understand this fully although a question on UML and the IMG you have on the page;

    Does the - mean a property going to the class and + mean a property going from the class so to speak ?

    Not to sure on UML you understand... Or maybe you can better explain this... Proberly

    Thanks.

  21. #21
    SitePoint Guru
    Join Date
    Oct 2001
    Posts
    656
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not Harry, but..
    - = private, + = public

  22. #22
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Captain Proton is probably correct, my inital impression was -=attribute +=method, but those are already indicated by the line and () for methods

  23. #23
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks folks; Always wondered that is all... Why a box was split into 2 parts... Now I know and it helps...

  24. #24
    eschew sesquipedalians silver trophy sweatje's Avatar
    Join Date
    Jun 2003
    Location
    Iowa, USA
    Posts
    3,749
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This figure from my article Industrial Strength MVC shows one way to architect a MVC application, including some of the recent discussion on this thread regarding moving business logic down to the database layer. I think picturing this "application stack" can help formalize some of the ideas of how to implement "Model-View-Controller" style frameworks in PHP. The yellow bars show the particular libraries I used in the article, but these could easily be changed out for your favorite RDBMS (except for MySQL), db abstraction layer, MVC controller framework and templating engine.

    HTH


  25. #25
    SitePoint Guru
    Join Date
    Nov 2002
    Posts
    841
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I want to avoid passing the model itself to the view completely to keep them ignorant and separate.
    Um, Here is a diagram of MVC dependencies:
    Code:
                     +--+
                     +--+---------+
                     | Controller |
                     +------------+
                     /            \
                    /              \
                   /                \
                  /                  \
          +--+   \/             +--+ \/
          +--+---------+        +--+---------+
          |    Model   | <----- |   View     | 
          +------------+        +------------+
    Note that these are packages (groups of classes), not classes.

    MVC applies the Dependency Inversion Principle. The controller is a high level package and thus may freely call the other two packages. The controller is dependent on the other two classes. The Model is a low level package and thus may not have dependencies on the other two packages. The View is dependent upon the model, but not the controller. (See Principles of Object Oriented Design for a list of types of dependencies.

    If you need to communicate "against the grain" then you would use the Hollywood Principle to allow communications without creating dependencies.

    For example, the model could communicate to the view that data has changed and that the view needs to be refereshed. To do this, it would issue a data changed event which the view would listen to. Since the model has no idea what kinds of objects are listening to its events, the pattern of dependencies is preserved, even though there is communication to the view originating in the model. It might be the controllers responsibility (being at the highest level) to pick the correct view to register to listen to the model.

    Traditionally, the controller is responsible for managing input. (In a PHP web app, this means that the controller classes would be the only ones with access to the $_GET, $_POST, etc arrays.

    So, input goes in the controller package, output goes in the view package, and business logic goes in the model package.

    The controller also acts as a mediator between the other two classes. Sometimes, you might want to split the controller up. You might also want to split up the model to encapsulate database access into its own package.

    Code:
                     +--+
                     +--+---------+
                     | Controller |
                     | (input)    |
                     +------------+
                            /\
                             |
                     +--+    |
                     +--+---------+
                     | Controller |
                     | (mediator) |
                     +------------+
                     /            \
                    /              \
                   /                \
                  /                  \
          +--+   \/             +--+ \/
          +--+---------+        +--+---------+
          |    Model   | <----- |   View     | 
          +------------+        +------------+
                 |
                 |
         +--+   \/
         +--+---------+
         | Database   |
         +------------+
    The advantage is flexibility: if you need to output in a different format (say XML instead of HTML), swap in a different view. If you need to support application scripting, swap in a different input controller. If you need to support a different database, swap in a different database package.

    The model, as the holder of the application (business) logic cannot be swapped out. You cannot swap out a Web Store model for a Web Log model in an application. You could swap out a MySQL implementation of a Web Store model for an Oracle implementation of a Web Store model. People develop persistence layers to avoid having to make any model swap at all when they want to make this kind of implementation change.

    Note that being at highest level, the mediating controller will probably have to change no matter what types of changes you make to your application.


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
  •