MYSQLQuery using PHP

[EmelNavz]

Hello to every one:

 

I very new to PHP/MYSQL. I created mysql table( Please see image) can i generate report like those image attached?

 

Please help.

 

Emel

[trq]

yes you could, where are you stuck?

[EmelNavz]

Thank you sir for your response. My co-worker solved the problem with this code: However i would like to create two report base on 1. fac_sem and 2. fac_sem and fac_name using combo box as an option selection. by the way i have separate table for name of faculty and semester. Please show me simple solution or code as possible. thank you very much!

 

 

 <?php
/////////////////////
//Create connection//
/////////////////////

$username = "+++++++";
$password = "+++++++";
$hostname = "+++++++++++++++";

///////////////////////////////
//connection to the database///
///////////////////////////////
$dbhandle = mysql_connect($hostname, $username, $password)
   or die("Unable to connect to MySQL");            //line 10
//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 single column///
////////////////////////////////

$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>"


?>

Edited August 12, 2013 by EmelNavz

[EmelNavz]

Hello sir;

 

the above code is supposedly display report by term base on the field fac_sem in the table fac_key and below is the comboselect which to be selected and preview base on the fac_sem.

 

<form action="avgss.php" method="post">

<?php

//Create connection

$username = "####";
$password = "######";
$hostname = "##########";

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password)
or die("Unable to connect to MySQL");            //line 10
//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");
?>
<?php

print "Term : ";

// Write out our query.
$query = "SELECT sem_name FROM semester";
// Execute it, or return the error message if there's a problem.
$result = mysql_query($query) or die(mysql_error());

$dropdown = "<select name='term'>";
while($row = mysql_fetch_assoc($result)) {
$dropdown .= "\r\n<option value='{$row['sem_name']}'>{$row['sem_name']}</option>";
}
$dropdown .= "\r\n</select>";
echo $dropdown;

?>

<br>
<input type="submit" name="Submit" value="Preview">
 
</form>
 

[EmelNavz]

Hello sir:

 

I need to resolve this. Please help.