SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Creating table structure independed site

    Hello

    I am wondering how I could make a site so that I am not depended on some specific table names/structure, that is that the columns don't neccisarily have to be called something specific and I can easily adjust my site to differant table/column names.

    I do run all querys throught an object so I am not depended on some specific database and I wanted to take it a step further.
    I was just wondering what is the best way to do this?
    I had thought of doing querys like this:
    PHP Code:
    $q =& $db->query('SELECT {table1.col1} as name1, {table2.col2} as name2 FROM {table1}, {table2}'); 
    replacing table1/col1 etc with some convinient names that are saved in my system, then I store what the actual field name is and do preg_replace through all my querys to place the appropriate names, still, this method must be a little bit slow with all those preg_replace calls, or what ?

    If anyone is doing/knows how to something like this please share it, I would appreciate it very much!

    Thanks in advance!

    edit: isn't it independed ? I don't know what is wrong with me today ; )
    Last edited by website; May 21, 2003 at 12:55.
    - website

  2. #2
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Never seen this done before....

    SQL Templates anyone ?

    The only thing that I can think of at the moment as to why you'd want to do this is to share table resources (ie data) between different tables/databases that all (must) have the same design and relationships...

    There is bound to be another way of doing this w/out using the idea you've just described I'd think ?

    But at the moment I can't think of method of templating SQL queries....

    Sorry

  3. #3
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I know, this isn't so good solution but this must exist, I mean, this is so useful!

    I plan to place my web'system' on two websites that already exist and already have differant table structure. This would be the key, I just don't know of a good way to do this...
    - website

  4. #4
    No. Phil.Roberts's Avatar
    Join Date
    May 2001
    Location
    Nottingham, UK
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    define('NEWS_TABLE''news');

    $q =& $db->query('SELECT title, article, date FROM ' NEWS_TABLE ' ORDER BY date'); 
    Perhaps you could also maybe work some kind of tag parsing mechanism into your query() method? Then you could implement an SQL templates system if you wanted.

  5. #5
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    my scripts would drown in constants

    No I would like to have the parsing logic, as you said, in the query method (or before I execute the query which is not done there). Any idea how to do so other then the way I pointed out in the first post ?
    - website

  6. #6
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quick link - have a read up on DB_DataObject (part of PEAR): http://devel.akbkhome.com/peardoc2/p...ataobject.html

  7. #7
    SitePoint Addict been's Avatar
    Join Date
    May 2002
    Location
    Gent, Belgium
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use the SQL 'AS' keyword to abstract from db column names.
    as for
    SQL Templates anyone ?
    This
    PHP Code:
    $sqlTemplate 'SELECT %s FROM %s WHERE %s';
    $sql sprintf$sqlTemplate$sqlFrom$dbTable$sqlWhere); 
    has been working fine for me, but maybe there are problems with it I haven't thought of ;-)
    Per
    Everything
    works on a PowerPoint slide

  8. #8
    Sidewalking anode's Avatar
    Join Date
    Mar 2001
    Location
    Philadelphia, US
    Posts
    2,205
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by HarryF
    Quick link - have a read up on DB_DataObject (part of PEAR): http://devel.akbkhome.com/peardoc2/p...ataobject.html
    Along similar lines, the phrasebook pattern.
    TuitionFree a free library for the self-taught
    Anode Says... Blogging For Your Pleasure

  9. #9
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well been, I would rather not need to include more variables all over my classes, rather do it like anode and HarryF are suggesting...

    About that method, I don't fully understand, I read the phrasebook and some pages of your link Harry but could you guys help me with it. I thought that was bit limiting as it is so hard to make complicated querys with joins etc, could you give me example of classes and usage when doing the following:
    PHP Code:
    $db->query('SELECT User.id, User.name, Post.title, Post.content FROM User, Post WHERE User.id = Post.User_id AND Post.id = 1'); 
    on the following tables
    Code:
    User:
    id
    name
    
    Post:
    id
    title
    content
    User_id (references User.id)
    Could you show me how to do this query with your methods ?

    I would appreciate it extreamly much !
    - website

  10. #10
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anode - have you looked at this article ?http://www.evolt.org/article/Effecti...149/index.htmlYet to look at it myself though....

  11. #11
    SitePoint Addict
    Join Date
    Mar 2003
    Location
    Germany
    Posts
    216
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh dear. Just had a look at the article Dr Livingston mentioned and I completely agree with tapir's 2nd comment. This approach isn't very adviseable. I think a DAO solves this problem in a much more flexible and scaleable way.
    website, you might want to check out the DAO pattern. It might offer what you are looking for: a nice way to abstract away the database implementation details. I cannot praise the advantages of this pattern enough.

  12. #12
    SitePoint Addict been's Avatar
    Join Date
    May 2002
    Location
    Gent, Belgium
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well been, I would rather not need to include more variables all over my classes, rather do it like anode and HarryF are suggesting...
    I was not suggesting you should include your variables all over the place, I was just showing a different kind of 'templating'...
    In the link HarryF posted I saw a line re-appearing all over the place: 'global $db'
    With the risk of being called a total ripoff-guy: 'GLOBALS ARE EVIL' (btw, HarryF, didn't you write an article about that? ;-) )

    No matter how you look at it, you're still going to have to store the table name, field names etc.. somewhere before you can use them in your apps.
    Now, I don't know, but it's my opinion these storage containers are going to be variables ;-)

    Of course, I wouldn't include these variables all over my classes!
    I'd use a class to fetch the queries (which are stored in sprintf()-compatible 'template' format (for example in a text file)) and pass them to the query() method of my db-connection-object.
    I'd simply use an identifier for the queries.
    I guess you could call it a Dao...

    All queries would be stored in 1 section of a configuration file, if queries change, I have to modify 1 specific section of the configuration file, if the table names or field names change, I change them in the same configuration file (could be another one btw ;-) ) I don't have to change anything in the rest of my app code....

    I've come to learn (and am convinced) that 'template view' not the way to go for application layout, why should it be any different for queries ?
    Per
    Everything
    works on a PowerPoint slide

  13. #13
    ********* Member website's Avatar
    Join Date
    Oct 2002
    Location
    Iceland
    Posts
    1,238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, yes, I have it similar but I use no globals, as I do it now, I pass an object call $db into the classes that need it.
    But if you store all querys in one config file, do you just call it like $db->execQuery(1) ?
    1 would be the number of the query...
    Well, that obviously places everything regarding the sql in one place but is that really the best way ?

    Anyway, I've got to check out sprintf() function and these DAO (some links maybe)
    - website

  14. #14
    SitePoint Addict been's Avatar
    Join Date
    May 2002
    Location
    Gent, Belgium
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, if it's the best way, I really don't know.
    But, considering a website:

    It has different pages, some of them are maybe static, some of them are dynamic and use a db.
    The pages that use a db have certain amount of queries that are executed, for example:
    Code:
    SELECT title, posts FROM forum WHERE date > 20030506;
    SELECT title, content, author FROM forum_posts WHERE post_id = 25;
    etc...

    What I'm getting at: each page has a limited set of queries to perform (depending on the action requested by the user). A system may change, queries may change, but all in all, it will be limited So I would say, yes store them in 1 place, let 1 layer of your app access this queries, perform them and return the results...

    If you look at the Dao pattern from Sun blueprint catalog they use pretty much the same system...
    Per
    Everything
    works on a PowerPoint slide

  15. #15
    Non-Member
    Join Date
    Jan 2003
    Posts
    5,748
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    mkrz - that bad huh ? Yet to read it myself; only passing it on since there is a discussion about it.

    been - I agree with mkrz - use HarryF's Dao for this.

    I cannot understand as to why you want to pass more overhead on your server simply to read in some query variables ?

    Nice idea and all but pointless IMHO - In HarryF's example, you'd only every need to change one class and that's it - in this case, it'd be the query it's self and nothing more.

    Honestly, this is the better method. Have a look and you'll see for yourself....


  16. #16
    SitePoint Wizard gold trophysilver trophy
    Join Date
    Nov 2000
    Location
    Switzerland
    Posts
    2,479
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In the link HarryF posted I saw a line re-appearing all over the place: 'global $db'
    With the risk of being called a total ripoff-guy: 'GLOBALS ARE EVIL' (btw, HarryF, didn't you write an article about that? ;-) )
    It was magic quotes but agree; in general probably a bad idea. Don't think DataObject ties you to doing it this way though.

    One other approach (seems like everyone in PHP is thinking along the same lines right now) is what John Lim, author of adodb has just come up with;

    http://php.weblogs.com/discuss/msgReader$2508 - this is more aimed at database abstraction but I guess it could be used for this problem.

  17. #17
    SitePoint Guru Richi's Avatar
    Join Date
    Jul 2001
    Location
    Hu,Bp.
    Posts
    842
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by website
    Hello

    I am wondering how I could make a site so that I am not depended on some specific table names/structure, that is that the columns don't neccisarily have to be called something specific and I can easily adjust my site to differant table/column names.

    I do run all querys throught an object so I am not depended on some specific database and I wanted to take it a step further.
    I was just wondering what is the best way to do this?
    I had thought of doing querys like this:
    PHP Code:
    $q =& $db->query('SELECT {table1.col1} as name1, {table2.col2} as name2 FROM {table1}, {table2}'); 
    replacing table1/col1 etc with some convinient names that are saved in my system, then I store what the actual field name is and do preg_replace through all my querys to place the appropriate names, still, this method must be a little bit slow with all those preg_replace calls, or what ?

    If anyone is doing/knows how to something like this please share it, I would appreciate it very much!

    Thanks in advance!

    edit: isn't it independed ? I don't know what is wrong with me today ; )
    I think the best tutorial for this Ez Publish 3, you can build forms from admin interface, the reading for this for going by query base on the node id.

    I think the idea is easy, that the data is stored in the DB base on the datatype, which is predefined by some php lines.

    there are 2 table where the data is stored,
    1. contentobject , include the the id,data, typeid
    2. contentobject_attribute: include the typeid, typename.

    so the query will ask the data from contentobject, and will format it base on contentobject_attribute, such input,textarea,enum .. ect ..

    Best thing just download it from ez.no , and look t the DB design and the php code !

    this what i think, didn`t test it, what you think ?


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
  •