Hi, I have some code to get data from a database. It didn't happen before, but I now get an HTTP error sometimes when I request data. Is there anything that I am not seeing that is causing it?
Here I have the database code:
String uploadData(String row, float value) {
String query = "UPDATE `Snuffel` SET `" + row + "`=" + String(value) + " WHERE Naam = 'Joanne'";
return executeQuery(query);
}
String downloadData(String row, bool value) {
String naam;
if (value){
naam = "Joanne";
} else {
naam = "Ruben";
}
String query = "SELECT " + row + " FROM Snuffel WHERE Naam = '" + naam + "'";
return executeQuery(query);
}
String executeQuery(String query) {
String returnValue = "";
String postData = "query=" + query + "&key=secret";
Serial.println(postData);
int httpCode = http.POST(postData);
if (httpCode > 0) { // Controleer op een succesvolle verbinding
if (httpCode == HTTP_CODE_OK || httpCode == 201) { // Als de verbinding OK is
String payload = http.getString(); // Ontvang de payload (response body)
// Zoek de eerste |/| om het sleutelgedeelte over te slaan
int lineIndex = payload.indexOf("|/|");
if (lineIndex != -1) {
// Sla het sleutelgedeelte over
lineIndex += 3; // Spring over het scheidingsteken naar het begin van de waarde
// Vind het tweede |/| om het waardegedeelte te krijgen
int nextIndex = payload.indexOf("|/|", lineIndex);
if (nextIndex != -1) {
// Ontvang de waarde
returnValue = payload.substring(lineIndex, nextIndex);
// Toon de waarde
}
}
} else {
Serial.print("Error on HTTP request. HTTP code: ");
Serial.println(httpCode);
}
} else {
Serial.print("Error on HTTP request. HTTP code: ");
Serial.println(httpCode);
}
return returnValue;
}
void getData() {
String newData = downloadData("NewData", Joanne);
if (newData.toInt() == 1) {
Serial.println("Downloading data...");
data.putString("Lkleur", downloadData("Lkleur", Joanne));
data.putString("Kkleur", downloadData("Kkleur", Joanne));
data.putInt("SlaapTijd", downloadData("SlaapTijd", Joanne).toInt());
data.putInt("OntwaakTijd", downloadData("OntwaakTijd", Joanne).toInt());
data.putInt("SlaapDuratie", downloadData("SlaapDuratie", Joanne).toInt());
uploadData("NewData", 0);
}
}
Other relevant code:
bool isNear() {if (digitalRead(D8) == HIGH){return true;}else{return false;}}
bool isCharging() {if (digitalRead(D0) == LOW){return true;}else{return false;}}
bool afterSleep() {if (data.getInt("SlaapTijd") < now()){return true;}else{return false;}}
bool beforeWake() {if (now() < data.getInt("OntwaakTijd")){return true;}else{return false;}}
void looped() {
if (count == 11) {
if (isCharging()) {
getData();
}
if (active && downloadData("Slapen", !Joanne).toInt() == 1) {
setLed(data.getString("Lkleur"));
} else if (active){
setLed(data.getString("Kkleur"));
} else {
setLed("000000");
}
count = 0;
}
count++;
}
void setup(){
http.begin("my url");
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
}
void loop() {
delay(1000);
data.begin("Snuffel", false);
server.handleClient();
looped();
if (isWiFiActive()) {
if (isNear() && !afterSleep() && !active) {
Serial.println("Touched!");
offTime = data.getInt("SlaapDuratie") + now();
active = true;
setLed(data.getString("Kkleur"));
if (offTime > 1440) {
offTime -= 1440;
}
Serial.print("Led turning on... offTime: ");
Serial.println(offTime);
} else if ((minutesUntil(offTime) > data.getInt("SlaapDuratie")) && !afterSleep()) {active = false;}
} else {connectWifi();}
data.end();
}
And my php script on the webserver:
<?php
/*
* Written By: ShivalWolf
* Date: 2011/06/03
* Contact: Shivalwolf@domwolf.net
*
* UPDATE 2011/04/05
* The code now returns a real error message on a bad query with the mysql error number and its error message
* checks for magic_quotes being enabled and strips slashes if it is. Its best to disable magic quotes still.
* Checks to make sure the submitted form is a x-www-form-urlencode just so people dont screw with a browser access or atleast try to
* Forces the output filename to be JSON to conform with standards
*
* UPDATE 2011/06/03: Code updated to use the Web Module instead of tinywebdb
*
* UPDATE 2013/12/26: minor modifications by Taifun
*
* UPDATE 2014/07/11: mysql API (deprecated) replaced by mysqli by Taifun
*
* UPDATE 2015/04/30: SELECT logic adjusted (result stored in temp. file removed) by Taifun
*
* UPDATE 2016/02/21: Bugfix Undefined variable: csv by Taifun
*
* UPDATE 2024-04-24: php 8 error handling update by Taifun
*/
/************************************CONFIG****************************************/
//DATABSE DETAILS//
$DB_ADDRESS="my db";
$DB_USER="my db";
$DB_PASS="my db";
$DB_NAME="my db";
//SETTINGS//
//This code is something you set in the APP so random people cant use it.
$SQLKEY="secret";
/************************************CONFIG****************************************/
//these are just in case setting headers forcing it to always expire
header('Cache-Control: no-cache, must-revalidate');
error_log(print_r($_POST,TRUE));
if( isset($_POST['query']) && isset($_POST['key']) ){ //checks if the tag post is there and if its been a proper form post
//set content type to CSV (to be set here to be able to access this page also with a browser)
header('Content-type: text/csv');
if($_POST['key']==$SQLKEY){ //validates the SQL key
$query=urldecode($_POST['query']);
try {
$conn = new mysqli($DB_ADDRESS,$DB_USER,$DB_PASS,$DB_NAME); //connect
try {
$result=$conn->query($query); //runs the posted query
if (strlen(stristr($query,"SELECT"))>0) { //tests if it's a SELECT statement
$csv = ''; // bug fix Undefined variable: csv
while ($fieldinfo = $result->fetch_field()) {
$csv .= $fieldinfo->name."|/|";
}
$csv = rtrim($csv, ",")."\n";
echo $csv; //prints header row
$csv = '';
$result->data_seek(0);
while($row = $result->fetch_assoc()){
foreach ($row as $key => $value) {
$csv .= $value."|/|";
}
$csv = rtrim($csv, ",")."\n";
}
echo $csv; //prints all data rows
} else {
header("HTTP/1.0 201 Rows");
echo "AFFECTED ROWS: " . $conn->affected_rows; //if the query is anything but a SELECT, it will return the number of affected rows
}
} catch (mysqli_sql_exception $e) {
header("HTTP/1.0 400 Bad Request"); //sends back a bad request error
echo "SQL statement: " . $query . "; Error: " . $e->getMessage(); // errors if the query is bad and spits the error back to the client
}
$conn->close(); //closes the DB
} catch (mysqli_sql_exception $e) {
header("HTTP/1.0 400 Bad Request"); //sends back a bad request error
echo "Error connecting database: " . $e->getMessage();
}
} else {
header("HTTP/1.0 400 Bad Request");
echo "Bad Request, Error code 02"; //reports if the secret key was bad
}
} else {
header("HTTP/1.0 400 Bad Request");
echo "Bad Request, Error code 01";
}
?>
