Category Archives: howto’s

Migrating PHP code from MySQL to SQLite, the basics

This is also for self-reference.

Data types


There’s a shitload of data types, really, just go look at the official documentation and bask in the glory of MySQL redundancy (j/k).


Very few, reassuring and simple data types. Basically text, numbers, and “whatever” (blob). Again, go look at the official documentation.

Database connection


$GLOBALS["dbcon"]=@mysqli_connect($dbhost, $dbuser, $dbpass);
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");
if (mysqli_error($GLOBALS["dbcon"])) die("errore connessione db");


$db = new SQLite3(dirname(__FILE__)."/DB/db.sqlite");
// WAL mode has better control over concurrency.
// Source:
$db->exec('PRAGMA journal_mode = wal;');
$db->exec('PRAGMA synchronous=NORMAL;');

(last couple of rows are only useful if you plan to have some -little- write-concurrency, otherwise don’t use them)

Very important thing to know: if you are writing code for a local-running application, SQLite connections will not “time out” as there’s no server to wait for your input, just a file on the disk (or memory, even!)






when you don’t expect results, so for INSERT, UPDATE or DELETE


when you expect multiple results, or several fields in a row


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



when you want both associative and indexed arrays,


when you only need associative arrays, and


if you want only indexed arrays.


In parallel with above:




Speed considerations

If you don’t 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’re not going to need those both anyway).






this function is not binary safe though at the time of writing (hasn’t been for a while from what I understand…)

Database functions

Just converting the PHP functions won’t 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.



NOW(), YEAR(), TIMEDIFF(), and another shitload of functions.


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

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

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.

Backup linux & raspbian important files on dropbox, automatically

I just create a repository on GitHub:

It’s about a little tool I wrote, that I needed to have small, fresh backups made regularly of my raspberry pi’s operating system, that I could safely upload to the cloud (hence, encrypted).

I already use to make regular backups of my systems, but that creates 4GB .img files (even if sparse) that I cannot easily move around, and include all the contents of the sytem, even those files that can be restored with a new installation.

Instead, I needed to systematically backup my home folder, crontabs, apache files, mysql databases (maybe you have something else in mind), zip everything, and put it on dropbox.

This is the reason I wrote, which I think someone else could find useful.

Apache on Raspbian using older version of PHP

A few weeks ago I used this tutorial to install PHP 7.3 on my Raspbian.

Fast forward to today, I was bashing my head because after 2 hours of messing around, I could have exec() work from PHP CLI but not from the browser.

php -v from CLI correctly reported 7.3, so what gives?

After two hours I also decided to try phpinfo() from the browser, and guess what, it was still using 7.0!

So, after:

sudo a2dismod php7.0
sudo service apache2 restart

phpinfo() correctly reported 7.3 and I had my exec() working as intended.

Protect empty mountpoint from write access when drive is not mounted in Raspbian and Linux

I knew I had read something to this effect in the past since one of my raspi‘s had this thing where you couldn’t write to a mountpoint unless the drive/USB disk was actually mounted.

Since I spent some time in finding it again, here I add it to my blog as personal reference.

From this post:

Always set the attributes of mountpoint directories to immutable using chattr.

This is accomplished with chattr +i /mountpoint (with the mount unmounted).

This would error-out on new write activity and also protects the mount point in other situations.

lighttpd cannot install because of libssl1.1, how to fix

My situation happened on a Raspberry with Raspbian Stretch installed, I had added PHP7.0 repository from quite a while back and when by chance today I went to open PiHole admin page from my laptop it simply wouldn’t load.

Google what’s up, I found out lighttpd was maybe the culprit, look around and noticed it wasn’t installed, and couldn’t be installed with the error:

lighttpd : Depends: libssl1.1 (>= 1.1.0) but it is not going to be installed

I then discovered that said libssl1.1 library off, coming together with the PHP distro, broke lighttpd.

I removed the repository source from /etc/apt/sources.list.d/php.list (your filename may vary) and issued a sudo apt-get update followed by a dist-upgrade but that didn’t solve anything.

I couldn’t find for the life of me a way to simply exchange the libssl1.1 package (which was kept from the repository even after removing its source) to the original repository version, and

