I'm working on an ORM experiment, and I'm looking for ideas to help choose which path to take in it's development. I currently have an index showing how to use it, two interfaces, a database class, a table base class and two auto-generated classes (representing a database table).

The first on my list is to implement the Update method (Read and Delete work). I'm also thinking of extending the BaseTable with methods, so the restrictions from MySQL are automatically used. It also needs a database interface check, so the database class can be swapped, as long as it is implementing the IDatabase interface (similar to how the database checks for a valid IBaseTable interface).

Looking forward to your thoughts.

Here's the code so far:

index.php

<html>
<body>
<pre>
<?php
/**
 * Interfaces.
 */
include 'IDatabase.interface.php';
include 'IBaseTable.interface.php';

/**
 * Classes.
 */
include 'Database.class.php';
include 'BaseTable.class.php';

/**
 * Auto-generated classes.
 */
include 'companies.class.php';
include 'users.class.php';

/**
 * Connect to the database.
 */
$database = new Database('mysql:dbname=mydatabase;host=localhost', 'myuser', 'mypassword');
if (is_a($database, 'IDatabase'))
{
    /**
     * Create and read a users record into a users object.
     */
    $user = $database->Create('users');
    if (is_a($user, 'IBaseTable'))
    {
        if ($user->Read(3))
            print_r($user);
    }

    /**
     * Create and read a companies record into a companies object.
     */
    $company = $database->Create('companies');
    if (is_a($company, 'IBaseTable'))
    {
        if ($company->Read(1))
            print_r($company);
    }
}
?>
</pre>
</body>
</html>

IDatabase.interface.php

<?php
/**
 * Interface IDatabase.
 * Defines required functionality for the Database class.
 */
interface IDatabase
{
    function Read($object, $id);
    function Delete($object, $id);
}
?>

IBaseTable.interface.php

<?php
/**
 * Interface IBaseTable.
 * Defines required functionality for BaseTable classes.
 */
interface IBaseTable
{
    function Read($id);
    function Delete();
    function Copy($object);
}
?>

Database.class.php

<?php
/**
 * Class Database.
 * Wrapper class for creating, reading and deleting table objects/records.
 */
class Database implements IDatabase
{
    private $pdo;

    /**
     * Constructor
     * Connects using PDO.
     */
    public function __construct($dsn, $username, $password)
    {
        $this->pdo = new PDO($dsn, $username, $password);
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /**
     * Destructor.
     */
    function __destruct()
    {
        unset($this->pdo);
    }

    /**
     * Create returns a new table object, based on the class/table name (a factory).
     */
    public function Create($tableName)
    {
        if (!$this->isValidClassOrObject($tableName))
            return null;

        /**
         * Create/return a new object.
         */
        $result = new $tableName();
        $result->database = $this;
        return $result;
    }

    /**
     * Read copies a table record into a table object.
     */
    public function Read($object, $id)
    {
        if (!$this->isValidClassOrObject($object))
            return false;

        /**
         * Get the class name for $object.
         */
        $reflector = new ReflectionClass($object);
        $name = $reflector->getName();

        /**
         * Select a record.
         */
        $query = "SELECT * FROM `{$name}` WHERE `id` = :id";
        $statement = $this->pdo->prepare($query);
        $statement->bindValue('id', $id, PDO::PARAM_INT);
        $statement->execute();
        $result = $statement->fetchObject($name);
        $statement->closeCursor();

        /**
         * Copy the record into the current object.
         */
        return $object->Copy($result);
    }

    /**
     * Delete removes a table record, clears the table object.
     */
    public function Delete($object, $id)
    {
        if (!$this->isValidClassOrObject($object))
            return false;

        /**
         * Get the class name for $object.
         */
        $reflector = new ReflectionClass($object);
        $name = $reflector->getName();

        /**
         * Delete the record.
         */
        $query = "DELETE FROM `{$name}` WHERE `id` = :id";
        $statement = $this->pdo->prepare($query);
        $statement->bindValue('id', $id, PDO::PARAM_INT);
        $statement->execute();

        /**
         * Clear the current object.
         */
        return $object->Copy(new $name());
    }

    /**
     * isValidClassOrObject checks whether the passed class name or object
     * implements the IBaseTable interface.
     */
    private function isValidClassOrObject($classOrObject)
    {
        /**
         * ReflectionClass is used to retrieve information about a class/object.
         */
        $reflector = new ReflectionClass($classOrObject);

        /**
         * Get the class name.
         */
        $className = $reflector->getName();

        /**
         * Get the implemented interfaces.
         */
        $interfaces = $reflector->getInterfaceNames();

        /**
         * Check that class name is recognized (included),
         * and that it implements the IBaseTable interface.
         */
        if (empty($className) or !in_array('IBaseTable', $interfaces))
        {
            return false;
        }

        return true;
    }
}
?>

BaseTable.class.php

<?php
/**
 * Class BaseTable.
 * Implements IBaseTable.
 * Can read, delete and copy a table object/record.
 */
class BaseTable implements IBaseTable
{
    /**
     * Reference to a Database object.
     */
    public $database;

    /**
     * Call the Read from the Database object.
     */
    public function Read($id)
    {
        return $this->database->Read($this, $id);
    }

    /**
     * Call the Delete from the Database object.
     */
    public function Delete()
    {
        return $this->database->Delete($this, $this->id);
    }

