SQL Trigger to delete outliters

Question

I am sending values to a php script on the same server as a MySQL database. A php script sends the values to the database. However, somewhere along the path sometimes a value loses a decimal and I end up with an extremely high value.

I am monitoring PH and a few other things so the number is relatively low (between 4-12). Sometimes I end up with 720 instead of 7.20. Should this be corrected through a trigger? If so how? Or should it be handled on the PHP side? How can I not accept values or render them null when entering? Thank you for your time.

<!DOCTYPE html>
<html> 
    <body>
        <h1></h1>
        <?php
        // Create connection
        $con = mysqli_connect("localhost", "***", "***", "***");
        // Check connection
        if (mysqli_connect_errno()) {
            echo "Failed to connect to MySQL: " . mysqli_connect_error();
        }

        $temp1 =  ( $_GET["1"]  * 9/5)+32;
        $temp2 =  ( $_GET["3"] *   9/5)+32;

        if ($_SERVER["REQUEST_METHOD"] == "GET") {
            $sql = "INSERT INTO `ANALOG_SENSORS` (`TEMPERATURE`, `HUMIDITY`, `TEMPERATURE2`, `HUMIDITY2`,`PH`,`DATE_TIME`)
            VALUES (" . $temp1 . ", " . $_GET["2"] . ", " . $temp2 . ", " . $_GET["4"] . ", " . $_GET["5"] . ", 
            NOW()- INTERVAL 7 HOUR)"; 
            $result = mysqli_query($con, $sql);
        }


    </body>
</html>

Note that you appear **wonderfully** open to SQL Injection, your db will be taken over by automated tools. You should be using [parameterized statements](http://stackoverflow.com/a/60496/812837), or suffer the consequences. — Clockwork-Muse, Aug 05 '14 at 03:55
"Should this be corrected through a trigger?". In my opinion: no. If you lose the decimal during insertion into the database, you should fix that. If you get an outlier due to measurement error, you should store it as you get it. When you go and process the data, you may want to include it, try to fix it, or filter it out. Also a trigger will not suffice in more complicated filters, e.g. first collect all data and then filter out anything beyond 3 std.dev. Besides keeping the values allows you to run statistics later, such as determining the number of read errors over time. — CompuChip, Aug 05 '14 at 11:41

score 1 · Accepted Answer · answered Aug 05 '14 at 03:45

Floating-point variables (float / double) should be handled as floating-point variable.

In MySQL and PHP, valid floating-point number are numbers with a dot as separator between integer and decimal part.

var_dump(floatval('13.37')) => double(13.37)
var_dump(floatval('13,37')) => double(13)

As you can see, with a coma, the decimal part is cut off, MySQL act exactly like this.

You have to be sure that your input data is formatted as is has to be.

$ph = str_replace(',', '.', $_GET['5']);

In a MySQL query, a coma is reserved character, if your data include a coma and isn't in a string (like in your case), the coma will be interpreted as separator, and add a new column to the insert query.

SELECT 1.2   => 1.2
SELECT 1,2   => [1, 2]

Also, make sure that your MySQL table fields' type are correctly set to float/double.

SQL Trigger to delete outliters

1 Answers1