Sqlite vs MYSQL

 

Sqlite or mySQL

Sqlite and MYSQL are two excellent options for your next database. Although the two do more or less the same thing such as select, update, insert and delete data from a database, they each have commands that are unique and portability differences. I will get to the command details later.

For now, I would like to focus on portability because it makes a huge difference. In fact, the portability makes an Sqlite database as a great option for both Android apps and web applications.

Android App

For example, you could create an app for Google Play that uses an Sqlite database. This means, the client can download your app and use the database on their phone. Although a mySQL database can connect to a host from a mobile web page, using an Sqlite database is obviously the way to go for an Android app.

Web Applications

If you want to create a web application that will deal with many users and plenty of traffic, mySQL would likely be an option you would consider. But, if you want to make a micro application that can be easily downloaded and used quickly by the client, an Sqlite database makes this possible.

Usage

This section will detail the usage for using Sqlite and mySQL. It will focus on writing code that is compatible for both databases and expanding on some slight differences. From a PHP scripting point of view, you can easily write the same queries for both, as long as you use PDO.

The main differences in usage will be at the command line. Although the select, update, insert and delete statements will be the same, the other commands like using a database and showing tables will be different.

Below, are examples of statements that could be used in a PHP script that connects to a mySQL or Sqlite database.

Connect to Database

   function connect()
{
    global $dbh;
    $user = 'root';
    $pass = "password";

    // MYSQL CONNECT
    //$dbh = new PDO('mysql:host=localhost;dbname=mydatabase', $user, $pass);

    // SQLITE CONNECT
    $dbh = new PDO('sqlite:db/mydatabase.sqlite');
    $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    return $dbh;
}


Select

   $command = $dbh->prepare("SELECT * FROM tablename");
    $command->execute();
    $result = $command->fetchAll();
    
	foreach ($result as $row) {
	$my_array[] = $row;
	}

Update

 $command = "UPDATE tablename SET var1=:var1, var2=:var2, var3=:var3 WHERE id=:id";
    $command2 = $dbh->prepare($command);
    $command2->bindParam(':var1', $var1);
    $command2->bindParam(':var2', $var2);
    $command2->bindParam(':var3', $var3);
    $command2->bindParam(':id', $id);
    $command2->execute();


Insert

    $command = "INSERT INTO tablename VALUES (NULL,:id, now())";
    $command1 = $dbh->prepare($command);
    $command1->bindParam(':id', $id);
    $command1->execute();