database query problem

M.T.D

Problem is regarding a website retrieving data from database
Everything worked without problem until host "upgraded" to maria 10 .

Description of process
Client logs in to her page and is presented with a data table two columns, (quantities and prices) from the last updated row in the relevant database table.
Clients enters new data into table as needed then clicks save.
This adds a new row to the database table
Values saved are `timestamp`, `type`, `location`, `prices` (with a price for each quantity entered)

The website page via a form extracts data from the table and based on type, location and quantity and quotes a price.

Until the server changes the website form looked up the latest database table row.
After the "update" it returns data from the first row only.
I think it might have to do with the 'timestamp'
Extracts from the code
The "save"
if(in_array($_POST, $locations) && in_array($_POST, $producttypes))
{
$location = $_POST;
$fueltype = $_POST;
$time = time();
$prices = json_decode($_POST);
$prices_save = array();
foreach($prices as $p)
{
$prices_save[] = array('range' => intval($p[0]), 'price' => number_format($p[1], 10, '.', ''));
}
print_r($prices_save);
query("INSERT INTO `db_table` (`timestamp`, `type`, `location`, `prices`) VALUES ('".$time."', '".$producttype."', '".$location."', '".serialize($prices_save)."')",'u');
}
else
{
echo'error';
}

Code that supplies data to form on webpage
$sql = "SELECT * FROM (SELECT * FROM `db_table` ORDER BY `timestamp` DESC) as tmp WHERE `type` = 'producta' GROUP BY `location` ORDER BY `timestamp` DESC";
$k = query($sql, '');
while($a=mysql_fetch_array($k))
{
$products[0][$a][]=array('range' => 'Select Quantity', 'price' => 0);
$aa = unserialize($a);
foreach($aa as $aaa)
{
$products[0][$a][]=array('range' => $aaa, 'price' => $aaa);
}
}
The 'timestamp' data is in this form 1448550323


As a stopgap I copied the current data row into the first row of the db table so although the prices are now correct the client can't update them.


Any insights would be appreciated.


Update from hosting company
To our regret, after deeper investigation it was detected that some MySQL queries are not being performed by the system as expected due to some differences in their interpretation by the newly installed Maria DB. In order to resolve the issue, the script needs to be reviewed by a web developer to be adapted to Maria DB.

We are deeply sorry for all the inconveniences caused by the update.

Talisman

There are no breaking changes in upgrading the database version. Your issue is most likely that you are using obsolete PHP code to query the database.

mysql_fetch_array() was the function to query MySQL in the 1990s. It was deprecated in PHP5.5 which reached it's end of life in 2016. My guess would be that your hosting company has upgraded to a version of PHP7 and the functionality is broken because the function is obsolete.

In 2000/2001, the mysqli extension became the recommended method to query a MySQL database and a few years ago, PHP Data Objects (PDO) became the recommended method for accessing databases in PHP.

Your easiest solution is to update the code to use mysqli, here's a guide that can help you: How to Convert MySQL to MySQLi PHP Code to Upgrade to PHP7

M.T.D

Thank you for your response Talisman. As you say and from searching online the problem is obsolete PHP code. The ORDER BY in a FROM Subquery seems to be the "broken" part
I will read the article you have referenced and find the correct way of doing it, no point in doing a bit of editing to make it work with the database if it is going to break again at the next upgrade.
If I can't find a solution, I'll come back for further help