Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
MySQL is by far the most popular database management system for small- to medium-sized web projects. In this course, Bill Weinman provides clear, concise tutorials that guide you through creating and maintaining a MySQL database of your own. Bill explores the basic syntax, using SQL statements to create, insert, update, and delete data from your tables. He also covers creating a new database from scratch, as well as data types, transactions, subselects, views, and stored routines. Plus, learn about the multi-platform PHP PDO interface that will help you connect your database to web applications.
Like most database APIs the PDO interface is built on top of the standard MySQL C interface which provides a facility for prepared statements. Prepared statements allow the database to analyze and compile an SQL statement and then execute it over and over again. To get the results one row at a time. This is actually a lot more efficient than it sounds. The alternative would be to grab all of the rows of a result at once which may sound like a good idea, but it rarely actually is a good idea.
You never know how many rows there'll be or how much memory and compute resources will be required to process them. Getting one row at a time is very manageable. And with prepared statements, it's actually quite efficient. Prepared statements also provide for statement parameters. Statement parameters provide significant security benefits, especially against SQL injection attacks. Here in this get_albums SQL function, you notice that the query is very simple. It's just a select everything.
From album, order by title. And so here on line three, 637 this prepare call prepares the statement and it compiles it in the server. And then there's a fetch statement later on which can fetch row by row each of the results from that prepared statement. Down here in the get_tracks_sql, this statement is a little bit more complicated. Here we have different statements for different database engines because the SQL differs between them.
This is the MySQL version. There's the SQL Lite version, there's the Postgres version. And you'll notice that each of these has in it somewhere a WHERE album_id equals question mark. And this question mark is actually a parameter. What happens is, when we get down here and we have the query here in the query variable. Prepare is called and then execute is called, and execute is called with an array of parameters, and so the album id is actually passed as a parameter.
What this means is the statement is already prepared and it's already on the server, and then the parameters are passed separately, effectively in a separate channel. They never actually combine the text of the parameter with the text of the query. Those strings are never in the same place at the same time. They're never combined and this makes it virtually impossible for a SQL injection attack to occur. And it also provides other efficiencies, in that the engine is given the opportunity to optimize the execution path for the query.
Based on what the parameters actually ar, and sometimes adjust that optimization as the query is being executed. If we go over here to the display_tracks function, we see down here in this while loop that rows are fetched one at a time. And then they're passed off to a track results row which builds the HTML of the results row along with all of the form elements and everything like that. And it's passed this associative array, this flag here, PDO::FETCH ASSOC.
That tells the PDO interface to take all of the results and put them in an associative array, which is very convenient for things like building form elements. And then once all of that is built and put together, you notice that there's little bits of HTML scattered throughout here. All of that is passed to a content call, which builds the whole page bit by bit. But what we're concerned with here is this fetch call because this, with the prepared statement and the parameters already passed off to the server.
That can just grab the results row by row and it's all very efficient to do it that way. So MySQL provides a rich and mature interface for preparing and executing SQL safely and efficiently and the PDO library implements it effectively.
There are currently no FAQs about MySQL Essential Training.
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.