Viewers: in countries Watching now:
Now that PHP has true object-oriented capabilities, it's best practice to access databases using PDO (PHP Data Objects) and MySQLi. These methods produce database-neutral code that works with over a dozen systems, including MySQL, SQL Server, PostgreSQL, and SQLite. Learn how to use PDO and MySQLi to perform basic select, insert, update, and delete operations; improve security with prepared statements; and use transactions to execute multiple queries simultaneously. Author David Powers also covers advanced topics like instantiating custom objects, and compares PDO to MySQLi so you can decide which method is right for you.
Among the advantages of choosing either PDO or MySQLi is their support for prepared statments which offer important security features. A prepared statement is a template for an SQL query that incorporates value from user input. The prepared statement contains a placeholder for each value that's stored in a variable. This not only makes it easier to embed the variables in your PHP code, it also prevents SQL injection attacks, because PDO and MySQLi automatically escape quotes and other characters before executing the query.
Other advantages of using prepared statements are that they're more efficient when the same query is used more than once. And you combine the results from each column of the select query to named variables, making it easier to display the output. Both PDO and MySQLi use question marks as anonymous placeholders. In this example, the question marks represent the values for username and password, gathered from user input. You'll see later in the course how to bind the values to the placeholders.
PDO also supports the use of named placeholders. A named placeholder begins with a colon followed by an identifier, which doesn't necessarily need to be the same as the column name. In this example, both user name and password are likely to be strings. But the placeholders are not wrapped in quotes. This applies to both named placeholders and anonymous placeholders. This makes it a lot easier to build an SQL query because there's no need to worry about getting the correct combination of single and double quotes.
Placeholders can be used only for column values. They can't be used for other parts of an SQL query, such as column names or operators. This is because values that contain non-numeric characters are automatically escaped and wrapped in quotes when the SQL is executed. Column names and operators can't be in quotes. Using a prepared statement involves the following steps. You begin by passing the SQL and placeholders to the prepare method, which checks if the syntax is valid.
Next, you bind values to the placeholders, then execute the prepared statement. Optionally, you can bind the results from each column of the select query to named variables. Finally, you fetch the results. Prepared statements involve slightly more code than submitting the query directly, but placeholders make the SQL easier to read and write, and the process is more secure. When the same query needs to be used more than once within the same script, a prepared statement increases efficiency, by analyzing and optimizing the SQL only once.
The values for each place holder are sent separately and interpolated into the optimized statement. A non-prepared statement on the other hand needs to be analyzed and optimized every single time. This can slow down an application noticeably. When a query is submitted only once, the question of efficiency isn't quite so clear cut. Using a prepared statement for a single query involves two round trips to the database server. The first time to validate and optimize the SQL, the second to send the values for the place holders.
On the other hand, a non prepared statement combines optimization and execution in a single operation. This means that if you have a simple query that's executed only once, it's arguably more efficient to check user input yourself and embed the value directly into the SQL, because it involves only a single round trip to the server. But user input still needs to be sanitized before incorporation into SQL. So the possible efficiency gain on the database server needs to be balanced against the effort involved in preventing SQL injection.
If in doubt, use a prepared statement to handle user input. Particularly, when multiple values need to be embedded in a query. Unless your site experience is very high traffic, the security offered by compared statements outweighs marginal differences in efficiency.
There are currently no FAQs about Accessing Databases with Object-Oriented PHP.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.