PHP and MySQL have long enjoyed a symbiotic relationship, with the historically popular duo responsible for powering millions of websites around the globe. The attractiveness of these technologies is due in part to the simplistic, straightforward syntax they each offer. In fact, it’s so easy to interact with a MySQL database via a PHP-driven website that I believe it can be a double-edged sword, causing developers to fall prey to less-than-optimal coding strategies.
One particularly prominent mistake many programmers make is heavily intermingling the application logic and the domain model. PHP developers, for example, have a tendency to jam a tremendous amount of SQL into their website logic, resulting in undecipherable, unmaintainable code. Quite a number of products have emerged to help developers avoid this practice, most notably MVC frameworks such as the Zend Framework.
However, whether you’re using an MVC framework or writing custom code, you can further separate your application logic from the domain model by taking advantage of two relational database features, views and stored procedures. Both features enable you to bundle more of the domain model’s logic within the database server itself, thereby minimizing the number of changes you’ll need to make later due to code refactoring — not to mention assigning more control over domain model-related matters to a database administrator when working in larger team environments.
MySQL stored procedures and views can not only go a long way towards separating the logic and domain model, but they also can easily be integrated into PHP. This article introduces stored procedures and views, and shows you how to use them within PHP code.
Introducing Views
Views are essentially query aliases that can greatly simplify the syntax used to execute an otherwise lengthy query, such as a table join. For instance, suppose you were building an e-commerce website and wanted to provide customers with an easy way to view their previously purchased products. This feature often involves using a table join, which might look like this:
SELECT p.id as id, p.title as title FROM products p LEFT JOIN order_detail od ON od.product_id = p.id LEFT JOIN orders o ON o.id = od.order_id WHERE o.customer_id='45753'It’s pretty easy to embed this statement within numerous parts of your application, but what if later you wanted to retrieve the product’s manufacturer also? Or what if the database administrator deemed it necessary to refactor the schema and rename the product table’s
title column to name? Such lengthy joins only add to the source code’s complexity. Using a view, you can eliminate many of the challenges surrounding embedding complex queries and instead use an alias such as this to execute the join:
SELECT * from products_customer_owns WHERE o.customer_id = '45753';
To create the view, log in to the MySQL client or a Web-based utility such as phpMyAdmin and execute the following query:
CREATE VIEW products_customer_owns AS SELECT p.id as id, p.title as title FROM products p LEFT JOIN order_detail od ON od.product_id = p.id LEFT JOIN orders o ON o.id = od.order_id;After creating the view, if you later decide to add another column to it, all you need to do is log back into the MySQL server and use the ALTER VIEW statement to change the view’s retrieved columns. After the view is changed, the new column will immediately be made available within any rowsets retrieved by the view query.
