How to update specific rows by comparing timestamps?

Question

Hey I have an online database which needs to be populated with sensor values periodically. There are 7 variables(columns) coupled with a timestamp column to make each row of the table. Problem is these variables are pushed a few at a time with the rest as 0. Now I'm coding an algo which will only insert a row if all values of latest row (by timestamp) are filled completely(not 0), else it will update the latest row. Here's my PHP code for one of the insert php files which inserts 3 variables at a time - temp, pressure and altitude.

<?php

// Prepare variables for database connection

$dbusername = "uxpertla_terr";  // enter database username, I used "arduino" in step 2.2
$dbpassword = "abc123";  // enter database password, I used "arduinotest" in step 2.2
$server = "localhost"; 

// Connect to your database

$dbconnect = mysqli_connect($server, $dbusername, $dbpassword);

// Prepare the SQL statement
    $sdl = "SELECT * FROM uxpertla_test_db.testt ORDER BY time DESC LIMIT 1";
    $snd=mysqli_query($sdl);
    $row = mysqli_fetch_array($snd,MYSQLI_ASSOC);
    if($row)
    {
    if($row['Temperature']==0||$row['Pressure']==0||$row['Humidity']==0||$row['Light']==0)
    {
    $sql = "UPDATE uxpertla_test_db.testt 
    SET temp='".$_GET["value"]."',
    pressure='".$_GET["value2"]."',
    altitude='".$_GET["value3"]."'
    WHERE time=".strtotime($row['time'])."";
    }
    else
    $sql = "INSERT INTO uxpertla_test_db.testt 
    (temp,pressure,altitude) VALUES('".$_GET["value"]."', '".$_GET["value2"]."','".$_GET["value3"]."')"; 
    }
    else
    $sql = "INSERT INTO uxpertla_test_db.testt 
    (temp,pressure,altitude) VALUES('".$_GET["value"]."', '".$_GET["value2"]."','".$_GET["value3"]."')"; 

// Execute SQL statement

mysqli_query($sql);

?>

Sorry if the code's not pretty, but I need help with the where clause of update query in the second if block..

If you're targeting a time stamp, say in 'between' a period of set time, you could use the SQL Between keyword. WHERE timeValue is between 'timeOne' and 'timeTwo'. Sorry about the free hand — Dylan Wright, Mar 31 '17 at 17:20
You're using unmaintained, insecure, deprecated database functions. PHP has been encouraging the use of alternatives for more than a decade. Just stop! http://stackoverflow.com/q/12859942/1255289 — miken32, Mar 31 '17 at 17:22
i'm aware of the deprecation, but all of the mysql functions work. They're not the problem. The problem is the where clause of the update statement. Please help me with that line.. — Jojo Thomas, Mar 31 '17 at 17:25
what does `mysql_error()` return on the query/queries? and php's error reporting http://php.net/manual/en/function.error-reporting.php — Funk Forty Niner, Mar 31 '17 at 17:30
can u answer me a question jojo? Why are so much indian developers asking a question, but before that they upload a profile picture. Thats incredible! — user2659982, Mar 31 '17 at 17:42

score 0 · Answer 1 · answered Mar 31 '17 at 18:41

Figured out the answer.. :p

<?php

// Prepare variables for database connection

$dbusername = "uxpertla_terr";  // enter database username, I used "arduino" in step 2.2
$dbpassword = "abc123";  // enter database password, I used "arduinotest" in step 2.2
$server = "localhost"; 

// Connect to your database

$dbconnect = mysqli_connect($server, $dbusername, $dbpassword);

    $sdl = "SELECT * FROM uxpertla_test_db.testt ORDER BY time DESC LIMIT 1";
    $snd=mysqli_query($dbconnect,$sdl);
    $row = mysqli_fetch_array($snd,MYSQLI_ASSOC);
    if($row['Temperature']==0||$row['Pressure']==0||$row['Humidity']==0||$row['Light']==0)
    {
    $sql = "UPDATE uxpertla_test_db.testt 
    SET temp='".$_GET["value"]."',
    pressure='".$_GET["value2"]."',
    altitude='".$_GET["value3"]."'
    ORDER BY time DESC LIMIT 1";        
    }
    else
    $sql = "INSERT INTO uxpertla_test_db.testt 
    (temp,pressure,altitude) VALUES('".$_GET["value"]."', '".$_GET["value2"]."','".$_GET["value3"]."')"; 

// Execute SQL statement

mysqli_query($dbconnect,$sql);

?>

How to update specific rows by comparing timestamps?

1 Answers1