Designing a general database interface in PHP

Question

I'm creating a small framework for my web projects in PHP so I don't have to do the basic work over and over again for every new website. It is not my goal to create a second CakePHP or Codeigniter and I'm also not planning to build my websites with any of the available frameworks as I prefer to use things I've created myself in general.

I have had no problems in designing and coding the framework when it comes to parts like the core structure, request handling, and so on, but I'm getting stuck with designing the database interface for my modules.

I've already thought about using the MVC pattern but found out that it would be a bit of an overkill for my rather small project(s).

So the exact problem I'm facing is how my frameworks modules (viewCustomers could be a module, for example) should interact with the database.

• Is it (still) a good idea to mix in SQL directly into PHP code? (Would be "old way": mysql_query( 'SELECT firstname, lastname(.....))?

• How could I abstract a query like the following?

  SELECT firstname, lastname FROM customers WHERE id=X
  

Would MySQL "helper" functions like

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

be a good idea?

I'm not really sure because they tend to become useless when dealing with more complicated queries like the pretty much trivial one above.

• Is the "Model" pattern from MVC my only real option to solve this?

• What do you currently use to solve the problems shown above?

Answer 1

I believe you just want to get access to your DB from your module. I'd avoid using mysql_query directly from the code. Rather, going for simple model with abstracted DB access would be easy and straight-forward.

For example, you can have a file like models/Customers.php with this code:

<?php

class Customers {

    public function getById($id) {
        $sql = "SELECT first_name, last_name FROM customers WHERE id='$id'";
        $res = $DB::getRow($sql);
        return ($res);
    }
}

I am assuming some kind of DB helper is already instantiated and available as $DB. Here is a simple one which uses PDO.

Now, you should include this in your module and use the following way:

<?php

include_once "models/Customers.php";

$customers = new Customers();
$theCustomer = $customers->getById(intval($_REQUEST['cust_id']));


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

Cheers.