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.
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.
Introducing Stored Procedures
Just as programming languages such as PHP support the ability to create custom functions, which encapsulate code and allow it to be repeatedly called using a designated keyword, so can you create functions that operate in the same fashion within the database server (known as stored procedures).
For instance, suppose you wanted a simple way to calculate the sales tax of an order based on a 5.5% rate. Because this rate might change over time and you may need to calculate an order's sales tax within multiple locations on the site, consider using a stored procedure to perform the calculation for you. An example of such stored procedure follows. Like the previously created view, all you need to do is log in to the MySQL client or phpMyAdmin and execute the following statement:
DELIMITER // CREATE FUNCTION calculate_salestax (order_id INT) RETURNS DECIMAL(5,2) BEGIN DECLARE tax DECIMAL(5,2); SELECT SUM(price) INTO tax FROM order_details WHERE order_id = order_id; SET tax = tax * .055; RETURN tax; END; // DELIMITER ;
As you can probably see from this example, you can not only pass input parameters into the stored procedure (the lone parameter in this example is identified as order_id), but you also can return calculated output back to the caller.
After defining the stored procedure, you can call it like this:
Advanced database features such as views and stored procedures can go a long way towards helping consolidate your application code, in addition to centralizing control over the domain model's behavior within the database server.