COLLECT DATA WITH RASPBERRY PI AND ESP8266

Ca
11 min readAug 23, 2022

--

Here at Xekera Systems we care about our personnel, so we have decided to implement a system that lets them express the way they feel at the company every time they come into work.

The system consists of a board with four different buttons. Each button will represent one of the following moods: happy, sad, angry, neutral. When a button is pressed, the mood chosen by the employee will be sent by the ESP8266 module to a server mounted on a Raspberry Pi in order to be able to access the data on a web page.

For this project we are going to need the following components:

  • ESP8266–12 Wi-Fi module
  • Arduino UNO
  • Raspberry Pi
  • Jumper Wires
  • 330 Ohm Resistors (x7)
  • Push Buttons (x4)
  • LEDs (1 red, 1 green, 1 blue)
  • SD Card
  • USB Wi-Fi Module
  • HDMI Cable
  • Keyboard
  • Mouse
  • Monitor with HDMI input

Operating System for the Raspberry Pi

Before using the Raspberry Pi, you will need to install its operating system, if you have not done so already. You can refer to the following, the operating system that we installed for this tutorial:

Once the image has been downloaded, you need to download and install the following program to write the image on the SD card:

Insert the SD card in your computer, open Win32 Disk Imager, select the image that you downloaded, and click on Write. Once the image has been downloaded, take the SD card out and insert it in the Raspberry Pi.

Connect the HDMI cable to the Raspberry Pi and to the monitor. When you power up the Raspberry Pi, you will get the next window:

Raspberry Pi Configuration Window

Using the keyboard, select Expand Filesystem to make sure all of the SD card storage is available. You can configure other things, but we will not make other changes. Now click on Finish and when asked if you want to reboot now, select Yes.

USB Wi-Fi Module

Now that we have our Raspberry Pi up and running, we need to connect it to the internet. Insert the USB Wi-Fi Module into one of the USB ports on the Raspberry Pi, open the LXTerminal, and type the following command:

1

sudo nano /etc/network/interfaces

This will open a new window. Modify it so that it looks like this:

1

2

3

4

5

6

7

8

9

10

auto lo

iface lo inet loopback

iface eth0 inet dhcp

allow-hotplug wlan0

auto wlan0

iface wlan0 inet dhcp

wpa-ssid “YourNetwork”

wpa-psk “YourPassword”

Now hit “Ctrl+x” to exit the file, then “y” to save the changes, and finally “Enter” to go back to the main LXTerminal window. Once in the main window, type the next command to reload the network interface:

1

sudo service networking reload

That’s it, your Raspberry Pi is now connected to the internet. Now we just need to find the IP address of the Pi with the following command:

1

hostname -I

LAMP Setup on Raspberry Pi

In order to host our web page, we need to have LAMP (Linux, Apache, MySQL, and PHP). So far we only have Linux, which is our Raspberry Pi. So let’s install Apache and PHP first:

1

sudo apt-get install apache2 php5 libapache2-mod-php5

Now restart Apache:

1

sudo service apache2 restart

If you type in a web browser the IP address of your Raspberry Pi, then you should see a page that says: It works! This means that the installation was successful.

Use the following command to install MySQL:

1

sudo apt-get install mysql-server mysql-client php5-mysql

When asked if you want to continue, type “y” and then “Enter.” Then you will be asked to create a password for the MySQL root user. Once the installation is done our Raspberry Pi is ready to function as a server and host web pages.

MySQL DataBase

In order to keep track of the inputs, we are going to store them in a database so that we can access them later on. First we need to create a user to have access to the database. In the LXTerminal window type this command:

1

mysql -u root -p

Then type:

1

create user ‘username’@’localhost’ identified by ‘password’;

To create the database type the next command:

1

create database databasename;

Now we need to give the user the necessary privileges to access the database and let it do whatever it need on the database:

1

2

grant usage on *.* to ‘username’@’localhost’;

