In this post I will introduce you to phpLiteAdmin which is a web-based user interface written in PHP that makes it easy to interact with SQLite databases.
Basic Raspberry Pi setup
Before you continue reading this project, make sure you have Raspbian Operating System installed in your Raspberry Pi.
You also need to have SQLite installed in your Raspberry Pi.
Installing Apache2 and PHP
You need to install Apache2 in your Raspberry, Apache is used to serve your web pages. Enter the next command:
sudo apt-get install apache2 -y
You also need to install PHP which is an open source web scripting language that is used to build dynamic web applications. To install PHP and its modules, type in this command:
sudo apt-get install php5 libapache2-mod-php5 php5-sqlite -y
Move to your /var/www/html directory and edit the dir.conf file:
sudo nano /etc/apache2/mods-enabled/dir.conf
Make the next change to the file. Before: index.html index.php
After: index.php index.html
Ctrl+X, type Y and press Enter to save your changes. Restart Apache2 service for the changes to take effect:
sudo service apache2 restart
Testing your installation
Go to your Raspberry Pi IP address (use the command ifconfig to print your Pi IP address in your terminal window) and this is what you should see:
Your PHP installation was successful. Now, you can remove the index.php file:
sudo rm index.php
While on the /var/www/html directory, create a new folder called database:
sudo mkdir database
Follow the next command to download and prepare phpLiteAdmin:
sudo wget https://bitbucket.org/phpliteadmin/public/downloads/phpLiteAdmin_v1-9-7-1.zip
sudo unzip phpLiteAdmin_v1-9-7-1.zip
sudo rm phpLiteAdmin_v1-9-7-1.zip
To set a password and the path to your SQLite database in your phpLiteAdmin configuration, this is what you need to do:
sudo cp phpliteadmin.config.sample.php phpliteadmin.config.php
sudo nano phpliteadmin.config.php
Change your file to look similar to mine (see below). Type a unique password and the path to your SQLite database:
Create a SQLite database file
You can use the file created in the previous tutorial or simply create a new one:
Changing file and folder permissions
In order to make phpLiteAdmin work properly you need to change some permissions and files ownership. Run the following commands:
sudo chmod 777 database
sudo chmod 777 database/*
sudo chown root:root database
sudo chown root:root database/*
After running the preceding commands, confirm that root is the owner and those are the permissions for the database folder:
drwxrwxrwx 2 root root 4096 Jan 30 15:11 database
Open your http://RPi_IP_Address/database/phpliteadmin.php. This is how it should look like:
Enter your password to login and this is the web page that loads:
On the left menu, you can see your database and table (if you’ve used the database created in the previous SQLite tutorial). Open the dhtreadings table:
Inside that table you can see two rows:
Open the Structure tab:
You can see the settings of each column and you can change them if necessary.
Next, open the sensordata database:
Then, you could define the data columns for each row:
The real advantage of using phpLiteAdmin is: instead of sending SQL commands through a terminal window, you can add, remove, update all data through a nice user interface.
We’ve just scratched the surface of what you can do with SQLite.