[RESOLVED] DB Queriy repository

dougal85

It's strange, I've done quite allot of C# and Java and had no problems. However, designing a web app OO style is kicking my ass

Anyway, Following on from my attempt to understand this here

What I want to do. (correct me if this doesn't seem like the best solution)

I would like a database object. That contains all of my queries. so they are in one place making this the only file that is dependant on the DBMS. When a query is called it will return the information in the relevant object for. So say, the query is to select a user by there id it will return a populated user object. lets call this method 1.

This however seems like it is going to get very big, very fast. why? because each query would require a function or something to read through the results and put the right variables where they belong in the object.

So... I thought... should i make a function in the object class. to populate itself from the result array, since the class knows what it needs and it can just take what is there. So, this will be method 2.

Am I on track with either of those Ideas?

Any and all guidance is appreciated.

B_man

Possibly a single function or set of functions for doing the query and results, using a store of queries in an array and doing a replace on unique parts of the query? Something like...

// Array of queries
$queries = array(
     'blah' => 'SELECT blah FROM blah',
     'join' => 'SELECT t1.blah FROM t1 LEFT JOIN t2 ON t1.urmom = t2.ph33r ORDER BY woo WHERE t2.id = [1] AND t1.status = "[2]"', // note the bracketed numbers
);

// Function to get a single record (feel free to reorganise this terrible function)
function single_record( $qid )
{
     if ( $queries[ $qid ] )
     {
          $args = func_get_args();
          foreach ( $args as $i => $v )
          {
               $find[ $i ] = "[$i]";
               $repl[ $i ] = $v;
          }
          $query = str_replace( $find, $repl, $queries[ $qid ] );
          whatever_query( $query );
          while ( $i = whatever_fetch_array() !== FALSE )
               $out[] = $i;
          return ( is_array( $out ) ) ? $out : FALSE;
     }
}

// And the function in the code to get the data - in this case you're running the "join" query at the top, where the ID is 34 and the status is "inactive".
$cool_data = $sqlclass->single_record( 'join', 34, 'inactive' );

Yeah, I realise this doesn't put the data in an object like you wanted, but I'm fairly certain this sort of system could be applied to that.

dougal85

That's a pretty good idea, however to cover that part. I am going to use the PDO/PD)Statement objects. It's really quite cool, Gives a similar functionality to what you suggested.

http://uk2.php.net/manual/en/function.pdostatement-bindvalue.php

I maybe didn't explain well, let me try again. This is the way that I think would be best;

User object is created ($user)
$user->getUserById(1); is executed.
the user object, which has a reference by value to the already active/initialised database object, calls the method in the database object, for example $objData->user_getUserById($id);
The database object executes the query. It then returns the result array to the object; i made a dummy table, so something like this:

Array
(
    [0] => Array
        (
            [id] => 1
            [0] => 1
            [name] => dougal
            [1] => dougal
        )
)

the object then, since it knows what values it needs goes through these and populates itself with the information. and then we have a populated object, without too much repeating code in the database object. It will only hold the actual query.

However, the problem would be, in the case of multiple results being returned. I don't know what to do 🙁 Should a class contain methods that can potentially return multiple instances of itself.

so, say, you have a news class. Where would you put the method to return the 10 newest news items?

uugghh when i start to think I'm figuring this out, i walk into a (metaphorical) wall at 100 miles per hour.

B_man

Theoretically you could add an argument to a standard "get records from this table" method that would set the limit, and you could have the one method for any number of records.

Roger_Ramjet

dougal85 wrote:
so, say, you have a news class. Where would you put the method to return the 10 newest news items?

Just starting with php/mysql 5 so I'm in here looking for tips, especially for my db abstraction.

Anyway I know the answer to this one from VB: you have a news_items class that instantiates news_item objects in a collection (terminology may be wrong for php) That way you can tell it to get 5 or 10 or whatever. Again, in VB this would be 3 classes: news_item, news_list to contain the news_item objects, and news_items the wrapper that does the work of creating the objects and putting them into the list object.

Now, I'd be delighted to know how this is done in php, or if the vb approach is a good'un in php as well.

Roger_Ramjet

And PDO really looks like the business to me, so I'll be going with that as well Dougal, thanks for the pointer.

Now, I'm planning to take 'query repository' even further with extensive use of views and stored routines. To my mind your getUserByID should be a stored function in the db that you call, rather than sql that you execute in eg a prepared statement.

Shrike

This problem area is known as object-relational mapping. The basic solution is that objects should be able to load and save (persist) themselves transparently, without having to know about how the underlying data source works.

There are some PHP tools available which will be of interest to you. Doctrine and Propel. Doctrine is probably more favoured as it uses PDO.

For further reading I recommend Martin Fowler's Patterns of Enterprise Application Architecture, which goes into some depth on techniques of object persistence.