Is separating MySQL code from PHP code a standard, common practice?

Question

I've been told that it's a common practice to separate MySQL code from PHP code, is this true? Is this like a new coding standard? Are there any benefits of doing such?

Can someone direct me what are best coding practices (PHP/MySQL) when writing for masses (open source script for example).

Thanks!

Answer 1

Good question!

• I've been told that it's a common practice to separate MySQL code from PHP code, is this true?
  • absolutely true!
• Is this like a new coding standard?
  • and old old concept. there is no standard, but defacto exists
• Are there any benefits of doing such?
  • many benefits
  • benefits from de-coupling between Layers.
  • when business logic is de-coupled from Presentation logic, if requirement changes you can change it easily.
  • so not only separate sql from php, also separate HTML code from php logic

using sqlMap

in the Java world there is MyBatis SqlMapper. This is very commonly used and this is what you want exactly if you are Java programmer.

But in PHP, hard to find something like MyBatis, the only thing I have found is PRADO http://www.pradosoft.com/

using ORM

ORM = Object Relation Map. http://en.wikipedia.org/wiki/Object-relational_mapping This is also used. but i don't know there is ORM framework for PHP. if anyone knows, please let me know. @jszobody has suggested http://www.doctrine-project.org/projects/orm.html (Thanks)

thanks.

Answer 2

I've been told that it's a common practice to separate MySQL code from PHP code, is this true?

I think, you are talking about Loose coupling, Separating your application logic in parts (Building a good architecture), so you can keep things organized, maintainable. Actually, this is too broad discussion and I may not properly describe everything but to answer your question, Yes, it's a good (recommended) practice and a design principle in software development.

In an application, the most important part is the domain/business logic, which serves the application and this should be kept separate from other logics, such as presentation logic (UI). For example, if your application, deals with creating of users, products then you may keep them in a separate place each one as an entity of your application. For example

User management

class User {

    public function createUser()
    {
        // ...
    }

}

Product management

class Product {

    public function addProduct()
    {
        // ...
    }

}

This are your business logic and you may use/call these from a separate class/place like

$user = new User;
$user->create();

Now, think about the data storage mechanism, if you create a new user, you may need to put all the information in a place, it maybe a database, a flat file or ccould be an xml file, so, what should you do in your createUser() function, you should use a data access layer between your data storage and business logic, which means, whatever data storage you use, you should not use any code in your createUser() function (business logic) directly, instead you access the data storage via another layer (DAL), for example, your createUser() function may look like something like this

class User {

    protected $storage = null;

    public function __construct($storage)
    {
       $this->storage = $storage;
    }

    public function createUser()
    {
        $userData = $_POST['first_name'];
        $userData = $_POST['user_name'];
        // more fields...

        // data validation

        $this->storage->save($userData);
    }
}

Now, to create a user and save it in a database, you can use it like

$storage = new DbStorage();
$user = new User($storage);

Maybe, you want to use a flat file to store data instead of a database, no problem, just change the data storage, i.e.

$storage = new FileStorage();
$user = new User($storage);

Now, see the difference, Your save method doesn't know about storage mechanism, it just calls storage->save() and each storage has it's own save mechanism, things are organized, clean and easy to maintain, change the storage mechanism without making any changes in your code. So, it's like

Business Logic --> Data Access layer --> Data Storage

Here in data access layer, you should write code according to your storage mechanism, if you have a database as a storage then you write code according to that and in this case, your DbStorage may have it's own abstract database layer (different drivers for different Database, i.e. mysql, oracle etc), here ORM comes in action.

So, main thing is to keep things loosely coupled, which help you to build a clean and maintainable application. This is a little idea only. These links may help you

1. Separation of concerns
2. SOLID
3. Software design pattern
4. PHP - The Right Way
5. Data access layer
6. ORM

Update : I think I should add a bit about database abstraction layer's example

So, when you use a database for storage mechanism, you may use

$storage = new DbStorage();
$user = new User($storage);

But, this DbStorage should contain another layer for different types of databases, i.e. myaql, oracle etc. In this case, your DbStorage may look like this

class DbStorage{

    protected $db = null;

    public function __construct($database)
    {
        $this->db = $database;
    }

    public function save($data)
    {
        $this->db->insert($data);
    }

}

To use it, you may use code like this

$config = array('hostname' =>'localhost', 'username'=>'root', 'password'=>'123456', 'database'=>'myDatabase');
$storage = new DbStorage(new MySqlDriver($config));
$user = new User($storage);

Or for another database engine, you may use

$config = array('hostname', 'username', 'password');
$storage = new DbStorage(new OracleDriver($config));
$user = new User($storage);

Now, your different database drivers may look something like this

class MySqlDriver(){

    public $connection;
    public $database;
    public function __construct($config)
    {
        $this->connection = mysql_connect($config['hostname'], $config['username'], $config['password'], $config['database']);
        mysql_select_db($db);
    }

    public function insert($data)
    {
        // code for insert (mysql)
    }

}

For oracle you may have another class as OracleDriver

class OracleDriver(){

    // ...

    public function __construct($config)
    {
        // ...
    }

    public function insert($data)
    {
        // code for insert (oracle)
    }
}

This is just an idea of database abstraction layer and in most cases, configuration for each database (MySqlDriver, OracleDriver) may contain in configuration file, i.e. database.php and it my contains array like

return array(
    'mysql' => array(
        'hostname'=>'localhost',
        ...
    ),
        'oracle' => array(
        'hostname'=>'localhost',
        ...
    )
);

This is just a rough idea, you may find better examples online and there are lots of open source libraries to use in live projects.

Answer 3

Yeah, this is standard across many programming languages. Typically you would write stored procedures and have your php code leverage those to do the database work. This separation makes it easier to manage your code base and helps with maintenance and extensability.

Answer 4

Don't take this advise too literally.
Because there is no way to follow it.

As you can see, noone provided you with example script. That's quite a problem with this site. Too much knowledge I'd call it. And lack of practice.

So, It cannot be used as a rule of thumb. Using stored procedures, where you can do single primary-key based lookup, is just an overkill. And ORM cannot create whatever complex query for you. So, it's rather good intentions than "coding standard".

Yet there is a rule of thumb you can easily follow: never let your SQL and HTML to meet in a same file. This one is quite easy to follow and way more practical, because you have to change appearance more often than database backends.