grant all on databasename.* to ‘username’@’localhost’;

We are now ready to create the table that will hold the inputs from the ESP module:

1

create table tablename (logdate DATE , mood VARCHAR(64));

This creates the following table:

So now as inputs are sent from the ESP module to our database, the columns will start to get filled with the moods selected by the employees and the date.

PHP Files

Now we are going to create two different PHP files. One will be used to store the data from the ESP module into our database, and the other will be used to access that data and create a pie chart based on the inputs. On the Raspberry Pi, navigate to the folder /var/www/, create a new folder, and name it “php.” Here is where we are going to save all of our php files.

Collect Data

1

<?php

1

$servername = “localhost”;

1

$username = “esp8266”;

1

$password = “Tutorial”;

1

$dbname = “esp8266”;

1

1

$mood = $_GET[‘mood’];

1

$conn = mysql_connect(“localhost”,”esp8266”,”Tutorial”);

1

if(!$conn)

1

{

1

die(‘Could not connect: ’ . mysql_error());

1

}

1

$datenow = date(‘Y-m-d’);

1

$sql = “INSERT INTO `JSDataTable`(`logdate`,`mood`) VALUES (\”$datenow\”,\”$mood\”)”;

1

$result = mysql_query($sql);

1

if(!result)

1

{

1

die(‘Invalid query: ‘ . mysql_error());

1

}

1

echo “<h1>The data has been sent!</h1>”;

1

mysql_close($conn);

1

?>

In this file we connect to the database “esp8266 ” and place the values of “mood” and “logdate” in the “JSDataTable.” The values will be obtained from the URL request sent from the client, which, in this case, is the ESP module. Save this file inside the php folder with the name “collectdata.php”

Display data

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

<?php

include(‘phpgraphlib.php’);

include(‘phpgraphlib_pie.php’);

$graph = new PHPGraphLibPie(1000,500);

$servername = ‘localhost’;

$username = ‘esp8266’;

$password = ‘Tutorial’;

$dbname = ‘esp8266’;

$sql = “Select * from JSDataTable where mood = ‘Happy’”;

$sql1 = “Select * from JSDataTable where mood = ‘Sad’”;

$sql2 = “Select * from JSDataTable where mood = ‘Angry’”;

$sql3 = “Select * from JSDataTable where mood = ‘Neutral’”;

$conn = mysql_connect(“localhost”, “esp82660”, “Tutorial”) or die (“Fail”);

mysql_select_db(“esp8266, “$conn”);

$result = mysql_query($sql, $conn);

$result1 = mysql_query($sql1, $conn);

$result2 = mysql_query($sql2, $conn);

$result3 = mysql_query($sql3, $conn);

$tothappy = mysql_num_rows($result);

$totsad = mysql_num_rows($result1);

$totangry = mysql_num_rows($result2);

$totneutral = mysql_num_rows($result3);

$data = array(“Happy” => $tothappy, “Sad” => $totsad, “Angry” => $totangry, “Neutral” => $totneutral);

$graph->addData($data);

$graph->setLabelTextColor(“black”);

$graph->setLegentTextColor(“black”);

$graph->setBackgroundColor(“pastel_green_1”);

$graph->createGraph();

mysql_close($conn);

?>

This file connects to the database “esp8266” and counts how many entries are in the “JSDataTable” for each mood. Then it uses the phpgraphlib to create a pie chart to represent each mood as a percentage. Save this file inside the php folder with the name “displaydata.php”

Graph Library

In order to display the data from the ESP module as a pie chart, we use the following library.

There are other libraries out there that you can use instead of this one if you want. Include the files that are downloaded inside the php folder.

HTML File

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

<html>

<head>

<style type=“text/css”>

.OutlineText

{

font-size: 55px;

color: white;

text-shadow:

-1px -1px 0 #000,

1px -1px 0 #000,

-1px 1px 0 #000,

1px 1px 0 #000,

}

</style>

</head>

