Jump to content

Photo

Your MySQL Code Sucks.


  • Please log in to reply
146 replies to this topic

#1
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

*
POPULAR

So from the sounds of most threads in the PHP forum here, all folks know how to use is the mysql_connect family of functions when it comes to talking to a MySQL server. And that sucks. Here's why:

  • 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 <img src='http://webdevrefinery.com/forums/public/style_emoticons/<#EMO_DIR#>/smile.gif' class='bbc_emoticon' alt=':)' />
$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!

#2
Comkid

Comkid
  • Members
  • 87 posts
  • Joined: 09-March 10
  • LocationWarez-BB
  • Expertise:PHP
Nice tutorial :D
Ist php freelance coder :P

Comkid is trying to learn Javascript, AJAX and jQuery :D
- After two years, Comkid is not trying to learn anymore :(

#3
GreySyntax

GreySyntax
  • Members
  • 102 posts
  • Joined: 08-March 10
  • Expertise:HTML, CSS, PHP, Java, Javascript, Ruby on Rails, Node.js, SQL
I've been using MySQLi for a while and im currently working on a PDO wrapper for my framework. What im liking so far is the way prepared statements are handled :) 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).

#4
JackHarley

JackHarley

    Jack

  • Members
  • 1275 posts
  • Joined: 08-March 10
  • LocationIreland
  • Expertise:HTML, CSS, PHP, Javascript, SQL
Great job Kyek :)
I think we need someone to write an entire guide like w3schools and tizag that teaches start to finish and give that to newbies. Best to get new people started on a good track.

#5
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

Great job Kyek :)
I think we need someone to write an entire guide like w3schools and tizag that teaches start to finish and give that to newbies. Best to get new people started on a good track.

I'll be starting one soon :) http://www.webdevref...-out-tutorials/

I've been using MySQLi for a while and im currently working on a PDO wrapper for my framework. What im liking so far is the way prepared statements are handled :) 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).

PDO handles its data traditionally, which means that if you want to free up the memory being used by $statement immediately, you can just call unset($statement). Remember, though, that all that memory gets freed up naturally after the request ends and the output is sent back to the user. You generally only need to use that when you're working with massive amounts of data within one request.

#6
IcyTexx

IcyTexx
  • Members
  • 355 posts
  • Joined: 08-March 10
  • LocationCroatia
  • Expertise:HTML, CSS, PHP, Python, SQL
Yay Kyek, thanks... I'm switching to PDO.

#7
GreySyntax

GreySyntax
  • Members
  • 102 posts
  • Joined: 08-March 10
  • Expertise:HTML, CSS, PHP, Java, Javascript, Ruby on Rails, Node.js, SQL

I'll be starting one soon :) http://www.webdevref...-out-tutorials/


PDO handles its data traditionally, which means that if you want to free up the memory being used by $statement immediately, you can just call unset($statement). Remember, though, that all that memory gets freed up naturally after the request ends and the output is sent back to the user. You generally only need to use that when you're working with massive amounts of data within one request.

Brilliant thanks for the information :)

#8
Chris

Chris
  • Members
  • 106 posts
  • Joined: 08-March 10
  • LocationSouth Yorkshire
Ooh this is interesting. I've never heard of it but it seems like a way better thing to use. I think I'll probably start using this from now on xD

I just have one question :P

while ($result = $statement->fetchObject()) { 
   echo $result->recipe_name;
    echo "<br />";}


What if I only want one result from the query? How would I get that?

Would it be like:

$result = $statement->fetchObject()->recipe_name;

or like:

$result = $statement->fetchObject(recipe_name);

or would you have to do the full:

$result = $statement->fetchObject()
$recipe_name = $result->recipe_name;



Just curious how it would work? xD

Thanks, Chris

#9
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
@Chris: Your first and last solutions both work :) According to PHP.net's documentation for PDO, the only arguments you can pass to fetchObject are a class name for it to use (instead of PHP's standard Object) and arguments to pass to that class's constructor. So your second version would have some very unexpected results :)

Edit: Arg, now I'm second-guessing myself. I THINK this will work:
$result = $statement->fetchObject()->recipe_name;
But if not, all you'd have to do is add an extra set of parentheses like this:
$result = ($statement->fetchObject())->recipe_name;


#10
Chris

Chris
  • Members
  • 106 posts
  • Joined: 08-March 10
  • LocationSouth Yorkshire
