Skip to content

Instantly share code, notes, and snippets.

@JGaudette

JGaudette/gist:615046

Created October 7, 2010 12:45
Show Gist options
  • Save JGaudette/615046 to your computer and use it in GitHub Desktop.
Save JGaudette/615046 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
gistfile1.php
<html>
<head>
<title>A Little Bit of PHP</title>
<style type="text/css">
td {font-size: 8pt; font-family: Arial;}
</style>
</head>
<?php
mysql_connect('localhost', 'USERNAME', 'PASSWORD');
@mysql_select_db('amazon') or die("Unable to select database");
#$query = "call gen_report();";
$query = 'select * from products p left join ( select p.asin, p.price as min_price, max(p.creation_date) as min_date from (select asin,min(price) as price from prices group by asin) as x inner join prices as p on x.asin = p.asin and x.price = p.price group by asin ) as A on p.asin = A.asin left join ( select p.asin, p.price as max_price, max(p.creation_date) as max_date from (select asin,max(price) as price from prices group by asin) as x inner join prices as p on x.asin = p.asin and x.price = p.price group by asin) as B on A.asin = B.asin left join ( select p.asin, p.price as curr_price, p.creation_date as curr_date from prices p where id in (select max(id) from prices group by asin) ) as C on B.asin = C.asin';
$result = mysql_query($query);
mysql_close();
$num = mysql_numrows($result);
?>
<body>
<table border="1">
<tr>
<th>ASIN</th>
<th>NAME</th>
<th>LOWEST</th>
<th>DATE</th>
<th>HIGHEST</th>
<th>DATE</th>
<th>CURRENT</th>
<th>DATE</th>
</tr>
<?php
$i = 0;
while($i < $num){
$min_price = mysql_result($result, $i, "min_price");
$max_price = mysql_result($result, $i, "max_price");
$curr_price = mysql_result($result, $i, "curr_price");
if(($min_price == $curr_price) && ($min_price != $max_price)){
print '<tr style="background-color: lightgreen;">';
}else if(($max_price == $curr_price) && ($min_price != $max_price)){
print '<tr style="background-color: pink;">';
}else if(($max_price > $curr_price) && ($min_price < $curr_price)){
print '<tr style="background-color: #eeee44;">';
}else{
print "<tr>";
}
print "<td>" . mysql_result($result, $i, "asin") . "</td>";
print "<td>" . mysql_result($result, $i, "name") . "</td>";
print "<td>" . $min_price . "</td>";
print "<td style=\"background-color: #eeeeee;\">" . mysql_result($result, $i, "min_date") . "</td>";
print "<td>" . $max_price . "</td>";
print "<td style=\"background-color: #eeeeee;\">" . mysql_result($result, $i, "max_date") . "</td>";
print "<td>" . $curr_price . "</td>";
print "<td style=\"background-color: #eeeeee;\">" . mysql_result($result, $i, "curr_date") . "</td>";
print "</tr>\n";
$i++;
}
mysql_free_result($result); //we're done using the results, so set it free
?>
</table>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment