Building a car battery data logger Part 5: Building a simple login system and displaying sets of data in SQL database into web page in form of line chart
This part shows how to use various language to build a simple dashboard with login authorization and date-search function in which screening out data, including:
In this project, I have taken my PC as Apache server and MySQL server by using XAMPP software.
CSS is a language that describes the style of an HTML document. Thanks to CSS, a simple HTML login form can be displayed in a nicer form.
To allow predefined users to log in and review the recorded data, an SQL query is needed in the authorization process. To begin with, a php file, checkpwd.php has to be created to do the following tasks:
- Create a connection to the database
- Select a specific table
- Search every row data in the table
- Pop up prompt message to show the success or failure
As an example, I have created a database called 'member' and there is a table called 'administrator'. The details of the table is shown below.
Simple HTML5 form:
When a user inputs username and password, that information will be stored in a global array called $_POST where the indexes depend on what you defined in HTML5 form.
However, we often connect to the database and do SQL query while we are writing or retrieving data into/from the database. Therefore, this will be more convenient.
If we can directly call the function instead of copying the whole code somewhere it is needed. I have developed a php file called dbtools.php to declare create_connection() and execute_sql() as below:
create_connection() allows you to connect the MySQL server after you input “IP addess”, “Server hostname” and “password” accordingly. Whenever you want to make an SQL query, you can call execute_sql() with the parameters of the database name and your SQL query statement. If you want to use the above two functions, you just simply declare require_once(“dbtools.php”) at the beginning of your current php file. Let's say “user” and “password” are the names I have defined in the form.
The details of checkpwd.php is shown as follows:
mysqli_num_rows() return the number of rows in the result set. So, there is no such record in the table if it returns 0. Otherwise, the record was found.
Next part is to talk about how to plot a simple line chart on a web page with date-search data screening function as follow:
For simplicity, CanvasJs - a simple API - was adopted in my design. One of the advantages of using CanvasJS is that it can provide high precision in ms unit. So, data representation is still fine if you take the measurement at a much higher speed rate. However, the x-axis is based on the timestamp in milliseconds, so you need to transform the date inputed into timestamp format in milliseconds. To start plotting graph, the following needs to be done:
- Create two 2D arrays to store the arrays of voltage and current respectively
- Json format encoding
- Set up Bootstrap date search form
- Convert date to a timestamp in milliseconds
To begin with, searching every row in the table and to retrieve voltage, current and timestamp information. Voltage with timestamp information data set will be stored into an array called $point_type1. Similiarly, current with timestamp information data set will be stored into $point_type2. The way of storing format is closely related to the application of CanvasJs. $point_type1 and $point_type2 will be pushed into $vol_ary and $cur_ary arrays respectively at the end of searching of every row.
Nowadays, Bootstrap Datepicker is commonly used as you may have seen in different websites. The following code shows a simple date picker that I have used.
The date will be stored in $_POST["date"] in a format of MM/DD/YYYY. Therefore, I need to take a conversion and it can be realized using the following code:
One point worth mentioning is that strtotime() requires a default timezone otherwise it will generate a timestamp in server timezone which could be misleading. One of the possible solution is to set the default time zone by using date_default_timezone_set(). A simple example is shown as below:
This is the end of the sharing of my car battery data logger build.