Data Collection with GridDB and PHP | GridDB: Open Source Time Series Database for IoT

Data collection is a very important phase in the majority of system development projects and researches. In this article, we will discuss how we can effectively perform a data collection task with PHP and GridDB. Let’s discuss a situation where we need to collect data through a simple web application. First of all let’s make the scenario clear.

The scenario

There is a project about human nutritions that requires collecting data related to the breakfast, lunch and dinner of selected people. Every person has an ID for unique identification and they are required to log the details such as ID, type of meal and foods included in the meal to a web based form right before having each meal. Since this application automatically captures the submitted timestamp, the users don’t have to add the time explicitly. Moreover, this application consists of a simple HTML form with a PHP backend and GridDB database .

The following table will give you a proper idea about what details should be submitted in the form.

Okay, now let’s move to the implementation. Implementation mainly will be done in two steps.

Creating HTML form
Developing the PHP backend with GridDB

Creating HTML form

We need to create a simple HTML form with ID, meal and meal type. user ID and meal details are collected with text boxes and meal type is collected with radio buttons. The complete HTML script can be written as follows. We have used Bootstrap 4 to create this form.

<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8" /> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> <meta http-equiv="X-UA-Compatible" content="ie=edge" /> <title>Static Template</title> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous" /> </head> <body> <h2 style="text-align: center;">Data Collection For Research</h2> <form style="margin: 20px;"> <div class="form-group"> <label for="userid">ID</label> <input type="text" class="form-control" id="userid" aria-describedby="userid" placeholder="Enter Your ID" /> <small id="userid" class="form-text text-muted" >Please enter the unique ID that given by the organization.</small > </div> <div class="form-group"> <label for="mealtype">Meal Type</label> <br /> <div class="form-check form-check-inline" is="mealtype"> <input class="form-check-input" type="radio" name="breakfast" id="breakfast" value="breakfast" /> <label class="form-check-label" for="breakfast">Breakfast</label> </div> <div class="form-check form-check-inline"> <input class="form-check-input" type="radio" name="lunch" id="lunch" value="lunch" /> <label class="form-check-label" for="lunch">Lunch</label> </div> <div class="form-check form-check-inline"> <input class="form-check-input" type="radio" name="dinner" id="dinner" value="dinner" /> <label class="form-check-label" for="dinner">Dinner</label> </div> </div> <div class="form-group"> <label for="meal">Meal</label> <input type="text" class="form-control" id="meal" aria-describedby="meal" placeholder="Enter Your Main meal" /> </div> <button type="submit" id="submitBtn" class= "btn btn-primary">Submit</button> </form> </body> </html>

The output looks like this.

Static Template

Data Collection For Research

Now let’s move to the second step in our implementation.

Developing the PHP backend with GridDB

Now we are done with our front-end part. Next we need to develop the backend with PHP and GridDB. First we need to install and set up the GridDB database connector for PHP. This connector supports PHP version 7 upwards. So you need to use PHP 7 or a higher version for this application.

You can clone the Github repository of the PHP client with the following command.

git clone https://github.com/griddb/php_client.git

You need to build and install the GridDB C client in order to set up the PHP client. You can read this blog to have a clear idea on how to install and set up the GridDB C client. After that, it’s required to follow the instructions in the README page of the PHP client github page to set up the PHP client.

At this point, I assume that you have successfully installed and set up the PHP client following the steps mentioned above. So now let’s see how we can connect GridDB with the PHP client.

Connecting GridDB with PHP client

To connect GridDB with the PHP client, you simply need to include the griddb_php_client. The GridDB package name in PHP is StoreFactory. The code below shows you how to implement the connection.

?php
     include('griddb_php_client.php');
     $factory = StoreFactory::get_default();
     try {
        
         $gridstore = $factory->get_store(array("notificationAddress" => $argv[1],
                           "notificationPort" => $argv[2],
                           "clusterName" => $argv[3],
                           "user" => $argv[4],
                           "password" => $argv[5]
                       ));
       } catch(GSException $e){
           echo($e->what()."\n");
           echo($e->get_code()."\n");
       }
    ?

Let’s discuss what we have done in this code. Here we have initialized a GridDB instance. For that, it’s required to mention the notification port, the cluster name , username and password. If any non-descriptive error occurs this will give the error and the error code.

Simple, right?

Creating a container

By now, we have initialized a gridstore. Now let’s discuss how we can create a container to store our data. As we discussed in the beginning of this tutorial, there should be 4 attributes in the container as User ID, Meal type, Meal and Timestamp.

In the implementation, we have used put_container method to create the container. The first column must be the timestamp. After creating the schema, we can insert the data as rows to the container.

