SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Boston
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    little help anyone??

    HI!!
    Okay, so I've been practicing my php and mysql stuff. I'm actually starting to enjoy using mysql and creating queries and stuff (almost more than php programming ... don't hurt me).

    Anyway, I've come to a little bump in the road: where should I save my queries? Should it be a class? Maybe procedural functions? Should it be just a static file? Or, save the same query in many places (ewww)?
    I'm just not sure.

    My tables are like this:
    Code:
    +----------------------+
    | Tables_in_dbMediaNew |
    +----------------------+
    | tblComposers         |
    | tblContainer         |
    | tblContainer2Medium  |
    | tblGroupsOfItems     |
    | tblItem              |
    | tblItem2Container    |
    | tblItem2ItemType     |
    | tblItem2Song         |
    | tblItemType          |
    | tblLeaders           |
    | tblMedium            |
    | tblPerformers        |
    | tblPersonnel         |
    | tblPlayAlong         |
    | tblRichTextItem      |
    | tblSong              |
    +----------------------+
    16 rows in set (0.00 sec)
    And the db is for multipurpose sites (yes sites, plural).

    As a quick example, take these tables:
    Code:
    (tblSong)
    +-----------+--------------+------+-----+---------+----------------+
    | Field     | Type         | Null | Key | Default | Extra          |
    +-----------+--------------+------+-----+---------+----------------+
    | SongID    | int(11)      |      | PRI | NULL    | auto_increment |
    | SongTitle | varchar(250) | YES  | MUL | NULL    |                |
    +-----------+--------------+------+-----+---------+----------------+
    2 rows in set (0.00 sec)
    
    
    (tblPersonel)
    +-------------+--------------+------+-----+---------+----------------+
    | Field       | Type         | Null | Key | Default | Extra          |
    +-------------+--------------+------+-----+---------+----------------+
    | PersonnelID | int(11)      |      | PRI | NULL    | auto_increment |
    | FName       | text         | YES  |     | NULL    |                |
    | LName       | varchar(250) | YES  | MUL | NULL    |                |
    +-------------+--------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    
    (tblComposers)
    +-------------+---------+------+-----+---------+-------+
    | Field       | Type    | Null | Key | Default | Extra |
    +-------------+---------+------+-----+---------+-------+
    | SongID      | int(11) |      | PRI | 0       |       |
    | PersonnelID | int(11) |      | PRI | 0       |       |
    +-------------+---------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    Now, what I need to do from different sites is say, search a song and show the list of songs and their composers (personnel). Or search a composer (personnel) and show all the songs they wrote as well as the rest of the composers for those songs (if they had writing partners).

    I'm pretty sure I know how to build the queries (i've done the sites procedurally), but I want to make sure before I take the final plunge to fully dynamic with OO.

    I feel I have it easier in showing a single result as compared to a browse of titles.

    Any advice is good advice, it's up to me to apply it right

    Thanks!!!

  2. #2
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First, a little off-topic advice. Get rid of the "tbl" prefix. Also, use lowercase_underscore for your naming convention. Prefix + ProperCase is so 1990's MS-style. You won't find many PHP programmers using that convention. And it's really unnecessary, anyway. The lowest common denominator among databases is lowercase_underscore. Just a suggestion.

    As for where to store queries, well, MySQL doesn't allow creation of stored procedures like SQL Server, so you'll have to store them in the code somewhere. How you do that is really up to you. PHP isn't really great at OO, but you may consider creating a DAL (Data Access Layer) to abstract your database, similar to how us lowly Java guys do it. Or you could create an include of strings, or god forbid, script out your queries right in your code.

    Personal preference.

  3. #3
    SitePoint Member
    Join Date
    Jul 2004
    Location
    Boston
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    THanks for the reply!!
    as far as the prefix and name schemes, it was a team decision (although we may want to change it, maybe in the near future).

    Anyway as far as the different options. i've been toying with them all. Again it's a matter of I'm trying to create multple sites that are multi-purpose, so they'll only use some of the queries some of the time, with the possibility of using all of them, over time.

    I thought perhaps of the include and all, but it's a matter of using the OOP interfacing I've used based upon the suggestions in Harry Fuecks book for the db connection and mysql class to run queries (very helpful).

    My big issue is that I'm working on a team, and I want it to be an easy read for all (obviously with comments), but make it flow simply and efficiently.

    That said, this is where I'm stuck. I'm down to probably doing an include. If I put the queries embeded in the pages and I need to edit a query, I fear that I'll be in deep trying to fix each site (I'm looking at about a dozen different sites for different purposes and with different security, but with a central database for basic content).
    Last edited by emgillis; Jul 27, 2004 at 07:08.


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
  •