Monitor multiple Raspberry Pi logins using PHP and MySQL

As you can see I recently bought a domain name in order to host a more customisable WordPress blog as well as build a few experiments. One of which is a quick and easy system for logging each of the Raspberry Pi devices that run my custom boot script.

I’d previously created something similar when I got my daughter’s Raspberry Pi to send a tweet whenever it booted up. That was fun but it didn’t scale easily when used with multiple devices. I wanted a central list where all devices were displayed in boot order.

I decided to create a simple MySQL database that could be queried via PHP. The database would contain information sent to it by each Raspberry Pi as it connected to the Internet. I decided initially that I only required device name, username and a timestamp.

I had thought that each Raspberry Pi could run a Python program that opened a connection to the MySQLi database but this appears to be the wrong approach. It seemed much easier to create PHP code that wrote a line to the database using values pulled from the URL (using the $_GET command).

In case it helps, here is some of the PHP to add information to the database:

$sql = "INSERT INTO pilogin (piname, timestamp, username) VALUES ('" . $_GET['piname']."', now(), '" . $_GET['username']."')";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "
" . $conn->error;

The final step was to edit /etc/rc.local so that it ran cURL with the appropriate web address and values. I wanted to delay the command until the network connection setup was complete and found that this forum post had a great suggestion for doing this while also allowing other programs to continue running.

I won’t post the actual line for obvious reasons but a similar solution. I’ve also included code that extracts the current Raspberry Pi hostname and user and encodes it into the URL.

Here is an extract from my /etc/rc.local file:

u="$USER" # system variable for user
h=hostname #note the backticks
(sleep 30s; curl "http://webaddress/page.php?hostname=$h&user=$u") &

Happy to hear of any improvements or alternatives!


Leave a Reply

Your email address will not be published. Required fields are marked *

Raspberry Pi Replit

Installing WordPress on a repl

Are you tired of sky-high hosting costs for your website? Have you ever considered running your own web server? If so, you’ll be excited to hear about the success I had reducing hosting costs for my csteach site.

Read More
AH Computing Science Computing Science Scotland Student Engagement

Connecting to a MySQL database using Python 3 #compsci #SQA #AHcomputingscience

Students completing the SQA Advanced Higher Computing Science course need to create their projects with a major and minor in mind. The major – the main area of content their project will showcase – can be either Software Development (e.g. Python), Web Design and Development (PHP) or Database Design and Development (e.g. MySQL). The minor […]

Read More
Student Engagement Uncategorized

Python Programming Challenges and open book assessments

Given that it is Computer Science in Education week and the last few weeks of term I wanted to wrap up my practical programming lessons for the term with some Python programming challenges. Why programming challenges? In the past I’ve used these successfully with lower age groups. In my opinion it helps to validate the […]

Read More