SQL dont't match float with Comma

Question

I'm probably making another rookie mistake. But can anyone help me?

I would like to create an instant cancellation function for our mobile POS system. This also works with whole numbers. The query does not work for numbers with a decimal place.

I have already checked that the numbers in the database and in the query are completely identical. But I still don't get a result back. What else could be the problem?

I use Joomla. But since it is probably not a Joomla problem, I am posting it here. The database is MariaDB.

Here is the code:

private function getAffectedRow($item): object|null
{

    $AmountArray = explode(';', $item->PriceCheck);
    $book        = floatval($AmountArray[0]);
    $other       = floatval($AmountArray[1]);
    $total       = floatval($AmountArray[2]);

    $db = Factory::getContainer()->get('DatabaseDriver');

    $query = $db->getQuery(true);

    // Die Abfrage formulieren
    $query->select(array('id', 'time', 'amount_total'));
    $query->from('#__ok_entries');
    $query->where($db->quoteName('amount_books') . ' = ' . $db->quote($book) . ' AND ' . $db->quoteName('amount_other') . ' = ' . $db->quote($other) . ' AND ' . $db->quoteName('amount_total') . ' = ' . $db->quote($total));
    $query->order('id DESC');
    $query->setLimit(1);
    // Daten lesen
    $db->setQuery($query);

    return $db->loadObject();


}

Thanks for any advice!

Answer 1

Many values cannot be represented exactly as floats; avoid such values in both your php code and mariadb storage.

On the php side, you need to format your value as a string with up to two decimal places. So do something like:

$other       = round( floatval($AmountArray[1]), 2);

In mariadb, ensure that your column does not have a FLOAT or DOUBLE type. Instead use something like DECIMAL(20,2) that can accurately store your amounts with two decimal places.