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.

Next, I create a database called mapping in phpMyAdmin and a table called bikelane in the 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);
      }
      
      }
      ?>
                    

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.

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.