Ideas & Design

This week's assignment is part of my final project. Recall that I want to make a skateboard with a sensor that can send data of acceleration and geolocation(lat,lng) to the cloud while I am skating around the city. Therefore, I need a ESP32 board to post realtime data through wireless connection.

Circuit's schematic design PCB design

Server Setup

Though the ideal case is that I could host a server in the cloud, but for testing, I would like to host an Apache and MySQL server locally using XAMPP.

Local Apache & MySQL Service

Next, I create a database called mapping in phpMyAdmin and a table called bikelane in the database.

XAMPP dashboard Create a database and a table

Modifying Database

The simplest operation of modifying a database with SQL is to 1.INSERT 2.SELECT data. For this week's assignment, I just need to use the INSERT operation. SELECT will be useful for my Week12 assignment.

php provides us with a fast way to make changes in database with SQL syntax. I created a file called mapping_data.php at `F:\xampp\htdocs\bikelane_project`

                  
              
                
<?php

$hostname = "localhost";
$username = "root";
$password = "";
$database = "mapping";

$conn = mysqli_connect($hostname, $username, $password, $database);

if (!$conn){
die("Connection failed: " . mysqli_connect_error());
}

echo "Database connection is OK
";

if(isset($_POST["latitude"]) && isset($_POST["longitude"]) && isset($_POST["dx"]) && isset($_POST["dy"]) && isset($_POST["dz"])){
$lat = $_POST["latitude"];
$lng = $_POST["longitude"];
$dx = $_POST["dx"];
$dy = $_POST["dy"];
$dz = $_POST["dz"];

$sql = "INSERT INTO bikelane (lat, lng, dx, dy, dz) VALUES (" .$lat.", " .$lng.", " .$dx.", " .$dy.", " .$dz.")";

if(mysqli_query($conn, $sql)){
    echo "\nNew record created successfully";
}else{
    echo "Error: " .$sql . "
" . mysqli_error($conn);
}

}
?>
              

Communication between ESP32S3 and localhost server

WiFi module in Arduino makes it possible to connect ESP32 to any WiFi. All we need to provide are just SSID and password.

I wrote an Arduino code that asks the microcontroller to post data to the database each time it senses motion. It's done by modifying variables in the .php file mentioned above, which helps INSERT data to the bikelane database.

                  
//MPU6050
#include 
#include 
#include 
Adafruit_MPU6050 mpu;


//WiFi
#include 
#include 

String URL = "http://192.168.2.110/bikelane_project/mapping_data.php"; //"http://computer_ip/project_folder/project_file.php";

const char* ssid = "LEGION_TANG 0522";
const char* password = "V1655-w8";

float lat = 42.3601;
float lng = 71.0589;

void setup() {
//------------------------WiFi---------------------------
Serial.begin(115200);
connectWiFi();

//-----------------------MPU6050-------------------------
Serial.begin(115200);
while (!Serial)
delay(10); // will pause Zero, Leonardo, etc until serial console opens

Serial.println("Adafruit MPU6050 test!");

// Try to initialize!
if (!mpu.begin()) {
Serial.println("Failed to find MPU6050 chip");
while (1) {
  delay(10);
}
}
Serial.println("MPU6050 Found!");

//setupt motion detection
mpu.setHighPassFilter(MPU6050_HIGHPASS_0_63_HZ);
mpu.setMotionDetectionThreshold(1);
mpu.setMotionDetectionDuration(20);
mpu.setInterruptPinLatch(true);	// Keep it latched.  Will turn off when reinitialized.
mpu.setInterruptPinPolarity(true);
mpu.setMotionInterrupt(true);

Serial.println("");
delay(100);
}

void loop() {
//-----------------------WiFi--------------------------
if(WiFi.status() != WL_CONNECTED){
connectWiFi();
}


//---------------------MPU6050-------------------------
if(mpu.getMotionInterruptStatus()) {
/* Get new sensor events with the readings */
sensors_event_t a, g, temp;
mpu.getEvent(&a, &g, &temp);

/* Print out the values */
Serial.print("AccelX:");
Serial.print(a.acceleration.x);
Serial.print(",");
Serial.print("AccelY:");
Serial.print(a.acceleration.y);
Serial.print(",");
Serial.print("AccelZ:");
Serial.print(a.acceleration.z);
Serial.print(", ");
Serial.print("GyroX:");
Serial.print(g.gyro.x);
Serial.print(",");
Serial.print("GyroY:");
Serial.print(g.gyro.y);
Serial.print(",");
Serial.print("GyroZ:");
Serial.print(g.gyro.z);
Serial.println("");


/*Send the data to database*/
String postData = "latitude=" + String(lat) + "&longitude=" + String(lng) + "&dx=" +String(a.acceleration.x) + "&dy=" +String(a.acceleration.y) + "&dz=" +String(a.acceleration.z);

HTTPClient http;
http.begin(URL);
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
int httpCode = http.POST(postData);
String payload = http.getString();


Serial.print("URL: "); Serial.println(URL);
Serial.print("Data: "); Serial.println(postData);
Serial.print("httpCode: "); Serial.println(httpCode);
Serial.print("payload: "); Serial.println(payload);
Serial.println("------------------------------------------");
}

delay(10);
}

void connectWiFi(){
WiFi.mode(WIFI_OFF);
delay(1000);

Serial.print("Try to connect to ");
Serial.println(ssid);
WiFi.begin(ssid, password);

while (WiFi.status() != WL_CONNECTED){
delay(500);
Serial.print(".");
}
Serial.print("Connected to : "); Serial.println(ssid);
Serial.print("IP address: "); Serial.println(WiFi.localIP());
}
                                   

              

To be noticed, you have to get the IP address and put it into the above code. To get your IP address, simply run ipconfig in your cmd.exe.

IPv4 address

Testing!

The ESP32S3 is connecting a hotspot shared by my laptop (don't forget to change the bend to 2.4GHz, otherwise the ESP32S3 will not be able to connect to the WiFi). Though there is a cable connecting ESP32S3 and my laptop, it is just for reading message from Serial port to debug. Once I move the PCB, the accelerametor senses the motion and the microcontroller posts data through WiFi automatically.

Serial port messages New data posted from ESP32S3