$ts = $gridstore->put_container("point01", array(array("timestamp" => GS_TYPE_TIMESTAMP),
    array("userid" => GS_TYPE_STRING),
    array("mealtype" => GS_TYPE_STRING)),
    array("meal" => GS_TYPE_STRING)),
    GS_CONTAINER_TIME_SERIES);

This is how we create schema with required attributes. Please note the types of the attributes. The type of the timestamp attribute is GS_TYPE_TIMESTAMP. We have used GS_TYPE_STRING type for other attributes.

You can use any type such as GS_TYPE_DOUBLE and GS_TYPE_INTEGER based on the attributes you need to have in the database.

Next we are going to create a row object and add data to it. It’s quite simple to do that. First let’s check how we can save some hard coded data.

$row = $ts->create_row();
        $row->set_field_by_timestamp(0, TimestampUtils::current());
        $row->set_field_by_string(1, 'GD1001');
        $row->set_field_by_string(2, 'dinner');
        $row->set_field_by_string(3, 'hoppers');
        $ts->put_row($row);

Let’s see what we have done here. First we created a new row object with create_row method. The important fact is, you should enter all the values to the row in the order that you have created the schema. As you can see in the code, 0,1,2,3 are the column indexes.

Now we know how to insert hard coded data. Next let’s see how we can insert data which are sent from our html form. For that, we need to check whether the submit button is clicked or not. If it is clicked we can declare some variables and save data which has been sent from the form. Then we can save those data in the database as we did in previous example code.

if(isset($_POST["submitBtn"]) && $_POST["submitBtn"]!=""){
        $userid=$_POST["userid"];
        $mealtype=$_POST["mealtype"];
        $meal=$_POST["meal"];
      
        $row = $ts->create_row();
        $row->set_field_by_timestamp(0, TimestampUtils::current());
        $row->set_field_by_string(1, $userid);
        $row->set_field_by_string(2, $mealtype);
        $row->set_field_by_string(3, $meal);
        $ts->put_row($row);

So this is how we can save form data in PHP using GridDB. Note that we have used the $_POST method to get data from the form to the current PHP page. As you can see, we have saved all the data in row format.

You don’t need to input the time that you submit the form. The timestamp automatically gets saved. You can analyse and retrieve data with multiple analysing methods in PHP with GridDB support.

Query the database

In this part let’s briefly talk about how we can query the database and analyze the data according to the requirements.

Suppose that you need to get the details of the meals in all the breakfasts. What can you do? You can simply query the database to get all the related details. For that, we need to create the query as follows.

$sql = "SELECT * FROM point01 WHERE mealtype='breakfast'";

Next a row object should be created.

$row = $ts->create_row();

Now we can access all the details required by iterating through all the results we require as follows.

$rs = $query->fetch($allow_update);
        while ($rs->has_next()){
        
         $rs->get_next($row);
         $userid = $row->get_field_as_string(1);
         $meal = $row->get_field_as_string(3);
          echo("UserId = $userid Meal = $meal");
        }

Note that we have accessed the required columns with it’s index that we have initialized previously and these indexes are 0 based.

You see the method get_field_as_string(1). Here, 1 is the 2nd column in the collection. That means it is the UserID.

Cool right?

Now let’s take a look at another query. Suppose that I need to get all the meal details which are submitted after a specific timestamp. That query will be written as follows.

SELECT * FROM point01 WHERE timestamp > 05012019 00:00:00

Then we can get all the necessary details we need like in the previous example.

However, here we have hard coded the timestamp value. If you need to use the query for multiple timestamps, you can use a function with simple changes to code above.

function getMeal($timestamp){

        $sql = "SELECT * FROM point01 WHERE timestamp > $timestamp";
        $row = $ts->create_row();
        $rs = $query->fetch($allow_update);
       
        while ($rs->has_next()){
       
          $rs->get_next($row);
          $userid = $row->get_field_as_string(1);
          $mealtype = $row->get_field_as_string(2);
          $meal = $row->get_field_as_string(3);
           echo("UserId = $userid Meal = $meal MealType = $mealtype");
        }  
       }

Likewise, you can use any other query to analyze and retrieve data. Try with complex queries that include joins and aggregation methods such as AVG, COUNT etc.

Summary

Okay, that’s pretty much about it. We learned how we can use GridDB with PHP for data collection purposes. Here we used a simple scenario that is useful in many of the researches and systems. The methods and techniques we have used in this article can be used for any related scenarios. It’s much easier to work with the GridDB PHP client. Considering the many utilities available in it and the time series data support, GridDB is something every PHP developer should know to develop high performance applications.

If you have any questions about the blog, please create a Stack Overflow post here https://stackoverflow.com/questions/ask?tags=griddb .
Make sure that you use the “griddb” tag so our engineers can quickly reply to your questions.