Migrating php code from MySQL to SQLite, the basics.
MySQL is free and open-source software under the terms of the GNU General Public License, and is also available under a variety of proprietary licenses. MySQL has stand-alone clients that allow users to interact directly with a MySQL database using SQL, but more often MySQL is used with other programs to implement applications that need relational database capability.

SQLite is ACID-compliant and implements most of the SQL standard, generally following PostgreSQL syntax. SQLite is a popular choice as embedded database software for local/client storage in application software such as web browsers. It is arguably the most widely deployed database engine, as it is used today by several widespread browsers, operating systems, and embedded systems (such as mobile phones), among others.

Database connection

MySQL

$GLOBALS["dbcon"]=@mysqli_connect($dbhost, $dbuser, $dbpass);
if (mysqli_error($GLOBALS["dbcon"])) die("Error");
@mysqli_select_db($GLOBALS["dbcon"],$dbname);
if (mysqli_error($GLOBALS["dbcon"])) die("Error");
@mysqli_set_charset($GLOBALS["dbcon"],'utf8');
if (mysqli_error($GLOBALS["dbcon"])) die("Error");

SQLite

$db = new SQLite3(dirname(__FILE__)."/DB/db.sqlite");
$db->busyTimeout(5000);

Very important thing to know: if you are writing code for a local-running application, SQLite connections will not time out as theres no server to wait for your input, just a file on the disk.

Queries

MySQL

$results=mysqli_query($GLOBALS["dbcon"],$query);

SQLite

$db->exec($query);
When you dont expect results, using INSERT, UPDATE or DELETE.
$results=$db->query($query);
When you expect multiple results, or several fields in a row.
$value=$db->singleQuery($query);
When you want returned a single-value result, for example when the query is something like SELECT timestamp FROM records WHERE id=$number LIMIT 1 (for this, with MySQL, you should parse the results with mysqli_fetch_array or similar, and then select the first value with [0])
Fetch results
MySQL
$row=mysqli_fetch_array($results);
When you want both associative and indexed arrays
$row=mysqli_fetch_assoc($results);
When you only need associative arrays
$row=mysqli_fetch_row($results);
If you want only indexed arrays

SQLite

$row=$results->fetchArray();
When you want both associative and indexed arrays
$row=$results->fetchArray(SQLITE3_ASSOC);
When you only need associative arrays
$row=$results->fetchArray(SQLITE3_NUM);
If you want only indexed arrays

If you dont need associative arrays, you should always go for indexed arrays, since both in MySQL and SQLite they are fetched significantly faster, also, even if by very little, fetching only associative arrays is still faster then having both associative and indexed fetched together.

Escaping
MySQL
mysqli_real_escape_string($GLOBALS["dbcon"],$string);

SQLite

SQLite3::escapeString($string);
Database functions
Just converting the PHP functions wont be sufficient for most.

Think about time functions for examples, or DEFAULT values, or NULLing a NOT NULL timestamp column to have it automatically assigned to CURRENT_TIMESTAMP, these things are not present in SQLite.

MySQL

DEFAULT CURRENT_TIMESTAMP

SQLite

DEFAULT (Datetime('now','localtime'))

Several variations on the strftime() functions, of which the Datetime() above is an example.

We use cookies to personalize and enhance your experience on our site. By using our site, you agree to our use of cookies.
  More information about cookies