ProductID---WeekDate---ProductAvailability
We forecast around15 weeks of availability so that means for each product we are creating 15 rows. It is extremely slow to load and edit. Here is the cut down version of the current code:
Code: Select all
$today=date("N")-1;
$mon=strtotime('-'.$today.' days');
$monformat=date("Y-m-d",$mon);
$baseweekorig=date("Y-m-d",strtotime("".$monformat." +0 week"));
$postamtweeks="8";
echo "<table border='1'><tr><td>Product Name</td>";
while ($i < $postamtweeks){
echo "<td>".date("Y-m-d",strtotime("".$baseweekorig." +".$i." week"));"</td>";
$i++;
}
echo "</tr>";
$resultprod=mysql_query("select ProductID, ProductName from product where ProdType='1' Order By ProductName");
while($rowprod=mysql_fetch_assoc($resultprod)){
$id=$rowprod['ProductID'];
echo "<tr><td>".$rowprod['ProductName']."</td>";
$j="0";
$Qty="0";
while ($j < $postamtweeks){
$baseweek=date("Y-m-d",strtotime("".$baseweekorig." +".$j." week"));
$result=mysql_query("select * from availability where UnrootedID='$id' and WeekDate = '$baseweek'");
while($row=mysql_fetch_assoc($result)){$Qty=$row['UnrootedAvailable'];}
echo "<td>".number_format($Qty)."</td>";
$j++;
}
echo "</tr>";
$Qty="0";
}
First, would it be smarter to lay the table out differently?
ProductID--_ProductAvailability--Week1---Week2---Week3---Week4--etc
That layout would mean that I would only have to connect with one statement and then report the weeks I am looking for instead of having to connect multiple times.
Secondly, am I going about the current query correctly? I need a "spreadsheet" type of layout for the office guys and this achieves it but, again, is very slow (I suspect because of multiple connections).
Thanks for the help,
Mick