hello Everyone.
Apologies cause i don't know where to post it right.
I have one table in there field name and other field in it. My problem is i want to create report filter by name. I populated names in combo box with this code:
print "Term : ";
// Write out our query.
$query = "SELECT fac_name FROM fac_key";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());
$dropdown = "<select name='facname'>";
while($row = mysql_fetch_assoc($result)) {
$dropdown .= "\r\n<option value='{$row['fac_name']}'>{$row['fac_name']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;
?>
<br>
<input type="submit" name="Submit" value="Preview">
</form>
I have the sample result that was group only by fac_name:
http://file.cmu.edu.ph/fac/avgfss.php
The codes is:
<?php
/////////////////////
//Create connection//
/////////////////////
$username = "+++++++";
$password = "+++++++";
$hostname = "+++++++++++++++";
///////////////////////////////
//connection to the database///
///////////////////////////////
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");
//echo "Connected to MySQL<br>";
//////////////////////////////////
//select a database to work with//
//////////////////////////////////
$selected = mysql_select_db("fac_db",$dbhandle)
or die("Could not select fac_db");
function RangeEquivalent($num){
switch ($num){
case ($num>= 90.01 && $num<= 100):
echo "Outstanding";
break;
case ($num>= 70.01 && $num<= 90):
echo "Very Satisfactory";
break;
case ($num>= 50.01 && $num<= 70):
echo "Satisfactory";
break;
case ($num>= 30.01 && $num<= 50):
echo "Moderately Satisfactory";
break;
case ($num>= 0 && $num<= 30):
echo "Needs Improvement";
break;
default: //default
echo "within no range";
break;
}
}
////////////////////////////////
//average query ///
////////////////////////////////
$query = "SELECT fac_Name as Name FROM fac_key GROUP BY fac_Name";
$result = mysql_query($query) or die(mysql_error());
//////////////////////
// Print out result///
//////////////////////
echo "<table border='1'>
<tr>
<th>Name of Faculty</th>
<th>ITEM</th>
<th>Self</th>
<th>Peer</th>
<th>Student</th>
<th>Supervisor</th>
<th>GWA</th>
<th>Descriptive Rating</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['Name']. "</td>";
echo "<td>
Commitment </br>
Knowledge of subjectt Learning </br>
Teaching for Independent Learning </br>
Management of Learning </br>
Over All Evaluation </br>
</td>";
echo "<td>";
$FacName = trim($row['Name']);
$query1 = "SELECT fac_Name as Name,fac_rater, avg(A1+A2+A3+A4+A5)/25*100 as Atotal_avg, avg(B6+B7+B8+B9+B10)/25*100 as Btotal_avg, avg(C11+C12+C13+C14+C15)/25*100 as Ctotal_avg, avg(D16+D17+D18+D19+D20)/25*100 as Dtotal_avg, avg(A1+A2+A3+A4+A5+B6+B7+B8+B9+B10+C11+C12+C13+C14+C15+D16+D17+D18+D19+D20)/100*100 as Overallavg FROM fac_key WHERE fac_Name = '$FacName' and fac_rater='Self' GROUP BY fac_Name";
//echo $query1;
$result1 = mysql_query($query1) or die(mysql_error());
$row1 = mysql_fetch_row($result1);
echo number_format($row1[2],2).
"</br>". number_format($row1[3],2).
"</br>". number_format($row1[4],2).
"</br>". number_format($row1[5],2).
"</br>". number_format($row1[6],2).
"</td>";
echo "<td>";
$query2 = "SELECT fac_Name as Name,fac_rater, avg(A1+A2+A3+A4+A5)/25*100 as Atotal_avg, avg(B6+B7+B8+B9+B10)/25*100 as Btotal_avg, avg(C11+C12+C13+C14+C15)/25*100 as Ctotal_avg, avg(D16+D17+D18+D19+D20)/25*100 as Dtotal_avg, avg(A1+A2+A3+A4+A5+B6+B7+B8+B9+B10+C11+C12+C13+C14+C15+D16+D17+D18+D19+D20)/100*100 as Overallavg FROM fac_key WHERE fac_Name = '$FacName' and fac_rater='Peer' GROUP BY fac_Name";
//echo $query2;
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_fetch_row($result2);
echo number_format($row2[2],2).
"</br>". number_format($row2[3],2).
"</br>". number_format($row2[4],2).
"</br>". number_format($row2[5],2).
"</br>". number_format($row2[6],2).
"</td>";
echo "<td>";
$query3 = "SELECT fac_Name as Name,fac_rater, avg(A1+A2+A3+A4+A5)/25*100 as Atotal_avg, avg(B6+B7+B8+B9+B10)/25*100 as Btotal_avg, avg(C11+C12+C13+C14+C15)/25*100 as Ctotal_avg, avg(D16+D17+D18+D19+D20)/25*100 as Dtotal_avg, avg(A1+A2+A3+A4+A5+B6+B7+B8+B9+B10+C11+C12+C13+C14+C15+D16+D17+D18+D19+D20)/100*100 as Overallavg FROM fac_key WHERE fac_Name = '$FacName' and fac_rater='Student' GROUP BY fac_Name";
//echo $query3;
$result3 = mysql_query($query3) or die(mysql_error());
$row3 = mysql_fetch_row($result3);
echo number_format($row3[2],2).
"</br>". number_format($row3[3],2).
"</br>". number_format($row3[4],2).
"</br>". number_format($row3[5],2).
"</br>". number_format($row3[6],2).
"</td>";
echo "<td>";
$query4 = "SELECT fac_Name as Name,fac_rater, avg(A1+A2+A3+A4+A5)/25*100 as Atotal_avg, avg(B6+B7+B8+B9+B10)/25*100 as Btotal_avg, avg(C11+C12+C13+C14+C15)/25*100 as Ctotal_avg, avg(D16+D17+D18+D19+D20)/25*100 as Dtotal_avg, avg(A1+A2+A3+A4+A5+B6+B7+B8+B9+B10+C11+C12+C13+C14+C15+D16+D17+D18+D19+D20)/100*100 as Overallavg FROM fac_key WHERE fac_Name = '$FacName' and fac_rater='Supervisor' GROUP BY fac_Name";
//echo $query4;
$result4 = mysql_query($query4) or die(mysql_error());
$row4 = mysql_fetch_row($result4);
echo number_format($row4[2],2).
"</br>". number_format($row4[3],2).
"</br>". number_format($row4[4],2).
"</br>". number_format($row4[5],2).
"</br>". number_format($row4[6],2).
"</td>";
echo "<td>";
$queryCom = "SELECT fac_Name as Name, avg(A1+A2+A3+A4+A5) as Atotal_commitment FROM fac_key WHERE fac_Name = '$FacName' GROUP BY fac_Name";
$resultCom = mysql_query($queryCom) or die(mysql_error());
$rowCom = mysql_fetch_row($resultCom);
$a1 = number_format($row1[2],2);
$s1 = number_format($row2[2],2);
$d1 = number_format($row3[2],2);
$f1 = number_format($row4[2],2);
$GWA1 = number_format(($a1 + $s1 + $d1 + $f1)/4,2);
echo $GWA1."</br>";
$a2 = number_format($row1[3],2);
$s2 = number_format($row2[3],2);
$d2 = number_format($row3[3],2);
$f2 = number_format($row4[3],2);
$GWA2 = number_format(($a2 + $s2 + $d2 + $f2)/4,2);
echo $GWA2."</br>";
$a3 = number_format($row1[4],2);
$s3 = number_format($row2[4],2);
$d3 = number_format($row3[4],2);
$f3 = number_format($row4[4],2);
$GWA3 = number_format(($a3 + $s3 + $d3 + $f3)/4,2);
echo $GWA3."</br>";
$a4 = number_format($row1[5],2);
$s4 = number_format($row2[5],2);
$d4 = number_format($row3[5],2);
$f4 = number_format($row4[5],2);
$GWA4 = number_format(($a4 + $s4 + $d4 + $f4)/4,2);
echo $GWA4."</br>";
$a5 = number_format($row1[6],2);
$s5 = number_format($row2[6],2);
$d5 = number_format($row3[6],2);
$f5 = number_format($row4[6],2);
$GWA5 = number_format(($a5 + $s5 + $d5 + $f5)/4,2);
echo $GWA5."</br>";
"</td>";
echo "<td>";
echo RangeEquivalent($GWA1)."</br>";
echo RangeEquivalent($GWA2)."</br>";
echo RangeEquivalent($GWA3)."</br>";
echo RangeEquivalent($GWA4)."</br>";
echo RangeEquivalent($GWA5);
echo "</td>";
echo "</tr>";
}
echo "</table>"
?>
All is in one table fac_key
Please help: