Migrating php code from MySQL to SQLite, the basics.
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