The Simple PHP MySQL Library


Our Beliefs

You should also read about why MeekroDB is better than PDO.

If simple use cases take more than one line of code, your MySQL library sucks

Consider the following example of making a mysql query, checking for errors, and grabbing the first row of results:

        $result = $mysqli->query("SELECT * FROM accounts WHERE username='" . $mysqli->real_escape_string($username) . "' AND rank=" . intval($rank));
        if ($result->error) die("Error occurred: " . $result->error);
        $user = $result->fetch_assoc();

Three lines! Shocking! And three very ugly lines at that. Strings need to be escaped and integers run through intval() to make sure they're really integers. You need to check for errors after each command. You'll need some variant of these 3 lines for every MySQL query you do, and that will make your code look repetitive and ugly.

MeekroDB simplifies common use cases like the one above. The above can now be done in one line.

        $user = DB::queryOneRow("SELECT * FROM accounts WHERE username=%s AND rank=%i", $username, $rank);

Corollary #1: An object-oriented MySQL library (e.g. $db->query()) isn't a good idea for most applications, and the singleton model doesn't make it much better

If we accept that repetitive code is a bad thing, then the standard object-oriented approach to MySQL (where you have a $db that you pass around) seems like a bad idea. If you pass the $db object around, every one of your function calls now needs an extra parameter. If you make the $db global, then every one of your functions needs a global $db; as it's first line. Either way, you have to put up with a lot of needless code.

The singleton model ($db = MySQL::getDB() or similar) helps clean this up a little bit, but also forces you to write needless code. You'll need to have that getDB() call in every one of your functions before you can use the database.

MeekroDB rejects both of the above approaches, and opts for a rarely-used one: using a class that has only static functions and variables. This means that you can run queries from anywhere in your code with only a single line.

This "static class" idea is what I get the most criticism for, so it's worth some more discussion. 90% of those who email me to criticize this simply say "it isn't standard/recommended practice" and leave it at that. If that's all a critic has to offer, then the criticism sounds hollow to my ears. The "standard" is not sacred or holy, and violating it does not automatically mean that the violator is either wrong or inferior. The violating code must be evaluated on its own merits, although the standard can be used as a guideline to quickly locate common problems.

The only substantive criticism that I've received of the "static class" approach is that it makes it more troublesome to deal with multiple databases. While there is a little bit of truth to this, I think this argument is mostly wrong. First, only a very small percentage of PHP web apps will ever have to deal with more than one database. If you take a random sampling of the top 10 PHP/MySQL projects (vBulletin, WordPress, phpBB, etc) and examine their code, you'll find that they only ever work on one database at a time. The name of that database is generally set in their config.php and never altered again. Second, even if a project does rely on multiple databases, you can always use SQL's USE command (or MeekroDB's useDB()) to switch between them. This is almost certainly faster than opening up yet another system socket. In fact, the only case I can imagine where you'd actually need 2 instances of a database class is if you're handling separate transactions in them simultaneously, and for some bizarre reason can't execute them one after the other. If you're in this super-minority, MeekroDB probably isn't the right choice for you. But then, for every person who is actually in this situation, there are probably 1000 others who just want to complain about static classes because of religious purity or something.

In short, the advantages of using a static class in this case are real and compelling, while the only disadvantage is largely theoretical. My preference is clear.

Your MySQL class should make common mistakes harder to make

It is well understood in software development that wrong code should look wrong. That is to say, the formatting of your code should make mistakes jump out at you while you're skimming over a page. PHP's mysqli class violates this rule in several ways.

Corollary #1: If you ever have to call an escape() function yourself, your MySQL library sucks

If you put together queries by concatenating strings, you'll have to escape string variables and check that your ints are really ints using something like intval(). Doing this manually is just asking for problems, since you'll have to do it countless times in any PHP application. You might escape your variables correctly the first 1000 times, then forget to escape one string variable on your 1001st query, and your application would now have a security flaw.

Forgetting to escape something is an easy mistake to make, which is why so many web apps have SQL injection exploits. MeekroDB makes this impossible: since you never escape anything yourself, you can be 100% sure that your app is safe. If you screw up your query, you might get an error, but you'll never get a security flaw.

Corollary #2: Large MySQL inserts shouldn't be messy

The SQL insert format makes large inserts hard to write. The format requires you to first write a list of your column names, and then write a separate list of the values you're inserting (and don't forget to escape() them all!) If these lists are very long, it becomes difficult to keep them in sync if you need to add or remove columns. MeekroDB simplifies inserts by letting you specify all column names and values in a single associative array, so it's easy to tell what value corresponds to what column. See our insert() function docs for more.

Corollary #3: MySQL errors should be hard to ignore

By default, the mysqli class handles errors in a positively strange way: it ignores them. If you run a query that has a mistake in it, mysqli will just silently drop the query and return no results. You'll have to manually access the $result->error variable if you want to check for errors. Since this is an extra step that's not needed 99% of the time, most PHP programmers will simply skip it.

This will hide all sorts of bugs. Unless you know for a fact that a query should be returning some results, and it doesn't, you'll have no indication that anything went wrong. What's worse, if you forgot to escape a string, it won't be obvious that anything is wrong until you get hacked. Even if you happen to test the query by sending some input that contains the quote character, it'll just look like your query returned no results.

MeekroDB, on the other hand, goes out of its way to throw any errors in your face. By default, it will spit out an error message along with the query that produced it, and die. If you prefer, MeekroDB can be set to throw an exception instead. Either way, you'll have to deal with the error and not simply ignore it.

Database abstraction layers are pointless for most projects

Well over 99% of PHP projects rely on MySQL for their backend, and will never need to use any other kind of database. Most alternatives to MeekroDB are database abstraction libraries, and they'll claim that the "freedom to switch whenever you want" is a good thing. On the contrary, this cripples projects by forcing you to use only the lowest common denominator of database features. For example, MySQL supports the REPLACE INTO function, which is a bit of a cross between INSERT and UPDATE. Many other databases don't have it, so a database abstraction library can't include it!

There are other examples, but we believe that you lose more than you gain when you try to target multiple databases. By focusing exclusively on MySQL, MeekroDB can stay simple, and provide features that make your code stay simple as well.

You shouldn't have to use a framework to get a great MySQL library

PHP frameworks might be helpful for certain very large projects, but they aren't always an asset. For many small and medium-size PHP apps, they simply get in the way and make it harder to do what you want to do. Still, many of the best MySQL libraries are locked away within PHP frameworks like CodeIgniter. MeekroDB is different -- it works the same whether you're using a massive framework, or just throwing a few PHP files together.

You should also read about why MeekroDB is better than PDO.

Copyright (C) 2008-2019 :: :: LGPL v3 :: GitHub Tracker