    /**
     * Copy the contents of $object into the current object ($this).
     * Does not copy inherited properties.
     */
    public function Copy($object)
    {
        $reflector = new ReflectionClass($this);
        $reflectorCopy = new ReflectionClass($object);

        if ($reflector->getName() == $reflectorCopy->getName())
        {
            $properties = array_filter(
                $reflector->getProperties(ReflectionProperty::IS_PROTECTED | ReflectionProperty::IS_PUBLIC),
                function($prop) use($reflector)
                {
                    return $prop->getDeclaringClass()->getName() == $reflector->getName();
                }
            );

            foreach ($properties as $property)
            {
                $name = $property->name;
                $this->$name = $object->$name;
            }

            return true;
        }

        return false;
    }
}
?>

companies.class.php

<?php
/**
 * Class companies.
 * Defines properties for table companies.
 * Auto-generated.
 */
class companies extends BaseTable
{
    protected $id;
    protected $name;
    protected $website;
    protected $email;
}
?>

users.class.php

<?php
/**
 * Class users.
 * Defines properties for table users.
 * Auto-generated.
 */
class users extends BaseTable
{
    protected $id;
    protected $username;
    protected $email;
    protected $active;
    protected $joined;
}
?>

MySQL dump

CREATE TABLE `companies` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `website` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

INSERT INTO `companies` VALUES ('1', 'comment included', null, null);

CREATE TABLE `users` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `fullname` varchar(255) DEFAULT NULL,
  `email` varchar(255) NOT NULL,
  `active` int(1) NOT NULL,
  `joined` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `users` VALUES ('3', 'pritaeas', 'pritaeas', 'pritaeas@example.com', '1', '2013-08-31');

What do you do when you want to retrieve data by something other than ID?

Not sure yet. Haven't even thought about it, because for most of my applications I don't really need a search. Ideas are welcome though (I'm leaning towards a search object).

In addition, here's the code to generate the table classes:

<html>
<body>
<pre>
<?php
$date = date('Y-m-d H:i:s');

$pdo = new PDO('mysql:dbname=mydatabase;host=localhost', 'myuser', 'mypassword');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$statement = $pdo->prepare('SHOW TABLES');
$statement->execute();
while ($result = $statement->fetchObject())
{
    $content = "<?php\n";
    $content .= "/**\n";
    $content .= " * Class {$result->Tables_in_mydatabase}.\n";
    $content .= " * Defines properties for table {$result->Tables_in_mydatabase}.\n";
    $content .= " * Auto-generated on {$date}.\n";
    $content .= " */\n";
    $content .= "class {$result->Tables_in_mydatabase} extends BaseTable\n";
    $content .= "{\n";
    $stat = $pdo->prepare("SHOW COLUMNS FROM `{$result->Tables_in_mydatabase}`");
    $stat->execute();
    while ($column = $stat->fetchObject())
    {
        $content .= "\tprotected \${$column->Field};\n";

        // Available columns:
        // Type, Null (NO, YES), Key (PRI), Default,  Extra (auto_increment)
    }
    $content .= "}\n";
    $content .= "?>";

    if (file_put_contents("{$result->Tables_in_mydatabase}.class.php", $content))
    {
        echo "Generated {$result->Tables_in_mydatabase}.class.php\n";
    }

    $stat->closeCursor();
}
$statement->closeCursor();
unset($pdo);
?>
</pre>
</body>
</html>

Sounds like you're on the right track. Seeing the rest of your code I think I would use a function that would convert a query string into a real query. For example if you want to search for WHERE field = value, you could transform an input string like "field=value" into "WHERE field = value". Problem arises when you start thinking about LIKE comparisons.

Haven't really thought this through yet, but am leaning towards filling data in an object. Every field that has a value gets searched. A search type can be exact or like. What you suggest can work well for a general search method that would work over any table.

Every field that has a value gets searched.

How would that work? Because the way I just interpreted that is: a Person object gets constructed with first name Jack and last name Weesl, then how would you use this object to perform a search? I mean, if you already have the person named Jack Weesl, what else could you search for with the data you already have?

No, not with what you have of course. Construct a new empty object, and fill the properties with the values you want to search, and pass this object to a search method, which will return a list of matching objects.

Ah! Yea I didn't really think that it would work like I just described. But I hadn't thought of what you said, yet, either, so this is some insight for me =). Sounds like a neat plan.

Since I generate the table classes with protected properties, I have added three methods to the BaseTable to allow access. The reason for doing it this way, is that I want to implement some rules later for property access based on the database table. These are the three methods:

/**
 * Magic get.
 */
public function __get($name)
{
    $result = null;
    foreach ($this->GetProperties() as $property)
    {
        if ($property->name == $name)
        {
            $result = $this->$name;
            break;
        }
    }
    return $result;
}

/**
 * Magic set.
 */
public function __set($name, $value)
{
    foreach ($this->GetProperties() as $property)
    {
        if ($property->name == $name)
        {
            $this->$name = $value;
            break;
        }
    }
}

/**
 * Get the current class' properties.
 */
protected function GetProperties()
{
    $reflector = new ReflectionClass($this);
    $properties = array_filter(
        $reflector->getProperties(ReflectionProperty::IS_PROTECTED | ReflectionProperty::IS_PUBLIC),
        function($prop) use($reflector)
        {
            return $prop->getDeclaringClass()->getName() == $reflector->getName();
        }
    );
    return $properties;
}
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.