That script uses unsalted MD5 hashes and the soon-to-be-deprecated ext/mysql PHP extension.
MD5 is a broken hash, and should no longer be used for any cryptographic purposes, and using any hash without salting leaves you vulnerable to rainbow table attacks. For reasons discussed in this thread, use of a deliberately slow method for generating your stored hash is much preferable. If there's one take-away to keep in mind, it's that it's not just your site you need to protect. The chances are pretty good that your average naive user is also using the same password on other sites, and it really is up to us to protect our users from themselves.
Stop using mysql_* -- not only does it leave you open to SQLI attacks, it will start generating errors in a couple PHP dot versions down the line (deprecation errors are slated for PHP 5.5) and will be removed from the language at some point. Use PDO (PHP Data Objects) and prepared statements instead. They've been around for years, there's no longer any excuse not to use them.
By the way, callumacrae's linked script (hosted on github) in the thread referenced above is a good one -- despite the razzing I gave him for plain-text emailing of passwords (something customers often want even though it represents a distinct vector for attack).
Due to the increasing demand for the popular "login PHP/PDO script" (have you ever noticed that once in a while, a question about the script pops up?), so my idea (if it's not TOO MUCH to ask) is that if you, "mission", "callumacrae", or anybody else (whoever's an expert in this, and also, who can find the time) can write (may be not individually, but a joined effort - however you all decide to do it) a working, simple step-by-step tutorial, and put it in the Sticky Threads section of the Tutorials forum (this will also help in reducing the number of questions on the subject later on).
I know that I, for one, for SURE will be benefiting A LOT from such a script.
P.S.: If you all decide to go for it, PLEASE keep the codes' explanations SIMPLE, short, right to the point as many of us are not as knowledgeable on the subject as you do (think of it as writing for DUMMIES, myself included).