Table joins accross multiple database tables and objects in PHP and MySQL

Question

I have 4 classes, each with their own database tables:

GlobalObject
Image
Project
Page

GlobalObject has 3 properties

global_object_id
added_by
datetime_added

Image, Project and Page have many different fields, they ALL have the following though:

id
global_object_id (Foreign key)

At the moment, my class structure has Images, Projects and Pages as subclasses of the GlobalObject class, this allows the 3 subclasses access to the variables that are required of them, datetime_added etc.

How should I set the properties using PHP and MySQL? At the moment, ALL of the fields (including those in the global_object table) are in each section's table (except global_object_id which cannot exist without the new table - and is why I need to do this), So i request the data from the Image table for example, which includes all the properties in the parent class and is set using parent::set_by_row($database_args), and $this->set_by_row($database_args);

At the moment I do the following (Using Image as an example):

class Image extends GlobalObject
{
$id;
$title;

function set_by_id($id)
{
// Select Q from Image table
$this->set_by_row($db_result);
}

function set_by_row($args)
{
parent::set_by_row($args);
// Set class properties
}

}

To reiterate: I want to be able to do the following:

$image = new Image()
$image->set_by_global_object_id(23);

And the image be set, including everything in the parent class (which is stored in a separate table)

Please ask away if any of that is unclear.

I'm not sure where you're stuck. Are you using any Object-Relational Mapping library, such as Doctrine? Or are you trying to build your own? Maybe this link will help: [SO - Good PHP ORM Library?](http://stackoverflow.com/questions/108699/good-php-orm-library) — bfavaretto, Mar 08 '12 at 18:54
All my own. Basically, I want the parent class to be set by a different table to the subclass, but for all the properties to be available after setting the subclass. — Alex, Mar 08 '12 at 18:56
Okay, but where do you need help specifically? To create the MySQL query? To create your class methods? Both? In case it's both, your question might be to broad for stackoverflow... And/or you should be using a third-party library for this. — bfavaretto, Mar 08 '12 at 19:06

score 0 · Answer 1 · answered Mar 08 '12 at 19:31

A very convenient way is offered by those "magic methods", like __get and __set. The following example provides read-only access to the data, but can easily be extended with a __set method.

abstract class GlobalObject {
    protected $_data = array();

    /* Magic Method: Enable read access via $this->name */
    public function __get($key) {
        if (isset($this->_data[$key])) {
            return $this->_data[$key];
        }
        throw new Exception('Property "'. $key .'" not found.');
    }

    protected function _setFromArray(array $data) {
        // .. do whatever needs to be done with the data ..

        $this->_data = $data;
    }
}

Now just extend this class:

class Image extends GlobalObject {
    public function setByGlobalId($id) {
        $result = mysql_query('SELECT i.*, g.* FROM images AS i
            LEFT JOIN global_objects AS g
            ON g.global_object_id = i.global_object_id 
            WHERE i.global_object_id = '. intval($id) .' LIMIT 1');

        // @todo check if result is empty

        $data = mysql_fetch_assoc($result);
        $this->_setFromArray($data);
    }
}

And a simple example:

$image = new Image();
$image->SetByGlobalId(42);
echo $image->added_by;

Table joins accross multiple database tables and objects in PHP and MySQL

1 Answers1