sudo apt-get remove libsll1.1

wanted me to remove also ALL the packages that depended on this library. And they were a LOT.

Like hell I wanted to do that!

Or not?

Well I tried

sudo apt-get install --reinstall libssl1.1

hoping it would get rid of the messy libssl1.1 exchanging it with the official repository one, but that also gave an error:

Reinstallation of libssl1.1 is not possible, it cannot be downloaded.

Sooooo I was getting out of options and very annoyed.

I thought, why not letting the frigging apt remove all those packages? I can always install them later on… I hope…

EDIT: benosco down in the comments offered an easier solution, you might want to check it before going the hardcore way like I did.

So I copied the list of the packages that were to be removed off putty window and into notepad++, crossed my fingers and let apt remove everything.

After that was done during an extremely long and suffered delay, I had to do

sudo apt-get install <paste big-ass list of packages here>

In my case, it didn’t work out that simply at the first try, I got some CRC mismatch somewhere so they were not installed all together.

But I issued the command with less packages listed, beginning with apache2 php7.0 mysql-server transmission-cli samba python somethingIcannotreallyrecallnow, and then reissued the first command with all the list again to fill in the blanks.

It installed everything piece by piece, and lighttpd with it as well.

Since apt-get actually removes those packages without purging them (unless you ask it to purge libssl1.1 which isn’t a good idea) their settings are kept and reinstalling the packages will restore the system to the previous state.

At least it did for me, since the system came back to normal after a reboot.

Cura bug sets nozzle temperature to 0 instead of leaving control to printer

Straight to the chase: if you set the nozzle temperature to zero degrees in Cura, and the printer instead of immediately starting the print at the temperature YOU set on it, it sets the nozzle temperature to 0, then there is a workaround which is not straightforward at all.

As per this forum post:

  • Inside Cura, open Help menu and click on Show configuration folder
  • close Cura
  • open subfolder “definition_changes” inside the configuration folder explorer window that just popped up
  • open your printer’s *_settings.inst.cfg in notepad
  • identify [values] section and add
    machine_nozzle_temp_enabled = False
  • start Cura after having saved the modified file

Thank you Ultimaker for making this very intuitive for your users.

But seriously, thank you for the nice free software.

Lettura della fattura elettronica XML con PHP

Visualizzare e formattare in HTML una fattura elettronica con del codice PHP concettualmente è semplice, ma scrivere un apposito programma che fa un parsing del codice XML, e quindi disegnare un layout HTML in cui inserire i dati ottenuti, è uno spreco di tempo, se lo si fa solo per “guardare” la fattura senza doverla ulteriormente processare in modo automatizzato.

Per questo ringraziamo AssoSoftware che fornisce liberamente il suo foglio di stile XSL (in realtà ne esiste anche uno ufficiale rilasciato da Sogei, ma… vabè non devo aggiungere niente su Sogei e quindi se volete provatelo, e mi saprete dire nei commenti quale vi piace di più), il quale applicato all’XML originale seguendo questa guida restituisce una gradevole formattazione tabellare completa di annotazioni.

D’accordo lo ammetto questo articolo dai risultati di ricerca sembra essere fuorviante, perché io stesso cercando una routine che leggesse l’XML per restituire delle variabili in PHP, ho trovato il mio articolo pensando “vuoi vedere che l’ho già scritta io questa routine e me ne sono dimenticato?” ed invece no… quindi non essendoci altre risorse realmente utilizzabili per il PHP, credo proprio che ora mi ci metterò io a scrivere la routine…

Ho rimediato e l’ho appena scritta.

Questo è uno snippet del mio reale codice, per chi bazzica di PHP è comprensibile. E’ incompleto siccome mi serviva solo a recepire i dati che vanno inseriti nel registro acquisti, ma la logica dietro è abbastanza chiara per poterlo estendere a vostro piacimento.

$xml = simplexml_load_string($source);

