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.
PHP treats MySQLi select queries as either buffered or unbuffered. Understanding this important concept will help you avoid errors when working with MySQLi. So what's the difference? With a buffered query, the result set is transferred immediately from the database server, and it's stored in PHP memory. By contrast, the results of an unbuffered query remain on the database server. And they're not fetched until required. Both have their advantages and disadvantages.
Let's start with the advantages of a buffered query. Because it's in the PHP memory, you can get the number of rows in the result set, from the num_rows property. The result's set internal pointer, can be moved using the data_seek method, allowing you to reuse the result set. And storing the results in PHP's memory, means that further queries can be issued on the same database connection. The main disadvantage, is that the larger the number of results, the more memory is consumed. Not surprisingly, the pros and cons of an unbuffered query are the opposite.
The main advantage is leaving the results set on the database server until its needed, consumes less PHP memory. The disadvantages, are that you can't find out how many rows are in the results set. Nor can you use data seek, to move the pointer, or reuse the results. An unbuffered query, blocks further queries from being executed on the same connection. You have to wait until the end of the result set, or truncate it. It also increases the load on the database server. So how do you know which type of query is being executed? With non prepared statements, by default, the query method immediately returns a result set.
In other words, it's buffered. Although you can alter this default behavior, the more normal way to execute an unbuffered query, is to use the real_query method. This leaves the result set on the database server. If you previously used the original PHP MySQL extension, real query is the equivalent of the old MySQL unbuffered query function. Working with real query is a two stage operation. To retrieve the results, you need to call the use_result or store_result method.
However, store_result, converts it from an unbuffered query, to a buffered one. So in practice, use_result is the one you need. With a prepared statement, the default state after calling the execute method, is an unbuffered result set. That means you can't immediately use the num_rows property or data_seek method. Nor can you execute other queries if any results are still unused. However, all you need to do is call the store_result method on the statement object.
This buffers the result, allowing further queries to be executed and giving access to num_rows and data_seek. The alternative is to use the_get result method, to assign the result set to a MySQLI result object. This is, slightly less portable than store result. Because it uses the MySQL native driver, My SQL ND. This became available in PHP 5.3, but it didn't become the default driver until PHP 5.4.
All this can appear rather confusing. So, let's cut through the complexity, with a few simple guidelines. Buffered queries are easier to handle. So, I suggest you use to them in preference to unbuffered ones, most if not all of the time. This means using the query method, for non-prepared statements. With prepared statements, the decision isn't so clear cut. If the result is going to be consumed immediately, there's no need to buffer it. But if you need to access num_rows, data_seek, or further queries, use store result to convert it to a buffered query. Use unbuffered queries for very large result sets. And release resources, as soon as they're no longer needed, such as by closing a prepared statement as we did in the previous chapter. We'll look at other ways to free resources, later in this chapter.
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.