Ahhh thanks, that clears it up (: I'll have to do some testing :D

Thanks, Chris

#11
Mack

Mack
  • Members
  • 2091 posts
  • Joined: 08-March 10

@Chris: Your first and last solutions both work :) According to PHP.net's documentation for PDO, the only arguments you can pass to fetchObject are a class name for it to use (instead of PHP's standard Object) and arguments to pass to that class's constructor. So your second version would have some very unexpected results :)

Edit: Arg, now I'm second-guessing myself. I THINK this will work:

$result = $statement->fetchObject()->recipe_name;
But if not, all you'd have to do is add an extra set of parentheses like this:
$result = ($statement->fetchObject())->recipe_name;


Not 100% sure, but wouldn't the second not work at all because of a forgotten ;?

Also, how would I install PDO using apt-get for PHP 5.3 and nginx?

And apt-get install php5-pdo didn't work :(

#12
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

Not 100% sure, but wouldn't the second not work at all because of a forgotten ;?

I.. you lost me. The second one has a ; right at the end :)

Also, how would I install PDO using apt-get for PHP 5.3 and nginx?

And apt-get install php5-pdo didn't work :(

PDO is a built-in module of PHP 5. All you need to use it is the mysql driver, which you get if you do "apt-get install php5-mysql" (Assuming you're on debian or a debian derivative like ubuntu). That's why it's so sweet -- every server already has exactly what you need to use it. No custom config required :)

#13
DarkCoder

DarkCoder
  • Members
  • 1463 posts
  • Joined: 08-March 10
  • LocationEngland, United Kingdom
  • Expertise:HTML, CSS, PHP, Javascript, SQL
This is a good tutorial, thanks! I now have now excuse not to use PDO in my future code releases! ;)


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.


I lol'ed when I read this... :D


EDIT: In progress?

#14
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

EDIT: In progress?

Yep. I wanted to wait for people's comments to make sure nothing I wrote was too confusing :) I'll promote this and a few other threads to official in a couple days.

#15
Mack

Mack
  • Members
  • 2091 posts
  • Joined: 08-March 10

I.. you lost me. The second one has a ; right at the end :)


PDO is a built-in module of PHP 5. All you need to use it is the mysql driver, which you get if you do "apt-get install php5-mysql" (Assuming you're on debian or a debian derivative like ubuntu). That's why it's so sweet -- every server already has exactly what you need to use it. No custom config required :)


yup I'm on Debian :D

Also, in this line, wouldn't you need a semicolon after the first line?
$result = $statement->fetchObject()
$recipe_name = $result->recipe_name


#16
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL

Also, in this line, wouldn't you need a semicolon after the first line?

$result = $statement->fetchObject()
$recipe_name = $result->recipe_name

ohohoh I gotcha, I thought you were talking about MY second line in the section you quoted xD

#17
T0-R3ST

T0-R3ST
  • Members
  • 8 posts
  • Joined: 17-March 10
thank you very much , I am starting working :)

#18
Mack

Mack
  • Members
  • 2091 posts
  • Joined: 08-March 10
If something you wrote, and completed, is still in this section, what IS an official tutorial?

#19
Kyek

Kyek

    Founder of wdR

  • Administrators
  • 5414 posts
  • Joined: 20-February 10
  • LocationPhiladelphia, PA, USA
  • Expertise:HTML, CSS, PHP, Java, Javascript, Node.js, SQL
Like I said -- I just wanted to give this tutorial some time in here so people could point out what confused them, what (if anything) more they wished I'd included, etc. I think a finished tutorial should spend a good week in here for user feedback before becoming official. Remember, official tutorials will be what's included in the main, public, non-forum, wdR tutorials pages later down the line (assuming the site stays fairly popular). So I want everything there to be tried and tested and top-notch when it gets moved in :)

#20
Mack

Mack
  • Members
  • 2091 posts
  • Joined: 08-March 10

Like I said -- I just wanted to give this tutorial some time in here so people could point out what confused them, what (if anything) more they wished I'd included, etc. I think a finished tutorial should spend a good week in here for user feedback before becoming official. Remember, official tutorials will be what's included in the main, public, non-forum, wdR tutorials pages later down the line (assuming the site stays fairly popular). So I want everything there to be tried and tested and top-notch when it gets moved in :)


Makes sense. Will tutorials in the Official Tutorials section be closed, or still allow comments?




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users