$supplier = $xml->FatturaElettronicaHeader->CedentePrestatore->DatiAnagrafici->Anagrafica->Denominazione;
if (!$supplier) {
	$supplier = $xml->FatturaElettronicaHeader->CedentePrestatore->DatiAnagrafici->Anagrafica->Nome . " " . $xml->FatturaElettronicaHeader->CedentePrestatore->DatiAnagrafici->Anagrafica->Cognome;
$supplierpiva = $xml->FatturaElettronicaHeader->CedentePrestatore->DatiAnagrafici->IdFiscaleIVA->IdCodice;
$suppliervia = $xml->FatturaElettronicaHeader->CedentePrestatore->Sede->Indirizzo;
$suppliercivico = $xml->FatturaElettronicaHeader->CedentePrestatore->Sede->NumeroCivico;
$suppliercap = $xml->FatturaElettronicaHeader->CedentePrestatore->Sede->CAP;
$suppliercomune = $xml->FatturaElettronicaHeader->CedentePrestatore->Sede->Comune;
$supplierprovincia = $xml->FatturaElettronicaHeader->CedentePrestatore->Sede->Provincia;

$invoicedate = $xml->FatturaElettronicaBody->DatiGenerali->DatiGeneraliDocumento->Data;
$invoiceno = $xml->FatturaElettronicaBody->DatiGenerali->DatiGeneraliDocumento->Numero;
$invoicewhat = $xml->FatturaElettronicaBody->DatiGenerali->DatiGeneraliDocumento->Causale[0];
if (!$invoicewhat) $invoicewhat=$xml->FatturaElettronicaBody->DatiBeniServizi->DettaglioLinee[0]->Descrizione;

foreach ($xml->FatturaElettronicaBody->DatiBeniServizi->DatiRiepilogo as $riepilogo) {
	switch ($riepilogo->AliquotaIVA) {
		case "22.00":
			$imponibileord = $riepilogo->ImponibileImporto;
			$impostaord = $riepilogo->Imposta;
		case "10.00":
			$imponibilerid = $riepilogo->ImponibileImporto;
			$impostarid = $riepilogo->Imposta;
		case "4.00":
			$imponibilemin = $riepilogo->ImponibileImporto;
			$impostamin = $riepilogo->Imposta;
		case "0.00":
			$esente = $riepilogo->ImponibileImporto;

Hide disable exclude specific mail account in thunderbird global inbox

I use Thunderbird’s unified inbox as it is a very convenient way to manage multiple accounts. Yet there was a management account where the inbox was a huge pile of messages that needed to stay there but that I didn’t want to show in there unless I specifically clicked on that account.

No other guide on the first page of google results gave info on it, but here’s how you do it.

Right-click on the Inbox master node under which all the accounts’ inboxes are listed, then click on Properties, then Choose the selected folders to search, and un-check the Inbox of the account you want to hide. Then click on Update.

There you go.

Cannot downgrade Xiaomi Router 3 firmware to development version

I bought a Mi Router 3 for the express purpose of installing Padavan firmware on it, but lo and behold, I could not for the life of me install the BIN file for the 2.11.20 development version of the firmware (see here and here) as, when choosing the BIN file from the browse file dialog of the router web interface, the popup just went numb, no action, no buttons, no upload activity. Tried many times it with Firefox Chrome and even Internet Explorer AND Edge, NOTHING!

Then, I found in a forum (can’t remember for the life of me where) that there is an alternative method do to it:

  1. Put the BIN file in an empty USB pendrive
  2. Turn off the router (unplug the power)
  3. Place the USB stick in the router
  4. Press the reset button on the back of the router, and while keeping it pressed plug in the power, and STILL keep the reset switch pressed
  5. Stay there like this for a while contemplating on the meaning of existence
  6. In my case I noticed that after a while (10-15 secconds maybe?) the front led was flashing in a different way than when it just booted up, so I figured I could finally leave the reset switch be
  7. I connected to after a while when the front led became static blue and, go figure, the firmware had downgraded

lftp and source: Is a directory error

I was running lftp with the -f switch to launch the usual mirror command, working fine with a few ftp servers at first, but when I added another sync with a pure-ftpd server something stopped working as intended: the message

source: Is a directory

appeared and nothing more happened, only after I pressed Enter the script went on with the execution.

What helped me was this discussion.

so I changed my script, and instead of having

open host
user username password
mirror blahblah

I used

open -u username,password host
mirror blahblah

and everything was magically fine!

Why? NO IDEA. Who cares. Works now.