<body style=“background-color:dodgerblue;”>

<div vlass=“OutlineText”><center>Employees at Jaycon Systems</center></div>

<center><top><img src =“displaydata.php” /></top><center>

</body>

</html>

This html file is used to display the pie chart created in “displaydata.php.” It is also used to create a title and set the background color of the web page. We saved this file as “Employees.html” inside the php folder.

ESP8266 Wiring

Complete Wiring Setup

Wiring Diagram

This circuit represents a prototype of the actual board setup.

Arduino Code

Remember to ground GPIO0 and GPIO15, and set GPIO2 high before downloading the code to the ESP module.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

#include <ESP8266WiFi.h>

const int buttonpin = 0;

const int buttonpin1 = 15;

const int buttonpin2 = 4;

const int buttonpin3 = 12;

const int red = 14;

const int green = 16;

const int blue = 13;

int buttonState = 0;

int buttonState1 = 0;

int buttonState2 = 0;

int buttonState3 = 0;

const char* ssid = "YourNetwork";

const char* password = "YourPassword";

const char* host = "YourIPAddress";

const char* streamId = "collectdata.php";

void sendRequest(String mood){

Serial.print("connecting to ");

Serial.println(host);

// Use WiFiClient class to create TCP connections

WiFiClient client;

const int httpPort = 80;

if (!client.connect(host, httpPort)) {

Serial.println("connection failed");

digitalWrite(red,HIGH);

delay(2000);

digitalWrite(red,LOW);

return;

}

digitalWrite(red,LOW);

// We now create a URI for the request

String url = "/php/";

url += streamId;

url += "?mood=";

url += mood;

Serial.print("Requesting URL: ");

Serial.println(url);

// This will send the request to the server

client.print(String("GET ") + url + " HTTP/1.1\r\n" +

"Host: " + host + "\r\n" +

"Connection: close\r\n\r\n");

delay(10);

digitalWrite(green,HIGH);

delay(2000);

digitalWrite(green,LOW);

Serial.println();

Serial.println("closing connection");

delay(2500);

}

void setup() {

Serial.begin(115200);

delay(10);

pinMode(buttonpin, INPUT);

pinMode(buttonpin1, INPUT);

pinMode(buttonpin2, INPUT);

pinMode(buttonpin3, INPUT);

pinMode(red, OUTPUT);

pinMode(green, OUTPUT);

pinMode(blue, OUTPUT);

digitalWrite(red,LOW);

digitalWrite(green,LOW);

digitalWrite(blue,LOW);

// We start by connecting to a WiFi network

Serial.println();

Serial.println();

Serial.print("Connecting to ");

Serial.println(ssid);

WiFi.begin(ssid, password);

while (WiFi.status() != WL_CONNECTED) {

delay(500);

Serial.print(".");

}

Serial.println("");

Serial.println("WiFi connected");

Serial.println("IP address: ");

Serial.println(WiFi.localIP());

digitalWrite(blue,HIGH);

}

void loop() {

buttonState = digitalRead(buttonpin);

buttonState1 = digitalRead(buttonpin1);

buttonState2 = digitalRead(buttonpin2);

buttonState3 = digitalRead(buttonpin3);

if(buttonState == HIGH)

else if (buttonState1 == HIGH)

else if (buttonState2 == HIGH)

else if (buttonState3 == HIGH)

}

Code Explanation

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

#include <ESP8266WiFi.h>

const int buttonpin = 0;

const int buttonpin1 = 15;

const int buttonpin2 = 4;

const int buttonpin3 = 12;

const int red = 14;

const int green = 16;

const int blue = 13;

int buttonState = 0;

int buttonState1 = 0;

int buttonState2 = 0;

int buttonState3 = 0;

const char* ssid = "YourNetwork";

const char* password = "YourPassword";

const char* host = "YourIPAddress";

const char* streamId = "collectdata.php";

