MySQL Forums :: PHP :: Creating report filter by name in one table


Advanced Search

Creating report filter by name in one table
Posted by: Emelio Navaja ()
Date: August 20, 2013 03:56PM

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:

Options: ReplyQuote


Subject Written By Posted
Creating report filter by name in one table Emelio Navaja 08/20/2013 03:56PM
Re: Creating report filter by name in one table Rick James 08/31/2013 06:11PM
Re: Creating report filter by name in one table Emelio Navaja 09/01/2013 08:10AM
Re: Creating report filter by name in one table Rick James 09/03/2013 07:57AM
Re: Creating report filter by name in one table Emelio Navaja 09/04/2013 01:43AM
Re: Creating report filter by name in one table Rick James 09/05/2013 07:27PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.