pulling values from joined tables doing math then ordering via value.

[pugsx]

Hello, I have written a small bit of code that pulls some values from 2 tables and want to do some simple maths then display them on a page but in order of value.

 

here is the code so far.

<?php
include ("inc/db.inc.php");
$date = date('Y-m-d');
$game_id = $_GET[gid];
if ($game_id == ""){
$game_id = 1;
}
echo $date . "<br>";
$query ="SELECT * FROM games WHERE game_id = $game_id";
$result1 = mysql_query($query);
$row = mysql_fetch_array($result1);
echo $row['game_name'] . "<br>";
echo '<img height="100px" width="100px" src="thumbs/' . $row['thumb'] .'"><br>';
echo wordwrap($row['details'],100, "<br />\n") . "<br>";

$query = "SELECT games.rrp, games.game_id, deals.offer1_t, deals.offer1_v, deals.expiry_date,deals.free_del, deals.retailer_id, retailers.retailer_id, retailers.retailer_name FROM deals JOIN games ON deals.game_id = games.game_id JOIN retailers ON retailers.retailer_id = deals.retailer_id WHERE deals.game_id = $game_id AND CURDATE() < deals.expiry_date ORDER BY deals.offer1_v DESC";
$result=mysql_query($query);
while ($row = mysql_fetch_alias_array($result, MYSQL_ASSOC)) {
$offer1t = $row['deals.offer1_t'];
//$rid=$row['deals.retailer_id'];
//offer 1 start
switch ($offer1t) {
case 1://percentage off
	$perc = ".".round((100 - $row['deals.offer1_v']) /10);
	$price1 = round($row['games.rrp'] * $perc, 2);
	$oprice = $row['deals.offer1_v'] . "% off";
break;
case 2://money off
	$price1 = $row['games.rrp'] - $row['deals.offer1_v'];
	$oprice = "" . $row['deals.offer1_v'] . " off";
break;
case 3://unknown
$price = "poo";
break;
}//offer 1 stop
if ($row['deals.free_del'] == 1)
{
$fdel = '<img height="20px" width="100px" src="img/freedel.png">';
}else if ($row['deals.free_del'] == 0)
{
$fdel = '';
}
$psaved = ($row['games.rrp'] / 100) ;
if ($date > $row['deals.expiry_date']){
$valid = "<font color='red'>False</font>";
}else{
$valid = "<font color='green'>True</font>";
}
echo "<br> Retailer = " . $row['retailers.retailer_name'] . " | rrp = £" . $row['games.rrp'] . " | offer price = £" . $price1 . " | saving " . $oprice . " | Valid = " .$valid . "(" . $row['deals.expiry_date'] . ")";
}

function mysql_fetch_alias_array($result)
{
    if (!($row = mysql_fetch_array($result)))
    {
        return null;
    }

    $assoc = Array();
    $rowCount = mysql_num_fields($result);
   
    for ($idx = 0; $idx < $rowCount; $idx++)
    {
        $table = mysql_field_table($result, $idx);
        $field = mysql_field_name($result, $idx);
        $assoc["$table.$field"] = $row[$idx];
    }
   
    return $assoc;
}

?>

 

I would like to order the results with the $price1 variable if that is possible but I can't seem to get it to work.

[kickstart]

Hi

 

You need to either move the calculation into the SQL, or alternatively (and less efficiently) move all the retrieved details into an array, loop around the array doing the calculations and then sort the array.

 

Assuming doing it in SQL, something like this:-

 

SELECT games.rrp, games.game_id, deals.offer1_t, deals.offer1_v, deals.expiry_date,deals.free_del, deals.retailer_id, retailers.retailer_id, retailers.retailer_name,
CASE deals.offer1_t WHEN 1 THEN round(games.rrp * round((100 - deals.offer1_v)/10)/10,2) WHEN 2 THEN games.rrp - deals.offer1_v ELSE 0 END AS DiscountPrice
FROM deals JOIN games ON deals.game_id = games.game_id 
JOIN retailers ON retailers.retailer_id = deals.retailer_id 
WHERE deals.game_id = $game_id AND CURDATE() < deals.expiry_date 
ORDER BY 10 DESC

 

All the best

 

Keith

[pugsx]

Thank you very much that worked a treat, a quick follow on question if I have more than 1 entry with the same game_id how would I change the code to make it show only the cheapest od the duplicate entries?

 

 

Cheers Ash.

[kickstart]

Hi

 

You would need to do a subselect to find the cheapest game for each id, and then join that with the existing select.

 

