Re: [chiPHPug-discuss] Data Models

[Arlo L. <ar...@ar...>]

Hello,

> I am curious what people use for fetching data in PHP. I've been calling 
> this my model.
> My goal is to write little-to-NO hardcoded SQL into my PHP.
> 
> I'm curious to hear about any strategies on this problem, or any 
> insights that may have been encountered in similar endeavors.


My experience sounds a lot like yours. I've always used a custom library file that handles SQL queries, adding error logging options and escaping and smoothing out some of the wrinkles in PHP's MySQL functions. Then a year ago I started using OOP for all but the simplest projects. That served to further abstract the data model from my code, and now I never find myself needing any SQL outside of my classes.

Here are some notes about how I've ended up building my classes:

- I typically have one class for each significant database table. For example, I might have a "user" table, with a related "user_status" and "user_location" table, but the User class would encompass all three.

- The User class would contain a getRecords method, which takes a list of IDs and returns an array of data about the requested users. For relational data like user_status, my convention has been to return the ID value as $status, but the text value from the user_status table as $status_display. Then the code calling the method can use whichever value is most convenient. I can call this method directly if I know the IDs of the records I want to get.

- The class also contains a getUsers method, which takes an associative array of search criteria, from which it builds a more complex query to pass into getRecords. The search criteria are specific to each class and might include something like $login_enabled. The nice thing here is that the definition of "login_enabled" can be fairly complex and encompass more than one database field, but my code doesn't have to know about that -- it's only defined in this one spot. Also, this method constructs the query based on what criteria are used, so it just joins the tables it needs. I use this method when I don't know the IDs but I want to get a particular set of records.

- And I have a Search class that passes requests through to the other classes but adds pagination functionality. This is a separate class because this functionality is the same for all classes, so it would be redundant to build it into each class.

- The classes also contain saveToDB methods, but this is very straightforward because the data I save is typically in a one-to-one relationship with the fields they save into. For example, the $status value just saves to the status field, and I don't have to worry about the related user_status table. If I need to set something like "login_enabled" which might involve changing more than one field, I would add a specific method for that.

- And some classes have additional "convenience" methods for common tasks. For example, if I often want to get a user's status and don't care about the other data, I'll make a getUserStatus method with a simple SQL query that bypasses those all-purpose methods.

I hope that makes sense / helps!

Cheers,
-Arlo

_______________________________

Arlo Leach
773.769.6106
http://arlomedia.com

The killer app for the iPad
http://washboardapp.com