SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Design Pattern: Extending SQL statement

    Hi, would like to know what kind of design patterns is suitable for dynamic generate SQL? Like for example I have a class solely for one database table:

    Select * from user WHERE userid = "' . $userid . '"

    Then someday later another programmer decided to extend the feature and add another user_log table. He needs to add in criteria for the sql like so:

    Select * from user u LEFT JOIN user_log ul (ON ul.userid = u.userid)
    WHERE u.userid = "' . $userid . '"

    What kind of methods/patterns do you guys adopt?
    Thanks in advanced
    I Dunno LOL \(_o)/

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I guess this is taken from a User object and you can structure and name things and generally set up "scaffolding" which handles the 1:1 table transactions, that bit is quite easy - take a look at the ActiveRecord pattern

    The fly then lands in the ointment when you want to do joins - try this DataMapper discussion for a taster.

    Ostensibly you'll may find yourself heading in the direction of ORMs which handle and abstract away the joins - read this post and this one.

    There are many schools of thought, one is that if you don't envisage many joins in the future, then just live with it and tunnel some way through to permit direct access or injection of an sql string as an argument - not very pure in OOP terms - but will get the job done.

    I think a lot depends on whether you are in fact making yourself an "SQL Query Generator" which just joins up strings or by extending something like PDO and using its prepared statements.

    If you want to know more or read more real-life experiences of OOP/DB issues do an Advanced Search on just this forum for the terms:

    DataMapper
    ActiveRecord
    ORM

    for starters.

    Another thing to know. This kind of data persistence issue is generally handled by the layer termed the "Model" (the M in MVC), the model can be doing other things but it is generally where you keep the code that writes to the DB.

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2007
    Posts
    170
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for pointing out directions Cups! I'll read up on those!

    Appreciate!
    I Dunno LOL \(_o)/

  4. #4
    SitePoint Guru bronze trophy TomB's Avatar
    Join Date
    Oct 2005
    Location
    Milton Keynes, UK
    Posts
    996
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)
    In addition to the topics Cups posted, there was a good discussion here: http://www.sitepoint.com/forums/show...Mapper-Library about specific implementation considerations when trying to build a DataMapper


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
  •