JGaudette
10/7/2010 - 12:45 PM

gistfile1.php

gistfile1.php
content_copyfile_download
<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>