Here we include the library required to set the ESP module as a client. We also assign pin numbers to the buttons and LEDs used on the protoboard. Then we write the name and password of our Wi-Fi network, the IP address of our Raspberry Pi, and the php file used to store data in our database.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

void sendRequest(String mood){

Serial.print("connecting to ");

Serial.println(host);

// Use WiFiClient class to create TCP connections

WiFiClient client;

const int httpPort = 80;

if (!client.connect(host, httpPort)) {

Serial.println("connection failed");

digitalWrite(red,HIGH);

delay(2000);

digitalWrite(red,LOW);

return;

}

digitalWrite(red,LOW);

// We now create a URI for the request

String url = "/php/";

url += streamId;

url += "?mood=";

url += mood;

Serial.print("Requesting URL: ");

Serial.println(url);

// This will send the request to the server

client.print(String("GET ") + url + " HTTP/1.1\r\n" +

"Host: " + host + "\r\n" +

"Connection: close\r\n\r\n");

delay(10);

digitalWrite(green,HIGH);

delay(2000);

digitalWrite(green,LOW);

Serial.println();

Serial.println("closing connection");

delay(2500);

}

This function serves to send URL requests to our server. First we connect to our Raspberry Pi. If the connection is successful, then we create the URL that is going to be requested. Otherwise, we make the red LED blink to let us know that the connection failed. Once the ESP connects to the Raspberry Pi, we send the URL request and blink the green LED to let us know that the information was sent.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

void setup() {

Serial.begin(115200);

delay(10);

pinMode(buttonpin, INPUT);

pinMode(buttonpin1, INPUT);

pinMode(buttonpin2, INPUT);

pinMode(buttonpin3, INPUT);

pinMode(red, OUTPUT);

pinMode(green, OUTPUT);

pinMode(blue, OUTPUT);

digitalWrite(red,LOW);

digitalWrite(green,LOW);

digitalWrite(blue,LOW);

// We start by connecting to a WiFi network

Serial.println();

Serial.println();

Serial.print("Connecting to ");

Serial.println(ssid);

WiFi.begin(ssid, password);

while (WiFi.status() != WL_CONNECTED) {

delay(500);

Serial.print(".");

}

Serial.println("");

Serial.println("WiFi connected");

Serial.println("IP address: ");

Serial.println(WiFi.localIP());

digitalWrite(blue,HIGH);

}

In the setup we set the baud rate to 115200 for serial communication. Then we set the buttons as inputs and the LEDs as outputs. Next we connect the ESP module to our Wi-Fi network. If the connection is successful, then we print the IP address and turn the blue LED on.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

void loop() {

buttonState = digitalRead(buttonpin);

buttonState1 = digitalRead(buttonpin1);

buttonState2 = digitalRead(buttonpin2);

buttonState3 = digitalRead(buttonpin3);

if(buttonState == HIGH)

else if (buttonState1 == HIGH)

else if (buttonState2 == HIGH)

else if (buttonState3 == HIGH)

}

In the loop we constantly check if one of the four buttons is pressed. Each button will create and send a different URL request with the mood associated with it.

Results

Now every time we press a button, the mood associated with that button will be stored in our database. If we type these commands in the LXTerminal window, then we can see the table created earlier with all the moods that have been selected.

1

2

3

mysql -u root -p

use esp8266;

select * from JSDataTable;

Database Entries

Now we just need to go to following web page to see the same information in graphic form:

1

192.168.1.118/php/Employees.html

192.168.1.118 is the IP address of our Raspberry Pi, php is the folder where we saved all of our php files, and Employees.html is the html file that we created, also inside the php folder, to display the data as a pie chart.

The convenient thing of having the Raspberry Pi running as a server is that we can connect more than one ESP module to it. So if we wanted, we could have another ESP measuring temperature, for example, and send the values to the Raspberry Pi to make them accessible on the Internet.

If you have any questions about this tutorial, don’t hesitate to post a comment, shoot us an email, or post it in our forum!

--

--