I'm trying to figure out the best way to accomplish the computation of weighted averages for my site. Here's the PHP that pulls down the results --
$SQL = "SELECT a.*, b.CName, c.Description FROM humidor a
LEFT JOIN cigar b on a.CigarID = b.CigarID
LEFT JOIN cd_manufacturer c on b.Manufacturer = c.Code
WHERE UserID = $UserID AND DisplayCD = 1 AND Quantity > 0
GROUP BY CigarID
ORDER BY c.Description, b.CName ASC";
$Result = mysql_query($SQL); }Then it goes into a WHILE loop --
while ($row = mysql_fetch_row($Result)) {
$convertpurchasedate = strtotime($row[3]);
echo("<tr style=\"vertical-align:middle\"><td><a href=\"../cigar?id=$row[2]\">$row[12] $row[11]</a></td><td>".date('F d, Y', $convertpurchasedate)."</td><td>$row[4]</td><td>$ $row[5]</td>
<td><input id=\"e$row[2]\" type=\"submit\" value=\"Edit\" name=\"edit\" style=\"width:47px;font-size: x-small\" onclick=\"return UpdateRecord(this.parentNode.parentNode.childNodes[2].innerHTML,this.id.substr(1));\" /><input id=\"d$row[2]\" type=\"submit\" value=\"Delete\" name=\"delete\" style=\"width:47px;font-size: x-small\" onclick=\"return DeleteRecord(this.id.substr(1));\" /><br /><input id=\"u$row[2]\" type=\"submit\" value=\"Smoke One\" name=\"use\" style=\"width:95px;font-size: x-small\" onclick=\"return UseCigar(this.parentNode.parentNode.childNodes[2].innerHTML,this.id.substr(1),'$row[2]','$row[9] $row[8]');\" /></td></tr>");}What I'm trying to do is two-fold --
(1) I want to calculate a weighted average based on the number of cigars in inventory currently. I've figured out how to calculate it (I'm going to use something like: $TotalPrice / COUNT($Quantity) ). The issue with this is I'm not sure how to do this within the loop.
(2) Similar to #1, I want to group the cigars by CigarID such that only the most recent date added displays (I'm sure this is simple using the (MAX) function).
I figure this is probably something straightforward, it's just been one of those days and I am having a mental block.... :-/