It seems the forums here at Wgg don’t get a lot of screen-time, so I’ve decided to expand on a long lost forum topic to compare two alternatives to regular old MySQL database accessing in PHP. I will be looking at MySQLi, where the ‘i’ stands for improved, and SQLite, where the ‘ite’ stands for light.

MySQLi and SQLite are different in 2 major ways. MySQLi is just a new and improved way of accessing databases that might as well have been created and populated with regular MySQL. This means you can stick with your good friend the PHPMyAdmin for easy table creation, modification and overview. This also means that if you move all your files to a different server, you need to recreate your tables and repopulate them, meaning that MySQLi is not portable in that sense. This is where SQLite comes in. SQLite is very slim, and should only be used for very small databases. It’s nowhere near as fast as its two bigger brothers, but it does have one perk. In SQLite, you create a .db file somewhere in your directory among your .php files, which means you it is just as portable as the .php files themselves. You also won’t need any other database engines, because PHP has SQLite built into it since version 5 (at least).

Now away from the theory and into the code bits. I’ve hinted in the title that I would do this objectively, by which I mean that I will orient some objects. First of all, the database connection:

In MySQLi:
function connect() {
     $host = "thehostname";
     $user = "theusername";
     $pass = "yoursupersecurepassword";
     $data = "thenameofthedatabase";
     $mysqli = new mysqli($host, $user, $pass, $data);
     if(mysqli_connect_errno()) die("Database connection failure.");
     return $mysqli;
}

In SQLite:
function connect() {
     $database = new SQLiteDatabase("pathtothe/database.db") or die("Failed to make/connect to database. ");
     return $database;
}

You see that each of these methods is black-boxy. I put one of these functions in a file that I include at the top of every page, and whenever I need a connection it’s as simple as:
$db = connect();
Where I now have my connection in $db. After I’m done, I’ll close the connection like this:
unset($db);

Now let’s get the information out of our databases. In this example I’ll use articles each with a title, entry date, and body.

In MySQLi:
$db = connect();
$result = $db->query("SELECT * FROM articles ORDER BY entry_date DESC");
// get all articles, most recent first
if($result->num_rows) { // proceed if there are articles *notice no ‘()’*
     while($article = $result->fetch_object()) { // make an article object of each article
          echo '<h1>'.$article->title.'</h1>';
          echo '<i>'.$article->entry_date.'</i><br />';
          echo nl2br($article->body);
     }
}
unset($db, $result);

In SQLite:
$db = connect();
$result = $db->query("SELECT * FROM articles ORDER BY entry_date DESC");
if($result->numRows()) {
     while($article = $result->fetchObject()) {
          echo '<h1>'.$article->title.'</h1>';
          echo '<i>'.$article->entry_date.'</i><br />';
          echo nl2br($article->body);
     }
}
unset($db, $result);

You see the code is very similar, but you’ll start noticing performance differences when you try pulling 500+ articles out at once. I would like to specifically point out the syntax difference between the MySQLi and SQLite. The first uses underscores, and the second uses camelCaps. In the beginning this got me quite confused, because I was learning both at the same time, but now I like the difference because it helps me keep them apart.

There are actually many more powers in MySQLi, such as batch queries, but I haven’t really mastered that myself enough to be able to teach it. I hope that this was helpful, and that you’ll finally step away from the regular MySQL now that you’ve seen the light.

Remember: send a man to the barber, and he’ll get a shave. Give a man a razor, and he’ll shave his pubic hair…

When not writing for WebGuruGuide, I occasionally contribute to RalphvandenBerg.com or ficklegods.com.