I am developing a small framework for my web projects in PHP and so i do not have to perform the fundamental work again and again again for each new website. It's not transpire to produce a second CakePHP or Codeigniter and I am also not likely to build my websites with the available frameworks when i would rather use things I have produced myself generally.

I've had no problems in creating and coding the framework if this involves parts such as the core structure, request handling, and so forth, but I am getting tied to creating the database interface for my modules.

I have already considered while using MVC pattern but discovered that it might be a little of the overkill for my rather small project(s).

Therefore the exact problem I am facing is when my frameworks modules (viewCustomers might be a module, for instance) should connect to the database.

  • Could it be (still) smart to add SQL straight into PHP code? (Could be "old way": mysql_query( 'SELECT firstname, lastname(.....))?

  • How could I abstract a question such as the following?

    Choose firstname, lastname FROM clients WHERE id=X

Would MySQL "assistant" functions like

$this->db->customers->getBy( 'id', $x )

be advisable?

I am not necessarily sure simply because they often become useless when confronted with more difficult queries such as the virtually trivial one above.

  • May be the "Model" pattern from MVC my main choice to solve this?

  • Exactly what do you presently use to resolve the issues proven above?

perhaps you have investigated http://world wide web.doctrine-project.org/ or any other php orm frameworks (zend_db involves mind)?

In my opinion you want to obtain access to your DB out of your module. I'd stay away from mysql_query from the code. Rather, opting for simple model with abstracted DB access could be simple and easy , straight-forward.

For instance, you'll have a file like models/Clients.php with this particular code:


class Clients Choose first_title, last_title FROM clients WHERE id='$id'"

        $res = $DB::getRow($sql)

        return ($res)


I'm presuming some type of DB assistant has already been instantiated and available as $DB. Here is a straightforward the one that uses PDO.

Now, you need to include this inside your module and employ the next way:


include_once "models/Clients.php"

$clients = new Clients()

$theCustomer = $clients->getById(intval($_REQUEST['cust_id']))

echo "Hello " . $theCustomer['first_name']


If you want speed, then use raw queries (however, you should certainly use PDO with prepared queries).

If you would like some thing OOP, you are able to —as you suggest it— design this with assistants.

Once, I have designed such like which in fact had the next concept:

  1. DB connection/handler classes (handling multi-connections to various databases and various servers for example MySQL, Oracle, etc.)
  2. A category per action (ie. Choose, Remove, etc.)
  3. Filter classes (eg. RangeFilter)

The code looked something similar to this:

$choose = new Choose('field1', 'field2', )

$result = $choose->from('myTable')

                 ->addFilter(SQLFilter::RangeFilter, 'field2')

                 ->match(array(1, 3, 5))

                 ->unmatch(array(15, 34))


It is a simple illustration of the best way to construct it.

You are able to go further and implements automated handling of table relations, area type check (using more self examination in your tables), table and area alias support, etc.

It could appear to become a lengthy and effort, but really, it will not get you much time for you to make all of these features (≈1 month).

Three tips:

  • Use Saved Methods (to help you separate the php in the db)
  • Use PDO/MySQLi for prepared claims CALL NEWS_LIST(?, ?)
  • Make use of a Static Class for the DB. Enables you to definitely can get on within any module.