Visualise IP-Geolocation data stored in MySQL on a PHP dashboard

For a C++ application I write, the users IP address is run through an IP-Geolocation service from ipify.org.

For each user, it adds an entry into my MySQL Database (MariaDB) each time they connect with one cell containing the following info:

“{"ip":"195.140.224.237","success":true,"type":"IPv4","continent":"Europe","continent_code":"EU","country":"Ukraine","country_code":"UA","country_flag":"https:\/\/cdn.ipwhois.io\/flags\/ua.svg","country_capital":"Kyiv","country_phone":"+380","country_neighbours":"BY,HU,MD,PL,RO,RU,SK","region":"Kharkiv Oblast","city":"Lozova","latitude":48.8902362,"longitude":36.308001,"asn":"AS56465","org":"Therecom Ltd","isp":"Therecom Ltd","timezone":"Europe\/Kiev","timezone_name":"EEST","timezone_dstOffset":3600,"timezone_gmtOffset":10800,"timezone_gmt":"+03:00","currency":"Ukrainian Hryvnia","currency_code":"UAH","currency_symbol":"\u20b4","currency_rates":30.45,"currency_plural":"Ukrainian hryvnias"}”

I also record their FCT and LCT (First Connect Time & Last Connect Time). FCT is the date/time that particular client_ID was first seen on the platform. LCT is the last date/time an already known client_ID was seen reconnecting to the system.

I would like some way to visualise these stats on a PHP page so I can see details about my users. This better helps me target the app to them such as providing localisations (language updates) or secondary servers in particularly busy locations to shorten download times for the files the app provides.

My current ideas are I’d like to see:
- Count of the current active users (users with a last connect time (LCT) of <15 mins)
- Some kind of map overlay where I can see current active sessions overlaid onto a map maybe with the ability to hover over each “hotspot” of activity to see how many sessions are originating from that area. Something like this, but it does NOT need to be 3D https://pbs.twimg.com/media/EnsZxPFW4AAGPV-.jpg

Then just stats for fun, things like
- total visitors from various countries
- top 10 countries
- etc

Essentially, I can provide you with the data I have and id just like to see as much of it as possible visually represented to save me accessing the data through MySQL Workbench every time.

The only boundary I will provide is the PHP page template which my admin back-end runs on. The page needs to fit into the styling of my other pages, which is very minimal and won’t be an issue with your work.

Access will be provided to the FTP server and database credentials can be provided, or I’m happy to provide screenshots of the data I have in the database.

There’s a lot of freedom on this project to do whatever you wish as long as it accomplishes the goal of providing a visually appealing way to view the data about the app users.

Occasionally, the app or IP-Geolocation service has a hiccup and a user may not have any data in the ip-geolocation field, the script needs to simply skip these if there is missing data and not throw an error.

For an idea of the data count, we’re currently holding data for just over 3,000 users and growing. The script needs to be able to handle 3,000 users as easily as 30,000 (not slow down significantly as more people use the app) and obviously be as lightweight as possible in terms of CPU cycles and time taken to load and parse the data.
Maximum PHP execution time with my host is 60 seconds, you need to work with this in mind on a growing database with approx 2,000-3,000 additions per year.

If required, I am happy to add another table (of your design) to the database to hold any cached stats if this helps.

The attached page template.txt is actually a zip file (PPH does not allow the upload of ZIP's), please rename the file to .zip and then extract it to get the php page, images and CSS)