Hi, I hope it is ok to ask this here.
This is a problem on a site I maintain, It worked correctly until updates a while ago.
It is a company selling 2 products per litre. Both products are in the same table but each has its own row. When they update the per litre price a new row is added to the database. This still works, (it also mentions location but they only deliver to one county at the moment)
This saves the price
<?
include('config.php');
if(in_array($_POST['location'], $locations) && in_array($_POST['fueltype'], $fueltypes))
{
$location = $_POST['location'];
$fueltype = $_POST['fueltype'];
$time = time();
$prices = json_decode($_POST['prices']);
$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 `zzz_oilprices` (`timestamp`, `type`, `location`, `prices`) VALUES ('".$time."', '".$fueltype."', '".$location."', '".serialize($prices_save)."')",'u');
}
else
{
echo'error';
}
?>
But the line of code that used to check the table for the latest entry now only gets the price from the first row in the table rather than the latest row for that product.
function query($query, $type)
{
$rs = mysql_query($query) or die(mysql_error());
if ($type == 'u') { return; }
if ($type == 'n'){ $num = mysql_num_rows($rs); if ($num == 0){ return 0; }else{ return $num; } }
if ($type == "1") {
$data=mysql_fetch_array($rs);
return $data;
}
else { return $rs; }
}
function Prices()
{
$products = array(0=>array('name'=>'Kerosene', 'locations'=>array()), 1=>array('name'=>'Gas', 'locations'=>array()));
$sql = "SELECT * FROM (SELECT * FROM `xxx_oilprices` ORDER BY `id` DESC) as tmp WHERE `type` = 'kero' GROUP BY `location` ORDER BY `id` DESC";
$k = query($sql, '');
while($a=mysql_fetch_array($k))
{
$products[0]['locations'][$a['location']][]=array('range' => 'Select Quantity', 'price' => 0);
$aa = unserialize($a['prices']);
foreach($aa as $aaa)
{
$products[0]['locations'][$a['location']][]=array('range' => $aaa['range'], 'price' => $aaa['price']);
}
}
$sql = "SELECT * FROM (SELECT * FROM `xxx_oilprices` ORDER BY `timestamp` DESC) as tmp WHERE `type` = 'gas' GROUP BY `location` ORDER BY `timestamp` DESC";
$g = query($sql, '');
while($b=mysql_fetch_array($g))
{
$products[1]['locations'][$b['location']][]=array('range' => 'Select Quantity', 'price' => 0);
$bb = unserialize($b['prices']);
foreach($bb as $bbb)
{
$products[1]['locations'][$b['location']][]=array('range' => $bbb['range'], 'price' => $bbb['price']);
}
}
return $products;
}
if($_POST['action']=='dropdown' && is_numeric($_POST['update_fueltype']) && $_POST['county']!='')
{
$products = Prices();
if(is_array($products[$_POST['update_fueltype']]['locations'][strtolower($_POST['county'])]))
{
foreach($products[$_POST['update_fueltype']]['locations'][strtolower($_POST['county'])] as $l)
{
echo'<option value="'.$l['range'].'">'.$l['range'].'</option>';
}
}
else
{
echo'<option value="0">Unavailable</option>';
}
}
if(is_numeric($_POST['update_quantity']) && is_numeric($_POST['update_fueltype']) && $_POST['update_county']!='')
{
$products = Prices();
echo CalculateCost(intval($_POST['update_quantity']), intval($_POST['update_fueltype']), $_POST['update_county'], $products);
}
function CalculateCost($quantity, $fueltype, $county)
{
$p = Prices();
if(!$p){ $p=$products; }
if(is_numeric($quantity) && is_numeric($fueltype))
{
foreach($p[$fueltype]['locations'][strtolower($county)] as $p)
{
if($p['range'] == $quantity)
{
return number_format($p['price']*$quantity, 2, '.', '');
}
}
}
else
{
return 'An error occured while calculating your quote';
}
}
function FuelName($fueltype)
{
$p = Prices();
return $p[$fueltype]['name'];
}
This might be the problem line
$sql = "SELECT * FROM (SELECT * FROM `zzz_oilprices` ORDER BY `timestamp` DESC) as tmp WHERE `type` = 'gas' GROUP BY `location` ORDER BY `timestamp` DESC";
This was updates to the hosting not wordpress
Any help appreciated.
Mod edit: added tags around code to improve formatting; replace 2 instances of database prefix with “zzz”.