There\u2019s a shitload of data types, really, just go look at the official documentation and bask in the glory of MySQL redundancy (j\/k).<\/p>\n\n\n\n
Very few, reassuring and simple data types. Basically text, numbers, and \u201cwhatever\u201d (blob). Again, go look at the official documentation.<\/p>\n\n\n\n
(last couple of rows are only useful if you plan to have some -little- write-concurrency, otherwise don\u2019t use them)<\/p>\n\n\n\n Very important thing to know: if you are writing code for a local-running application, SQLite connections will not \u201ctime out\u201d as there\u2019s no server to wait for your input, just a file on the disk (or memory, even!)<\/p>\n\n\n\n when you don\u2019t expect results, so for when you expect multiple<\/strong> results, or several fields in a row<\/p>\n\n\n\n when you want returned a single-value result, for example when the query is something like when you want both associative and indexed arrays,<\/p>\n\n\n\n when you only need associative arrays, and<\/p>\n\n\n\n if you want only indexed arrays.<\/p>\n\n\n\n In parallel with above:<\/p>\n\n\n\n If you don\u2019t 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 (and you\u2019re not going to need those both anyway).<\/p>\n\n\n\n this function is not binary safe though at the time of writing (hasn\u2019t been for a while from what I understand\u2026)<\/p>\n\n\n\n Just converting the PHP functions won\u2019t be sufficient for most.<\/p>\n\n\n\n Think about time functions for examples, or Several variations on the Again, go look at the official documentation, as what you see above is my own translation for my own purposes, and you will find both in official sources and in the vast world of StackOverflow a plethora of readings and interpretations.<\/p>\n","protected":false},"excerpt":{"rendered":" Data types MySQL There\u2019s a shitload of data types, really, just go look at the official documentation and bask in the glory of MySQL redundancy (j\/k). SQLite Very few, reassuring and simple data types. Basically text, numbers, and \u201cwhatever\u201d (blob). Again, go look at the official documentation. Database connection MySQL $GLOBALS[“dbcon”]=@mysqli_connect($dbhost, $dbuser, $dbpass);if (mysqli_error($GLOBALS[“dbcon”])) die(“errore … <\/p>\n$GLOBALS[\"dbcon\"]=@mysqli_connect($dbhost, $dbuser, $dbpass);<\/code>if (mysqli_error($GLOBALS[\"dbcon\"])) die(\"errore connessione db\");<\/code>@mysqli_select_db($GLOBALS[\"dbcon\"],$dbname);<\/code>if (mysqli_error($GLOBALS[\"dbcon\"])) die(\"errore connessione db\");<\/code>@mysqli_set_charset($GLOBALS[\"dbcon\"],'utf8');<\/code>if (mysqli_error($GLOBALS[\"dbcon\"])) die(\"errore connessione db\");<\/code><\/p>\n\n\n\nSQLite<\/h4>\n\n\n\n
$db = new SQLite3(dirname(__FILE__).\"\/DB\/db.sqlite\");<\/code>$db->busyTimeout(5000);<\/code>\/\/ WAL mode has better control over concurrency.<\/code>\/\/ Source: https:\/\/www.sqlite.org\/wal.html<\/code>$db->exec('PRAGMA journal_mode = wal;');<\/code>$db->exec('PRAGMA synchronous=NORMAL;');<\/code><\/p>\n\n\n\nQueries<\/h2>\n\n\n\n
MySQL<\/h4>\n\n\n\n
$results=mysqli_query($GLOBALS[\"dbcon\"],$query);<\/code><\/p>\n\n\n\nSQLite<\/h4>\n\n\n\n
$db->exec($query);<\/code><\/p>\n\n\n\nINSERT<\/code>, UPDATE<\/code> or DELETE<\/code><\/p>\n\n\n\n$results=$db->query($query);<\/code><\/p>\n\n\n\n$value=$db->singleQuery($query);<\/code><\/p>\n\n\n\nSELECT timestamp FROM records WHERE id=$number LIMIT 1 <\/code>(for this, with MySQL, you should parse the results with mysqli_fetch_array<\/code> or similar, and then select the first value with [0]<\/code>)<\/p>\n\n\n\nFetch results<\/h2>\n\n\n\n
MySQL<\/h4>\n\n\n\n
$row=mysqli_fetch_array($results);<\/code><\/p>\n\n\n\n$row=mysqli_fetch_assoc($results);<\/code><\/p>\n\n\n\n$row=mysqli_fetch_row($results);<\/code><\/p>\n\n\n\nSQLite<\/h4>\n\n\n\n
$row=$results->fetchArray();<\/code><\/p>\n\n\n\n$row=$results->fetchArray(SQLITE3_ASSOC);<\/code><\/p>\n\n\n\n$row=$results->fetchArray(SQLITE3_NUM);<\/code><\/p>\n\n\n\nSpeed considerations<\/h4>\n\n\n\n
Escaping<\/h2>\n\n\n\n
MySQL<\/h4>\n\n\n\n
mysqli_real_escape_string($GLOBALS[\"dbcon\"],$string);<\/code><\/p>\n\n\n\nSQLite<\/h4>\n\n\n\n
SQLite3::escapeString($string);<\/code><\/p>\n\n\n\nDatabase functions<\/h2>\n\n\n\n
DEFAULT<\/code> values, or NULL<\/code>ing a NOT NULL<\/code> timestamp column to have it automatically assigned to CURRENT_TIMESTAMP<\/code>, these things are not present in SQLite.<\/p>\n\n\n\nMySQL<\/h4>\n\n\n\n
DEFAULT CURRENT_TIMESTAMP<\/code><\/p>\n\n\n\nNOW()<\/code>, YEAR()<\/code>, TIMEDIFF()<\/code>, and another shitload of functions.<\/p>\n\n\n\nSQLite<\/h4>\n\n\n\n
DEFAULT (Datetime('now','localtime'))<\/code><\/p>\n\n\n\nstrftime()<\/code> functions, of which the Datetime()<\/code> above is an example.<\/p>\n\n\n\n