Inheritance of PHP and MySQL

Question

So, I'm trying to use good object oriented programming techniques with PHP. Most (all) of my projects are related to the MySQL database. My immediate problem is with the user model I need to develop.

My current project has agents and guides. Both agents and managers are users with much of the same information. Therefore, obviously, I want the agents of the class and class to extend the general class Users. Now my question is this:

What is the best way to process SQL to load these objects? I do not want to execute multiple SQL statements when creating an agent or master instance. However, the logic tells me that when starting the Users constructor, it must execute an SQL statement to load general information between agents and conductors (username, password, email, contact information, etc.). Logic also tells me that when I run the agent or explorer constructor, I want to execute SQL to load data that is unique to the agent class or slaves. But again, logic also tells me that it is a bad idea to execute 2 SQL queries every time I need an agent or a host (since there may be thousands).

I tried to find examples of how this is usually handled without success ... Perhaps I was just looking for the wrong thing?

Answer 1

Basically, you have three approaches to this problem (one of which I will fix immediately):

• One table for each class (this is the one I will delete);
• Type of record with additional columns; and
• The type of record with the child table, depending on the type you are joining.

For simplicity, I usually recommend (2). So, as soon as you have a table:

CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, type VARCHAR(10), name VARCHAR(100) ); 

where the type can be "AGENT" or "LEAD" (for example). Alternatively, you can use a single character type code. Then you can start filling in the blanks using the object model:

• You have the parent class of the user;
• You have two child classes: Lead and Agent;
• These children have a fixed type.

and he should easily fall into place.

As for loading in one of the operators, I would use some kind of factory. Assuming these barebones classes:

 class User { private $name; private $type; protected __construct($query) { $this->type = $query['type']; $this->name = $query['name']; } ... } class Agent { private $agency; public __construct($query) { parent::constructor($query); $this->agency = $query['agency']; } ... } class Lead { public __consruct($query) { parent::constructor($query); } ... } 

a factory might look like this:

 public function loadUserById($id) { $id = mysql_real_escape_string($id); // just in case $sql = "SELECT * FROM user WHERE id = $id"; $query = mysql_query($sql); if (!query) { die("Error executing $sql - " . mysql_error()); } if ($query['type'] == 'AGENT') { return new Agent($query); } else if ($query['type'] == 'LEAD') { return new Lead($query); } else { die("Unknown user type '$query[type]'"); } } 

Alternatively, you can use the factory method as a static method, for example, for the User class and / or use the lookup table for types for classes.

It is possible that contaminating classes with a query result resource like this is a dubious design in the strict sense of OO, but it just works.

Answer 2

Will you ever have a user who is not a host or agent? Does this class really need to retrieve data from the database?

If so, why not pull the SQL query into a function that you can override when creating the child class.

Answer 3

Could you inherit to say the SQL skeleton and then use the function in each subclass to complete the query based on its needs?

Using a really basic example:

 <?php //our query which could be defined in superclass $query = "SELECT :field FROM :table WHERE :condition"; //in our subclass $field = "user, password, email"; $table = "agent"; $condition = "name = 'jim'"; $dbh->prepare($query); $sth->bindParam(':field', $field); $sth->bindParam....;//etc $sth->execute(); ?> 

As you can see, my example is not surprising, but should let you see what I get. If your query is very similar between subclasses, I think my suggestion might work.

Obviously, this will require some tweaking, but this is probably the approach I would take.