Sending sensor data to localhost using IoT development board esp8266-12

Sending  sensor data to localhost  using IoT development board esp8266-12

In this article, I am going to show you how to send sensor sensor data to a local webserver using esp8266.

Before delving into this article , let me explain the term localhost.
Localhost refers to the local computer that a program is running on. For example, if you are running a Web browser on your computer, your computer is considered to be the "localhost".

The deliverables for this project are:

Xamp webserver download it here https://www.apachefriends.org/

Arduino IDE download here https://www.arduino.cc/

ESP8266 official website https://www.nodemcu.com/

Smartphone used as a router.

Project requirement: Create a folder in the xamp/htdocs named esp8266. In this folder you are going to save the postData.php and databaseConfig.php

To send the sensor data to a database, we need to write an arduino sketch using a TCP protocol to communicate wirelessly to the local webserver.

Let's get it done now!

Step 1: Install esp8266 board the link to the procedure can be found here https://randomnerdtutorials.com/how-to-install-esp8266-board-arduino-ide/

Step 2: The arduino sketch is shown below. I create two function connectWifi() and sendSensorData() which I invoke respectively in the void setup and void loop. If you are confused please look for a tutorial on basic arduino code.

#include <WiFiClient.h> 
#include <ESP8266WebServer.h>
#include <ESP8266HTTPClient.h>
const char *ssid = "MelleB";  //ENTER YOUR WIFI ssid
const char *password = "refj4497";  //ENTER YOUR WIFI password


void setup() {
connectWifi();
}


void loop() {
SendSensorData();
}
//function to connect to wifi
void connectWifi(){
  delay(1000);
  Serial.begin(115200);
  WiFi.mode(WIFI_OFF);        //Prevents reconnection issue (taking too long to connect)
  delay(1000);
  WiFi.mode(WIFI_STA);        //This line hides the viewing of ESP as wifi hotspot
  WiFi.begin(ssid, password);     //Connect to your WiFi router
  Serial.println("");
  Serial.print("Connecting");
  // Wait for connection
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  //If connection successful show IP address in serial monitor 
  Serial.print("Connected to ");
  Serial.println(ssid);
  Serial.print("IP address: ");
  Serial.println(WiFi.localIP());  //IP address assigned to your ESP
}


//function to send sensor data 
void SendSensorData() {
  HTTPClient http;    //Declare object of class HTTPClient
  String sensorData1,sensorData2,sensorData3,sensorData4,sensorData5,sensorData6,sensorData7, postData;
  sensorData1="High";
    sensorData2="High";
      sensorData3="High";
        sensorData4="High";
          sensorData5="High";
            sensorData6="High";
              sensorData7="High";
  //Post Data
  postData = "sensor1=" +  sensorData1 + "&sensor2=" + sensorData2+ "&sensor3=" + sensorData3+  "&sensor4=" + sensorData4+ "&sensor5=" + sensorData5+ "&sensor6=" + sensorData6+  "&sensor7=" + sensorData7;
  
  http.begin("http://192.168.43.142/esp8266/postData.php");              //change the ip to your computer ip address
  http.addHeader("Content-Type", "application/x-www-form-urlencoded");    //Specify content-type header
 
  int httpCode = http.POST(postData);   //Send the request
  String payload = http.getString();    //Get the response payload
 
  Serial.println(httpCode);   //Print HTTP return code
  Serial.println(payload);    //Print request response payload
 
  http.end();  //Close connection
  
  delay(5000);  //Post Data at every 5 seconds
}

Step 3: Create the database sensor and the table name logs and save it in the esp866 folder as databaseConfig.php

//connect to localhost if not exists
	$servername = "localhost";
	$username = "root";
	$password = "";


	// Create connection
	$conn = new mysqli($servername, $username, $password);
	// Check connection
	if ($conn->connect_error) {
	    die("Connection failed: " . $conn->connect_error);
	}


	// Create database
	$sql = "CREATE DATABASE sensor";
	
	    echo "Database created successfully";
	} else {
	    echo "Error creating database: " . $conn->error;
	}


	$conn->close();


	echo "<br>";
