Tutorial ESP32 Databases to Control Anything Anywhere Section 4 - PHP Script to Insert Data in Dbase
Now, let’s write the PHP script to insert data into our database. This script will receive data from the ESP32 via HTTP POST requests and then insert this data into the MySQL database.
Here’s the PHP script (`insert_data.php`):
```php
(?php
$servername = "your_servername"; // Replace with your server name
$username = "your_username"; // Replace with your database username
$password = "your_password"; // Replace with your database password
$dbname = "your_dbname"; // Replace with your database name
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn-connect_error) {
die("Connection failed: " . $conn-connect_error);
}
// Get data from POST request
$sensor_value = $_POST["sensor_value"];
// Prepare and bind
$stmt = $conn-prepare("INSERT INTO sensor_data (sensor_value, timestamp) VALUES (?, NOW())");
$stmt-bind_param("d", $sensor_value);
// Execute the statement
if ($stmt-execute()) {
echo "New record created successfully";
} else {
echo "Error: " . $stmt-error;
}
$stmt-close();
$conn-close();
?
```
Explanation:
1. **Database Credentials**:
Replace `"your_servername"`, `"your_username"`, `"your_password"`, and `"your_dbname"` with your actual database credentials.
2. **Connection**:
The script establishes a connection to the MySQL database using the provided credentials.
3. **Error Handling**:
It checks if the connection was successful and terminates the script if there’s an error.
4. **Data Retrieval**:
`$_POST["sensor_value"]` retrieves the sensor value sent from the ESP32 via a POST request. Make sure the ESP32 sends the data with the key `"sensor_value"`.
5. **Prepared Statement**:
A prepared statement is used to prevent SQL injection. The SQL query inserts the `sensor_value` and the current timestamp into the `sensor_data` table.
6. **Binding Parameters**:
`$stmt-bind_param("d", $sensor_value);` binds the `$sensor_value` to the prepared statement. The `"d"` indicates that the `$sensor_value` is a double (or float). Make sure this matches the data type of your sensor value.
7. **Execution**:
`$stmt-execute()` executes the prepared statement.
8. **Response**:
The script echoes a success or error message based on whether the insertion was successful.
9. **Closing Connection**:
The script closes the statement and the database connection.
Deploying the Script:
1. **Upload to Web Server**:
Save the script as `insert_data.php` and upload it to your web server in a directory accessible by the web. For example, `/var/www/html/`.
2. **Permissions**:
Ensure that the web server has the necessary permissions to read and execute the PHP file.
With this PHP script, your database is ready to receive data from your ESP32. Next, we’ll modify the ESP32 code to send data to this script.
#robot #robotics #robots #robo #robotic #roboticautomation #roboticaeducativa #robotgames #robotica #microcontroller #mikrokontroler #mikrotik #arduino #arduinoproject #esp32 #esp32project #esp32projects