Ok, so I thought about implementing the Active Record Design Pattern for mapping a class over some tables in my MySQL database.
The thing is I want a generic class that will shuffle through my tables, doing basic CRUD transactions, based on what properties (fields from my db) are selected.
So I have methods for CRUD, which I want to select based on the properties passed to the object, properties which I access using __get and __set methods.
I thought about using an array, having it's keys as table prefixes and it's values another array containing the list of properties from each table.
But I think this is to complicated, cause I`ll have to shuffle thru a whole array to select the specified table.
Can you point me to some design tips I could use? Thanks!
Use a pre-built library, like Doctrine, Propel or PHPActiveRecord that can generate your class files from the existing DB Schema. Also have a look at http://martinfowler.com/eaaCatalog/index.html for some ideas about the fundamental patterns used in Object Relational Mapping, etc.
If your problem to solve is "handle tables" then that is the way to implement.
What are the commands you wish to perform? insert record, delete record?
Create a entity Table that represents a table, value object Column that represents a column, a Record that represents a table row, and fine grain as much as needed.
Then you are ready to use every single table.
Usage should be as simple as:
<?php
$usersTable = new Table($connection, 'users');
// sample search
$userRow = new Record('user');
$usersTable->find(array('id' => 1), $userRow);
// sample insert
$row = new Record('users', array('id' => 1, 'name' => 'jose'));
$result = $usersTable->insert($row);
But would only recommend that is that is really the problem, otherwise you should represent business entities as such: Users, Order, etc..