How to install phpLiteAdmin Database in a Raspberry Pi

In this blog post I’ll introduce you to phpLiteAdmin which is a web-based user interface written in PHP that makes it easy to interact with SQLite databases.

Don’t have a Raspberry Pi board? read Best Raspberry Pi Starter Kits.

Basic Raspberry Pi setup

Before you continue reading this project, make sure you have Raspbian Operating System installed in your Raspberry Pi.

You can read my Getting Started with the Raspberry Pi Guide to install Raspbian and complete the basic setup.

Installing SQLite

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:

pi@raspberry:~ $ 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:

pi@raspberrypi:~ $ sudo apt-get install php5 libapache2-mod-php5 php5-sqlite -y

Move to your /var/www/html directory and edit the dir.conf file:

pi@raspberry:~ $ cd /var/www/html
pi@raspberrypi:/var/www/html $ 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:

pi@raspberrypi:/var/www/html $ sudo service apache2 restart

Testing your installation

Create a new PHP file with the name index.php:

pi@raspberrypi:/var/www/html $ sudo nano index.php

Copy the next line to your index.php file:

<?php phpinfo(); ?>

Ctrl+X, type Y and press Enter to save your changes.

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:

pi@raspberrypi:/var/www/html $ sudo rm index.php

Installing phpLiteAdmin

While on the /var/www/html directory, create a new folder called database:

pi@raspberrypi:/var/www/html $ sudo mkdir database
pi@raspberrypi:/var/www/html $ cd database/
pi@raspberrypi:/var/www/html/database $

Follow the next command to download and prepare phpLiteAdmin:

pi@raspberrypi:/var/www/html/database $ sudo wget https://bitbucket.org/phpliteadmin/public/downloads/phpLiteAdmin_v1-9-7-1.zip
pi@raspberrypi:/var/www/html/database $ sudo unzip phpLiteAdmin_v1-9-7-1.zip
pi@raspberrypi:/var/www/html/database $ 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:

pi@raspberrypi:/var/www/html/database $ sudo cp phpliteadmin.config.sample.php phpliteadmin.config.php
pi@raspberrypi:/var/www/html/database $ 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:

pi@raspberrypi:/var/www/html/database $ sqlite3 sensordata.db
sqlite> .quit

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:

pi@raspberrypi:/var/www/html/database $ cd ..
pi@raspberrypi:/var/www/html $ sudo chmod 777 database
pi@raspberrypi:/var/www/html $ sudo chmod 777 database/*
pi@raspberrypi:/var/www/html $ sudo chown root:root database
pi@raspberrypi:/var/www/html $ 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:

pi@raspberrypi:/var/www/html $ ls -l
drwxrwxrwx 2 root root 4096 Jan 30 15:11 database

Testing phpLiteAdmin

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 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:

Select the Create new table option and give it a name, for example outputs:

Then, you could define the data columns for each row:

Wrapping up

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. In the next blog post, we will publish sensor readings with the ESP8266 that will be stored in the database and displayed in your Python web server.

Like home automation? Learn more about Node-RED, Raspberry Pi, ESP8266 and Arduino with my course: Build a Home Automation System for $100.

Do you have any questions? Leave a comment down below!

Thanks for reading. If you like this post probably you might like my next ones, so please support me by subscribing my blog.



Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »
Learn how to build a home automation system and we’ll cover the following main subjects: Node-RED, Node-RED Dashboard, Raspberry Pi, ESP32, ESP8266, MQTT, and InfluxDB database DOWNLOAD »

Enjoyed this project? Stay updated by subscribing our newsletter!

16 thoughts on “How to install phpLiteAdmin Database in a Raspberry Pi”

  1. I recommend also having a look at “Adminer- Database managerment in a single file”. It is a very small but capable tool with PHP and MySQL support in one file sized 283 kB. I have switch from all Php*Admins to this lately due to its simplicity and the one-file-only installation.

    You can find it at adminer.org.

    Reply
  2. Hi Rui,
    I really enjoy reading your posts. It have helped me a lot with my hobby projects.
    Have you been working with any .Net Micro Framework microprocessors?

    Reply
  3. I was wondering if you could try this using the QEMU Raspberry Pi 3 emulator for windows? I’m sure this will work best using the real thing but sometimes I like to use an emulator because I don’t always carry my Pi 3 with me.

    Reply
  4. hi

    i need help pls

    pi@raspberrypi:~ $ sudo apt-get install php5 libapache2-mod-php5 php5-sqlite -y
    Reading package lists… Done
    Building dependency tree
    Reading state information… Done
    Package php5 is not available, but is referred to by another package.
    This may mean that the package is missing, has been obsoleted, or
    is only available from another source

    Package libapache2-mod-php5 is not available, but is referred to by another package.
    This may mean that the package is missing, has been obsoleted, or
    is only available from another source

    Package php5-sqlite is not available, but is referred to by another package.
    This may mean that the package is missing, has been obsoleted, or
    is only available from another source

    E: Package ‘php5’ has no installation candidate
    E: Package ‘libapache2-mod-php5’ has no installation candidate
    E: Package ‘php5-sqlite’ has no installation candidate

    Reply
  5. Thanks for the post, But I am facing an error after this step:
    – 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:

    My Raspberry Pi IP address is ‘192.168.178.147’, After opening this in chrome, a blank page appears with the ‘ ‘ return on it.

    Reply

Leave a Comment

Download Our Free eBooks and Resources

Get instant access to our FREE eBooks, Resources, and Exclusive Electronics Projects by entering your email address below.