Efficiently using PHP OOP to reduce MySQL queries.

  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

I am currently trying to rewrite a large amount of procedural code on my website into classes, so I can reuse them and to simplify some scripts that are becoming just incredibly huge. For most basic things, this isn't too difficult, like database wrappers, a login class, template classes, etc...

However now I'm trying to represent data sets in my database with classes. For example, let's say I have 3 tables in my database: "Sports", "Teams" & "Players". There can be many sports, each sport can have many teams and each team can have many players (Many "1 to many" relationships in my database). I wish to create a class for each of these, called "Sport", "Team" & "Player". The problem is a lot of my current programming on my website is using libraries of functions and queries which are combined with many joins. I started writing a few classes to represent these, however when I get to calling data from the database and planning how I will implement these classes in the long run, I just don't see a clear way to do it, and do it efficiently.

I stumbled upon this, which is about the same problem I'm having, but I didn't really get much out of reading the responses: http://www.daniweb.com/forums/thread221776.html

So far I've been making my database calls occur when the object/class is loaded, but normally that entity/object ends up needing data from many other tables. I've also thought about Extending the classes or just making them aggregate classes (which store other classes in themselves in arrays), like the following example...

Note: this is straight from pg. 119 of the book "Object Oriented PHP: Concepts, Techniques and Code" by Peter Lavin.
PHP Code: [ Select ]
<?php
class Player{
private $name;
private $position;
public function __construct($name){
$this->name = $name;
}
public function getName(){
return $this->name;
}
public function setPosition($position){
$this->position = $position;
}
}
 
class Team{
private $players = array();
private $name;
public function __construct($name){
$this->name = $name;
}
public function addPlayer(Player $p){
$this->players[] = $p;
}
public function getPlayers(){
return $this->players;
}
public function getName(){
return $this->name;
}
?>
  1. <?php
  2. class Player{
  3. private $name;
  4. private $position;
  5. public function __construct($name){
  6. $this->name = $name;
  7. }
  8. public function getName(){
  9. return $this->name;
  10. }
  11. public function setPosition($position){
  12. $this->position = $position;
  13. }
  14. }
  15.  
  16. class Team{
  17. private $players = array();
  18. private $name;
  19. public function __construct($name){
  20. $this->name = $name;
  21. }
  22. public function addPlayer(Player $p){
  23. $this->players[] = $p;
  24. }
  25. public function getPlayers(){
  26. return $this->players;
  27. }
  28. public function getName(){
  29. return $this->name;
  30. }
  31. ?>


I've also thought about just somehow gathering my data outside of the class and then putting it in, which makes no sense what-so-ever for what I'm doing. When I google anything to do with OOP/data/MySQL optimization, the results are flooded with database wrapper examples instead of what I need to know. Does anybody know a book where I could learn how to better go about doing this or have a way they like to do it?
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Rabid Dog
  • Web Master
  • Web Master
  • User avatar
  • Posts: 3245
  • Loc: South Africa

Post 3+ Months Ago

The only way you can really make it efficient is a pain in the backside. Unfortunately domain models don't map very cleanly to database models.

That being said you might look at making sure your domain model is clean and on load of one of your primary objects load child objects via joins as opposed to calling each child objects load mechanism. This is fine but you run into the problem that you load data that isn't really needed at that point.

The next option would be to "lazy load" child objects. This implies only loading the object when it is needed.

There is no real way to optimize this as there is no query caching and once you make a call and finish with the objects it is pretty much onloaded. The only way to get round this (I don't even know if PHP is capable of this) is to load of commonly used types in a globally accessible cache. When you query you add it to the cache engine so if you ever need to retrieve that object again you fetch it from cache as opposed to hitting the db. This would require you updating that object should any changes be made against it and the committing those changes.

Again I don't know how much you gonna benefit from a mechanism like this as I know Java and .NET have the capacity build an "Engine" that maintains it's state through out the life span of the application. Not sure PHP has any mechanism to do this unless you start hacking things into the OS
  • PolishHurricane
  • Mastermind
  • Mastermind
  • User avatar
  • Posts: 1585

Post 3+ Months Ago

Hmmmm... Enlightening, thanks for that. I wish I read this earlier. I didn't think about creating a statement that is simply extended via joins.

I ended up making single instance type classes with child classes that extend them. Each of the classes can take either an ID representing a row in the database (and the class itself will load the data via SQL with an overloaded load function) OR the class can be force-fed data to it's load function from a larger query individually.


PHP Code: [ Select ]
<?php
$Sport = new Sport($ID);
$Sport->do_something();
 
$Sport = new Sport(0,$QUERY_RESULT);
$Sport->do_something();
?>
  1. <?php
  2. $Sport = new Sport($ID);
  3. $Sport->do_something();
  4.  
  5. $Sport = new Sport(0,$QUERY_RESULT);
  6. $Sport->do_something();
  7. ?>


So, if I wish to get multiple instances or load multiple instances of a type of class, I made smaller classes that do 1 query but pass the data to the overloaded load function of each single instance of each class. Then I can do something like this:

PHP Code: [ Select ]
<?php
$Sports = new Sports();//does 1 big query and makes multiple calls to new Sport(0,$QUERY_RESULT); and returns an array of singular Sport() objects
foreach($Sports->get() as $Sport)
{
$Sport->do_something();
}
?>
  1. <?php
  2. $Sports = new Sports();//does 1 big query and makes multiple calls to new Sport(0,$QUERY_RESULT); and returns an array of singular Sport() objects
  3. foreach($Sports->get() as $Sport)
  4. {
  5. $Sport->do_something();
  6. }
  7. ?>


The benefit of this is it looks really clean in my driver, I can also sort multiple instances easily and such with the plural wrapper class. However, the problem with child classes that extend the single instance classes is the more and more the class gets extended, the larger and larger the queries are because I load data for the parent in the childs query. Also there are 2 queries per datatype in the source code (1 for the Sports class and 1 for the Sport class [if it needs to call it]). However I think what you said with JOINs solves that problem, perhaps I could figure out how to keep a solid MySQL statement from child to child. I think I could do that while maintaining what I setup.

Thanks.

Oh BTW, do you know if it's possible to dodge using the $Sports->get() in the code example above? I couldn't return an array of objects out of the constructor, so I had to make a get() method, but I wish it worked like this:
PHP Code: [ Select ]
<?php
$Sports = new Sports();//returns an array of singular Sport() objects
foreach($Sports as $Sport)
{
$Sport->do_something();
}
?>
  1. <?php
  2. $Sports = new Sports();//returns an array of singular Sport() objects
  3. foreach($Sports as $Sport)
  4. {
  5. $Sport->do_something();
  6. }
  7. ?>

Post Information

  • Total Posts in this topic: 3 posts
  • Users browsing this forum: No registered users and 118 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.