How to output both Average and Total in this php script

Question

I have this php scrips,

  //mysql_connect

   $sql2 ="SELECT stdreg_exam.*,  
   (mathematics+english+kiswahili+geograph+civics+french+religion+pds+
   vskill+ict+science+history)/12 as Average FROM stdreg_exam 
         WHERE fname like '%".$fname."%' AND exam_name like '%".$exam_name."%'AND   
   class like '%".$class."%' AND date like '%".$date."%'";

$sql3 ="SELECT stdreg_exam.*,  
   (mathematics+english+kiswahili+geograph+civics+french+religion+pds+vskill+ict
   +science+history) as Total FROM stdreg_exam 
         WHERE fname like '%".$fname."%' AND exam_name like '%".$exam_name."%'AND   
    class like '%".$class."%' AND date like '%".$date."%'";


    $q=mysql_query($sql);
$q=mysql_query($sql3) AND $q=mysql_query($sql2);

         }
  else{
  $sql="SELECT * FROM stdreg_exam";
  $q=mysql_query($sql);
     }
  ?>


<form method="post">
<table width="500" border="0">
<tr>
<td>Student name</td>
<td><input type="text" name="fname" value="<?php echo $fname;?>" /></td>
<td>Exam name</td>
<td><input type="text" name="exam_name" value="<?php echo $exam_name;?>" /></td>
<td>Class</td>
<td><input type="text" width="10" name="class" value="<?php echo $class;?>" /></td>

<td>Date</td>
<td><input type="date" name="date" value="<?php echo $date;?>" /></td>

<td><input type="submit" name="submit" value="Search" /></td>
 </tr>
 </table>
  <br />
  </form>
  <table>
   <h4>
        <?php echo $exam_name;?>&nbsp;examinaton result of&nbsp;<?php echo $fname;?>
        &nbsp;held on&nbsp;<?php echo $date;?></h4><hr> 
      <table width="1110" border="0" cellpadding="2" cellspacing="1" >
    <tr>
    <td bgcolor="#CCCCCC" width="2000">Student name</td>
     <td bgcolor="#CCCCCC" width="10">Mathematics</td>
      <td bgcolor="#CCCCCC" width="10">English</td>
       <td bgcolor="#CCCCCC" width="10">Kiswahili</td>
        <td bgcolor="#CCCCCC" width="10">Geograph</td>
        <td bgcolor="#CCCCCC" width="10">Civics</td>
          <td bgcolor="#CCCCCC" width="10">French</td>
          <td bgcolor="#CCCCCC" width="10">Religion</td>
            <td bgcolor="#CCCCCC" width="10">Pds</td>
             <td bgcolor="#CCCCCC" width="10">Vskill</td>
             <td bgcolor="#CCCCCC" width="10">Ict</td>
              <td bgcolor="#CCCCCC" width="10">Science</td>
                <td bgcolor="#CCCCCC" width="10">History</td>
                 <td bgcolor="#CCCCCC" width="10">Average</td>
                  <td bgcolor="#CCCCCC" width="10">Total</td>
    <td bgcolor="#CCCCCC" width="10">Exam_name</td>
    <td bgcolor="#CCCCCC" width="1000">Class</td>
    <td bgcolor="#CCCCCC" width="1000">Date</td>
    </tr>
    <?php
     while($res=mysql_fetch_array($q)){
    ?>

     <tr>
     <td width="100"><?php echo $res['fname'];?></td>
     <td width="10"><?php echo $res['mathematics'];?>%</td>
     <td width="10"><?php echo $res['english'];?>%</td>
     <td width="10"><?php echo $res['kiswahili'];?>%</td>
     <td width="10"><?php echo $res['geograph'];?>%</td>
     <td width="10"><?php echo $res['civics'];?>%</td>
    <td width="10"><?php echo $res['french'];?>%</td>
    <td width="10"><?php echo $res['religion'];?>%</td>
    <td width="10"><?php echo $res['pds'];?>%</td>
    <td width="10"><?php echo $res['vskill'];?>%</td>
    <td width="20"><?php echo $res['ict'];?>%</td>
    <td width="10"><?php echo $res['science'];?>%</td>
    <td width="10"><?php echo $res['history'];?>%</td>
    <td width="30"><?php echo $res['Average']; ?></td>
    <td width="30"><?php echo $res['Total']; ?></td>
    <td width="10"><?php echo $res['exam_name'];?></td>
    <td width="30"><?php echo $res['class'];?></td>

    <td width="280" style="border-width:medium"><?php echo $res['date'];?></td>
   </tr>
     <?php }?>

The issue is when I run the script I only get the "Average" when this script arranged like this mysql_query($sql3) AND $q=mysql_query($sql2); BUT when I change the script like this $q=mysql_query($sql2) AND mysql_query($sql3) I only get the Total, And I want to get both Average and Total at the same time. Any one can help me.

Thank u in advance.

possible duplicate of [How can I display Total in this code](http://stackoverflow.com/questions/20013829/how-can-i-display-total-in-this-code) — scrowler, Nov 17 '13 at 04:11

score 0 · Answer 1 · answered Nov 17 '13 at 04:29

You're trying to execute two multiple queries using AND. That is incorrect.

$q = mysql_query($sql3) AND $q=mysql_query($sql2);

Consider the following example:

$var = 'one' AND 'two';
var_dump($var); // => one

As you can see, the following piece of code will always output one. This is because of operator precedence. You can read more about it in the PHP Manual.

A good idea to use solve this would be to get the Total first and then calculate the average using PHP:

$q = mysql_query($sql3);

Now, to get the average, you just have to divide it by the total number of subjects:

<td width="30"><?php echo $res['Total'] / 12; ?></td>

Sidenote: Your query is vulnerable to SQL injection and you should properly escape the user input before inserting it in your queries. I'd recommend using MySQLi / PDO to run parameterized SQL queries. Not only does this protect against SQL injection, it also speeds up queries.

I try to edit and gives average of ZERO(0) which is not true, the total is correct. — user2976535, Nov 17 '13 at 04:36

How to output both Average and Total in this php script

1 Answers1