Cartoon Clouds
Mountains
Sunrise
Clouds
Green Clouds
None
Your MySQL Code Sucks.
An introduction to using PDO instead of that mysql_query crap
- The mysql_ library was meant only for MySQL versions earlier than 4.1. Most MySQL installations are using version 5.1. 5.5, at the time of this writing, is in preview stages.
- MySQL 4.1 was released in 2004. It's currently 2012. The mysql_ library has been obsolete for EIGHT YEARS.
- The mysql_ library isn't object-oriented. Including it in modern-day PHP produces very messy code that's impossibly hard to manage.
- The library is slow. Not going into specifics, much of the processing for the mysql_ library is done inside of PHP itself instead of being performed natively, which is a huge performance hit. Additionally, using mysql_ forces you to concatenate lots of strings to do what you need to do, which is another performance hit.
- The library is insecure. Cryptic functions like mysql_real_escape_string had to be written into it solely to help programmers write code that wasn't completely open to hackers, and even WITH that most sites are still at risk.
- You don't have access to a huge (huge!) number of really awesome MySQL 4.1+ features when using this antiquated library.
OMG Kyek, if the mysql_ commands suck so hard, why do all the PHP guides still use it?
I would LOVE to know. Some guides were written back when pre-4.1 MySQL was still in popular use, so that I can understand. But all these new guides that still use it just have no excuse. Either the author didn't know any better, didn't want to take the time to learn the better libraries, or for some reason thought that it was easier for people to learn the old stuff. All of these reasons are bad.
So what should I use?
There are a handful of alternatives to these commands, but currently the two most popular libraries are PDO ("PHP Data Objects") and MySQLi. MySQLi was first, and took over where mysql_ left off. It's a very good library, but once PDO was released, there was almost no reason to use it. You can look up all the details if you're interested, but there are three big, main reasons I say PDO is the better library to use:
- It supports a whole range of SQL servers. In addition to MySQL, it will connect to PostgreSQL, MSSQL, Oracle, DB2, and a range of other popular SQL servers -- and you use the exact same functions and objects to access the database no matter which type of server you're connecting to. That means that if you ever need to move to a different server with a different type of SQL on it (moving from Windows to Linux or vice-versa is a really common one), porting your code to work on the new platform is a SNAP.
- PDO puts MUCH more emphasis on object-oriented use than MySQLi does. Using PDO makes it a lot harder to write bad code, and that's a very good thing
. - When writing queries with PDO, you don't have to specify the "type" of your variables. With MySQLi, you do. This is a pain in the neck and puts a lot of limitations on you if you're trying to write a database wrapper. Even if you have no idea what a wrapper is and what it's for now, trust me -- you'll thank yourself in the future when you do.
Ok, I'm sold. Where do I start?
The concept of PDO is similar to the mysql_ methods that many of you are already used to. You still connect to a database, you still send queries, you still parse through the results. It's just that the code you use to do these things is a little bit different, especially since we're going to be using Objects.
If the idea of using Object Oriented stuff scares you, relax
It's very very easy to pick up, and hopefully you'll see the benefit of using these types of concepts in the rest of your code too Let's connect to a database!
To connect to a MySQL database, you do this:
// Fill in all the info we need to connect to the database. // This is the same info you need even if you're using the old mysql_ library. $host = 'localhost'; $port = 3306; // This is the default port for MySQL $database = 'myDatabase'; $username = 'myDatabaseUser'; $password = 'myDatabaseUserPassword'; // Construct the DSN, or "Data Source Name". Really, it's just a fancy name // for a string that says what type of server we're connecting to, and how // to connect to it. As long as the above is filled out, this line is all // you need :) $dsn = "mysql:host=$host;port=$port;dbname=$database"; // Connect! $db = new PDO($dsn, $username, $password);
That's it! Really no different from the mysql_connect method you might be used to. The only difference is that DSN bit, which we need since PDO has to know what type of database we're connecting to.
Your first query
So, we have a website all about fish recipes. A bunch of recipes for trout, a bunch of recipes for haddock, a bunch for tuna, and on and on and on. There's a search form on this website that lets people search by fish type and their favorite chef that made the recipe, and it hands back the first 20 recipe names it finds. What we need to do is write code that will query the database and return the results.
I'm going to assume you know some basic SQL, here -- for example, what a "SELECT" statement looks like. If you don't know that yet, go brush up on some SQL and come back
Here's our code. This is going to be crazy, but don't freak out -- I'll go through this line-by-line:
$statement = $db->prepare("SELECT recipe_name FROM recipes WHERE fish_type = ? AND chef_name = ? LIMIT 20");
$statement->execute(array($_POST['fish'], $_POST['chef']));
while ($result = $statement->fetchObject()) {
echo $result->recipe_name;
echo "<br />";
}
OMG KYEK WTF IS "PREPARE" AND "STATEMENT" AND THAT ARROW LOOKING THING LIKE -> WTF IS THAT OMG I DON'T UNDERSTAND THE WORLD IS FUCKING MELTING I TELL YOU MELTING HOW CAN YOU POSSIBLY PACK SO MUCH HATE INTO FIVE LINES OF CODE
Let's walk through it, shall we?
$statement = $db->prepare("SELECT recipe_name FROM recipes WHERE fish_type = ? AND chef_name = ? LIMIT 20");
Of our few lines of code up there, this one's the hardest to understand. So let's go through step by step. First, we're starting with "$statement =", so you know that whatever the rest of the line produces will be dropped into a variable called $statement. That part's easy
Next, we're talking to $db. With mysql_connect, once you connect to a database, the library magically "knows" about that connection in the future. Object-oriented code doesn't work that way. If you look up at our connection script above, you'll see that we're setting $db equal to a "new PDO" -- that's an "object", or an all-wrapped-up block of code that has its own variables and functions and all kinds of cool stuff inside of it. So to interact with that stuff, we need a way of telling PHP "I want to get inside of $db and use something in its package". That's what our little arrow -> is for
"$db->" says "whatever comes next is something inside of the $db object".And the "prepare" function we're calling is one of those things inside our $db object
. This is where it starts getting different from what you're used to. With PDO (and MySQLi, in fact), you "prepare" an SQL query before you use it. Not only does this allow the query to run faster, it lets you specify "blank" spots where you can fill in variables later. Notice the two question marks in that string? When we execute this prepared statement, we're going to pass in two variables that PDO will automatically fill in there -- and there is NO NEED for any escape_string or addslashes hacks! A query like this is called a Parameterized Query, and is totally, 100% safe from all SQL injection attacks.$statement->execute(array($_POST['fish'], $_POST['chef']));
In this line, we're taking the $statement object that we created in the last line, and telling that statement to run. Since we have two question marks in the prepared query, we need to supply an array of two variables in the execute() function. The first question mark is where the fish type needs to go, so we're dropping in $_POST['fish'], our form field for the fish type. Similarly, we're dropping in $_POST['chef'] to put the user's chef input in place of the second question mark of our prepared query.
With this line, the query runs
The results are saved inside of that same $statement object we're using, as you'll see in this little block of code:while ($result = $statement->fetchObject()) {
echo $result->recipe_name;
echo "<br />";
}If you're familiar with the mysql_ library, you'll recognize that first line. That $statement->fetchObject() function returns the next result in the group of results MySQL returned. When we reach the end of the list, that function returns false, so the loop ends. What we're doing here is setting $result equal to whatever $statement->fetchObject() gives us right there inside that 'while' statement, so that inside the loop, $result will be the current result we're working with.
Now the magic is almost over
All we're really doing inside the loop is echoing out $result->recipe_name, which would be just like saying $result['recipe_name'] to most of you folks who are used to the mysql_ commands. You can tell PDO to pass the results back inside of an associative array like that, but getting them as an object is a little faster. If you're feeling a little on the wild side, PDO supports a few other ways to get the results too -- like updating a variable of your choice with them automatically ;-)This is only the very tip of the iceberg!
PDO is capable of so many more things, from different ways of running parameterized queries to different ways of grabbing results to allowing you to execute the same prepared statement more than once with different variables each time. Some really, really cool stuff
This guide is meant to get you started with PDO and show you how each of the pieces works. If you're ready to learn more, all you need to do is visit PHP.net's official PDO documentation.There's one last thing you should know
I mentioned above that one of the benefits of PDO was that it can switch to different types of SQL servers. What you should know in advance is that the SQL language is a little different from server type to server type. MySQL supports different SQL commands than PostgreSQL which supports different commands than MSSQL. While PDO itself will connect to a range of databases, your queries might need to be tweaked in order to run on them.
If you're looking to write code that can run on any SQL server type without being tweaked or rewritten, you need a database wrapper library that has the ability to "translate" your SQL queries so they'll work on any of these servers. You could do a lot of research and attempt to write your own... or, you could use Hydrogen ;-). </shameless plug>
Go forth and code better!
and how simple it is to switch between servers. However one thing is puzzling me is there anything like $link->free_result (mysqli_free_result).
Help
Register Now!