Do you really have duplicate games with different prices? To you have a field that identifies the particular copy?

 

Quick try and something like this might work. Might be a more efficient way to do it though, rather than deriving the same column twice.

 

SELECT *
FROM (SELECT games.rrp, games.game_id, deals.offer1_t, deals.offer1_v, deals.expiry_date,deals.free_del, deals.retailer_id, retailers.retailer_id, retailers.retailer_name,
CASE deals.offer1_t WHEN 1 THEN round(games.rrp * round((100 - deals.offer1_v)/10)/10,2) WHEN 2 THEN games.rrp - deals.offer1_v ELSE 0 END AS DiscountPrice
FROM deals JOIN games ON deals.game_id = games.game_id 
JOIN retailers ON retailers.retailer_id = deals.retailer_id 
WHERE deals.game_id = $game_id AND CURDATE() < deals.expiry_date ) Deriv1
JOIN (SELECT games.game_id, MIN(CASE deals.offer1_t WHEN 1 THEN round(games.rrp * round((100 - deals.offer1_v)/10)/10,2) WHEN 2 THEN games.rrp - deals.offer1_v ELSE 0 END) AS DiscountPrice
FROM deals JOIN games ON deals.game_id = games.game_id
GROUP BY games.game_id) Deriv2 ON Deriv1=game_id = Deriv2.game_id AND Deriv1=DiscountPrice = Deriv2.DiscountPrice
ORDER BY 10 DESC

 

All the best

 

Keith

[knsito]

wow... wish I could query like that lol

 

 

SELECT *
FROM (SELECT games.rrp, games.game_id, deals.offer1_t, deals.offer1_v, deals.expiry_date,deals.free_del, deals.retailer_id, retailers.retailer_id, retailers.retailer_name,
CASE deals.offer1_t WHEN 1 THEN round(games.rrp * round((100 - deals.offer1_v)/10)/10,2) WHEN 2 THEN games.rrp - deals.offer1_v ELSE 0 END AS DiscountPrice
FROM deals JOIN games ON deals.game_id = games.game_id 
JOIN retailers ON retailers.retailer_id = deals.retailer_id 
WHERE deals.game_id = $game_id AND CURDATE() < deals.expiry_date ) Deriv1
JOIN (SELECT games.game_id, MIN(CASE deals.offer1_t WHEN 1 THEN round(games.rrp * round((100 - deals.offer1_v)/10)/10,2) WHEN 2 THEN games.rrp - deals.offer1_v ELSE 0 END) AS DiscountPrice
FROM deals JOIN games ON deals.game_id = games.game_id
GROUP BY games.game_id) Deriv2 ON Deriv1=game_id = Deriv2.game_id AND Deriv1=DiscountPrice = Deriv2.DiscountPrice
ORDER BY 10 DESC

 

[kickstart]

Hi

 

Could probably be made simpler to read by setting up a view to get the discounted price for each game.

 

All the best

 

Keith

[pugsx]

Thank you for your help I've made a few changes to the database to make things a little easier, it now has the reduced price of the game

 

SELECT games.rrp, games.game_name, games.game_id, games.thumb, games.details, games.rls_date, games.game_url, games.platform, deals2.deal_id, deals2.price, deals2.expiry_date, deals2.free_del FROM games LEFT JOIN deals2 ON games.game_id = deals2.game_id WHERE games.platform = '$platform' GROUP BY games.game_id ORDER BY games.game_id ASC limit 10

 

I would like it to show only the lowest deals2.price of each game_id

 

I tried this

 

SELECT games.rrp, games.game_name, games.game_id, games.thumb, games.details, games.rls_date, games.game_url, games.platform, deals2.deal_id, deals2.price, deals2.expiry_date, deals2.free_del FROM games LEFT JOIN deals2 ON games.game_id = deals2.game_id WHERE games.platform = '$platform' GROUP BY games.game_id, deals2.price ORDER BY games.game_id ASC limit 10

with no luck also this

 

SELECT games.rrp, games.game_name, games.game_id, games.thumb, games.details, games.rls_date, games.game_url, games.platform, deals2.deal_id, deals2.price, deals2.expiry_date, deals2.free_del FROM games LEFT JOIN deals2 ON games.game_id = deals2.game_id WHERE games.platform = '$platform' GROUP BY games.game_id HAVING MIN(deals2.price) ORDER BY games.game_id ASC limit 10

 

Again no luck.

 

Any ideas?

 

Ash