PHP 5 MySQL
PHP 5 provides an object-orientated interface to MySQL allowing your scripts to easily query and manipulate your databases. Previously, PHP 4 did not have a built-in OOP library to connect to MySQL and was done using function calls – very similar to how the MySQL C API library works.
What does the MySQL OOP Library provide?
PHP 5 provides a feature rich interface to MySQL allowing the developer to use all of the advanced features of MySQL including:
- Prepared SQL Statements;
- Input & Output Parameter Binding;
- SQL Sub-Selects;
- SQL Transactions with Transactional Databases;
- Multiple SQL Query Execution.
- MySQL SSL Connections
All of these features are provided within an OOP interface to allow implementation within your applications.
Traditional SQL Queries
Prepared SQL statements allow faster execution over native traditional SQL query execution. Everytime you make an SQL query, MySQL parses the request, validates the query, then executes it. Although MySQL does this extremely quickly, prepared SQL statements does this even faster, which provides benefits when a SQL query is executed multiple times within an instance of the database connection to MySQL. A prepared SQL statement tells MySQL what a query looks like and what parameters it should expect before it is executed. The prepared statements allow the binding of parameters for both input and output to the execution of the query.
Binding Parameters to SQL Queries
One of the main advantages of SQL parameter parsing is that when the parameters are bound to the statement they are automatically escaped by the underlying library to prevent SQL injection attacks. This can be extremely useful if a SQL query is dependant upon form data input from a webpage, which is entered by a user, e.g. a search form for searching a website. This is a benefit even suitable for single query execution due to the features that parameter binding provides. When a statement is declared a handler to that statement is returned allowing it to be either executed or to allow parameters to be bound to the statement.
Prepared SQL statements are not limited to input parameter binding, but to output parameter binding as well. For instance, you may have a prepared statement which executes a stored procedure located in the database on the server. The output from the stored procedure can be easily bound to an output parameter. This is a variable which can be used throughout your program for checking the returned value from the stored procedure.
SQL Sub-Selects
SQL sub-selects allow the implementation of a second query within another query to accomplish a specific task which can not be achieved using a single query. Sub-selects allow you to write shorter and more efficient code within your programs whilst maintaining the functionality. Sub-selects should be used within your application when INNER JOINS do not provide the functionality required, they are also commonly used when using an aggregate function such as COUNT().
SQL Transactions
MySQL provides an interface to SQL transactions. The main principle for SQL transactions is to allow one of two different states of execution, either where a complete set of SQL queries are executed successfully, or where none of them are executed successfully. This feature allows developers to create applications where data remains consistent within a database and that no rogue records appear within a database. A common example of where SQL transactions are useful is airline booking systems. When a consumer purchases a ticket online this might involve updating data on multiple tables within a database as well as executing multiple insert queries. Looking at this scenario from a logical perspective, you either want all of the queries to execute successfully or none at all, you would not want a state in between where the data is partially updated in some tables but not in others. To ensure this is successfully done, a MySQL transaction accomplishes this task.
Multiple SQL Queries
The MySQL OOP interface allows the execution of multiple SQL queries which are prepared within a single statement. Although they can sometimes be difficult to read by other developers than those which programmed it themselves, they do offer some advantages to the performance during execution. Multiple SQL queries must be concatenated within a string by a semi-colon, this allows MySQL to identify where the queries begin and finish. By using multiple SQL query functionality you can greatly reduce the number of lines within your code, making it easier to debug your applications and increases the performance of your applications.
Securing SSL Connections
As previously available within PHP 4, PHP 5 supports SSL connections to your MySQL databases. This allows developers to add additional security within their applications, by providing encrypted connections between your PHP applications and the actual database. If any data is intercepted between the connection, where it would be sending or retrieving data, then the interceptor would be unable to determine the content within the data as it is encrypted. Enabling SSL connections within MySQL can be quite mundane, where MySQL needs to be compiled with OpenSSL support as well as creating SSL certificates for the connections and setting up users for SSL connections. As well as this you also need to set your PHP application to use a SSL MySQL connection in your code wherever a database connection takes place. Although SSL connections provides an additional feature for the developer, it can have an effect on the performance and efficiency of your applications because the data which is sent to and from the MySQL server requires encrypting and decrypting respectively. A careful analysis of whether SSL connections are required for your circumstances should be considered.
Article Discussion & Comments for PHP 5 MySQL