//Connect to database and create table
	$servername = "localhost";
	$username = "root";
	$password = "";
	$dbname = "sensor";


	// Create connection
	$conn = new mysqli($servername, $username, $password, $dbname);
	// Check connection
	if ($conn->connect_error) {
	    die("Connection failed: " . $conn->connect_error);
	}


	
	$sql = "CREATE TABLE logs (
	id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
	sensor1 VARCHAR(30),
	sensor2 VARCHAR(30),
    sensor3 VARCHAR(30),
	sensor4 VARCHAR(50),
	sensor5 VARCHAR(50),
	sensor6 VARCHAR(50),
	sensor7 VARCHAR(50),
	\`Date\` DATE NULL,
	\`Time\` TIME NULL, 
	\`TimeStamp\` TIMESTAMP NULL DEFAULT CURRENT\_TIMESTAMP ON UPDATE CURRENT\_TIMESTAMP)";


	if ($conn->query($sql) === TRUE) {
	    echo "Table logs created successfully";
	} else {
	    echo "Error creating table: " . $conn->error;
	}


	$conn->close();
?>

Step 4: Create postData.php in the esp866 folder and paste the code below in it.

//Creates new record as per request
    //Connect to database
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "sensor";


    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
        die("Database Connection failed: " . $conn->connect_error);
    }


    //Get current date and time
    date\_default\_timezone_set('Asia/Kolkata');
    $d = date("Y-m-d");
    //echo " Date:".$d."<BR>";
    $t = date("H:i:s");


    if(!empty($\_POST\['sensor1'\]) || !empty($\_POST\['sensor2'\]))
    {
    	$sensorData1 = $_POST\['sensor1'\];
    	$sensorData2 = $_POST\['sensor2'\];
		$sensorData3 = $_POST\['sensor3'\];
		$sensorData4 = $_POST\['sensor4'\];
		$sensorData5 = $_POST\['sensor5'\];
		$sensorData6 = $_POST\['sensor6'\];
		$sensorData7 = $_POST\['sensor7'\];


	    $sql = "INSERT INTO logs (sensor1, sensor2,sensor3,sensor4,sensor5,sensor6,sensor7, Date, Time) VALUES ('".$sensorData1."', '".$sensorData2."', '".$sensorData3."', '".$sensorData4."', '".$sensorData5."', '".$sensorData6."', '".$sensorData7."', '".$d."', '".$t."')";


		if ($conn->query($sql) === TRUE) {
		    echo "OK";
		} else {
		    echo "Error: " . $sql . "<br>" . $conn->error;
		}
	}




	$conn->close();
?>

Step 5: software testing

  • Open your web browser and type localhost/esp8266. Click on databaseConfig.php you should get a message data base sensor created successfully and do the same with postData.php. If you do not get any error message then you are good to go.
  • Open the serial monitor of your arduino and set the baud rate to 115200
  • The wifi ssid and ip address should display to the serial monitor
  • You should get a response 200 from the server and ok message
  • Check your database table you will get all the sensor table high, the date and time.
  • ** The output is shown below**

Please let me know in case of any errors.

PHP / MySQL - Count how many table in database contain Specific name

I'm building an admin to control an interactive game, and I'm using PHP/MySql to store all the data for all the rounds. Each of the tables that I want to load into the game via PHP are titled "Puzzle*" with the * being an incremental index number. There are a few other tables in my database for global game data and bonus rounds.

I'm building an admin to control an interactive game, and I'm using PHP/MySql to store all the data for all the rounds. Each of the tables that I want to load into the game via PHP are titled "Puzzle*" with the * being an incremental index number. There are a few other tables in my database for global game data and bonus rounds.

What I want to be able to do is count the number of tables in my database that contain the name "Puzzle", and then use that total number as a randomizer to select a puzzle randomly. I plan to have roughly 50 puzzles, so an automated way of selecting a random puzzle, and then if I want to select another puzzle - to remove that previous puzzle from the list so that there is no chance of it being repeated until I start the whole game over again.

MySQL Database

Database Name: puzzleGame
Table 1: puzzle1
Table 2: puzzle2
Table 3: puzzle3
Table 4: puzzle4
Table 5: globals
Table 6: bonus


Inserting session array into mysql database

I am still learning bits of PHP and have been stuck on this issue regarding the POST data from radio buttons within a form being inserted into a mysql database from a session array. Currently, I have 2 questions within my survey and they consist of radio button options for a user to select from. The relevant answers can be selected and they are stored in a session array:

I am still learning bits of PHP and have been stuck on this issue regarding the POST data from radio buttons within a form being inserted into a mysql database from a session array. Currently, I have 2 questions within my survey and they consist of radio button options for a user to select from. The relevant answers can be selected and they are stored in a session array:

array(1) { ["choice"]=> array(2) { [0]=> string(11) "56-65 years" [1]=> string(17) "Prefer not to say" } }

However I was not too sure if the array should be as such. The two answers chosen from the radio options in the above example are "56-65 years" and "Prefer not to say".

Following on from the session array, I am trying to insert each of those answers in to a mysql database. I have a column created to insert each answer (i.e. there are two columns (question 1 and question 2) to store the two answers from the example above).

I am aware that my code is open to SQL injection and plan on fixing that after overcoming the inserting of data.

Question.php

     <form method="POST" action="process.php">
      <ul class="choices">
  &lt;?php while($row = $answers-&gt;fetch_assoc()):?&gt;
    &lt;li&gt;&lt;input type="radio" name="choice" value="&lt;?php echo $row['answerText']; ?&gt;"&gt;

    &lt;?php echo $row['answerText'] ?&gt;&lt;/li&gt;
    &lt;?php endWhile; ?&gt;

  &lt;/ul&gt; 
  &lt;input type="submit" name="submit" value="Next Question"&gt;
  &lt;input type="hidden" name="number" value="&lt;?php echo $number; ?&gt;"&gt;

</form>

Process.php

<?php

    if(isset($_POST['submit'])){

$number = $_POST['number'];
$next = $number+1;

$selected_choice = $_POST['choice'];
$_SESSION['choice'][] = $selected_choice;

foreach ($_SESSION['choice'] as $key) {

$user_responses = $key;

}

$insertdata = "INSERT INTO userAnswersTable('question1', 'question2') VALUES ($_SESSION['choice'])";
$resulting = $mysqli->query($insertdata) or die($mysqli->error.LINE);

}
?>

I would expect each of the user responses to be in an array from which each individual selected choice within the session array can be stored in each corresponding column within the mySql table. Any suggestions would be very helpful.

Flexible PHP 7- MySQLi database class (+ download)

Flexible PHP 7- MySQLi database class (+ download)

We all know it: Write annoying code over and over again! That’s over now. With this PHP MySQLi database class you can easily connect to your MySQL database.

This class is very simple and flexible for any project. It also ensures that no SQL-Injectionattacks can be carried out. You may use the class below freely for your projects (also commercially) and of course extend it as you like. And so you use it:

1. Include class in your project structure

You copy the complete class and create a new file in your project structure, e.g. Database.php and paste the complete code there.

<?php
/**
* Simple Database class for PHP7+
* The class contains main functions for your database. For a detailed documentation, see: https://webdeasy.com/
* created 08.11.2017
* 
* @author LH
*/
class Database {
  private $host, $database, $username, $password, $connection;
  private $port = 3306;

/**
*

  • Sets the connection credentials to connection to your database
  • @param string $host - the host of your database
  • @param string $username - the username of your database
  • @param string $password - the password of your database
  • @param string $database - your database name
  • @param integer $port - the port of your database
  • @param boolean $autoconnect - to auto connect to the database after settings connection credentials
    /
    function __construct($host, $username, $password, $database, $port = 3306, $autoconnect = true) {
    $this->host = $host;
    $this->database = $database;
    $this->username = $username;
    $this->password = $password;
    $this->port = $port;
    if($autoconnect) {
    $this->open();
    }
    }
    /
    *
  • Open the connection to your database
    /
    function open() {
    $this->connection = new mysqli($this->host, $this->username, $this->password, $this->database, $this->port);
    }
    /
    *
  • Close the connection to your database
    /
    function close() {
    $this->connection->close();
    }
    /
    *
  • Execute your query
  • @param string $query - your sql query
  • @return the result of the executed query
    /
    function query($query) {
    return $this->connection->query($query);
    }
    /
    *
  • Escape your parameter
  • @param string $string - your parameter to escape
  • @return the escaped string
    */
    function escape($string) {
    return $this->connection->escape_string($query);
    }
    }
    ?>
2. Include database class

Your program code runs in a different PHP file. There you add the following code to include the class:

require_once("path/to/your/file/Database.php");

What’s the different between require and includeThis!

3. Create instance

To connect to the database, we need to create an instance of the class. For this we need the access data to the database. You can find the optional parameters of the construct in the class. A call could look like this:

$database = new Database("localhost", "testuser", "verySafePassword", "ourDatabase");

An attempt is made to establish a connection to the database with the access data transferred. This happens automatically if the $autoconnect parameter is set to true (see line 26).

If you don’t get any errors, the connection to the database is established successfully. Now you can formulate your SQL queries.

4. Escape parameters!

That’s probably the most important step. To prevent SQL injections, every parameter you use in the query must be escaped. This can prevent harmful SQL statements – whether intentional or unintentional – from getting into your query and thus into your server system. The following lines must be inserted before each query:

$parameter = $_POST["id"];
$parameter = $db->escape($parameter);

In this example, the id parameter of the POST request is passed to PHP and escaped by the second line.

5. Your queries

You can pass the queries to the query($query) function. The query is sent to the database and the function returns the result.

Example of an Insert Query

$query = "INSERT INTO users (id, name) VALUES (1, 'Peter Parker');";
$db->query($query);

Example of a select query with output of the result

$query = "SELECT name FROM users WHERE id = 1";
$result = $db->query($query);

while($row = mysqli_fetch_assoc($result)) {
echo "Name: " . $row["name"];
}
</pre>

At the end of your program you should call the close() function to close open database connections.

$db->close();

Ultimately, these are the functions of the database class. I kept them as simple as possible, but still tried to include all important and security relevant functions. And yes: I am aware that PHP and the MySQLi class have much more functions to offer. But this class is just for simple, fast and flexible applications that only require a simple database connection.

Safety instructions or suggestions for improvement are welcome in the comments, so that I can add them! 

Originally published at webdeasy.de on 26. May 2019

====================================================================

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter


Learn More

☞ Ultimate PHP Basics for Absolute Beginners - [200+ PHP Code]

☞ PHP in Web Development in 2020

☞ PHP for Beginners - Become a PHP Master - CMS Project

☞ Learn Object Oriented PHP By Building a Complete Website

☞ MEVP Stack Vue JS 2 Course: MySQL + Express.js + Vue.js +PHP

☞ Object Oriented PHP & MVC

☞ PHP OOP: Object Oriented Programming for beginners + Project

☞ Learn PHP Fundamentals From Scratch

☞ The Complete PHP MySQL Professional Course with 5 Projects

☞ The Complete JavaScript Course 2019: Build Real Projects!