How to run a sql query inside a while of another sql query?

Question

Hi my code as follow:-

$sql="SELECT student_id,DA1,DA2,DA3,DA4,DA5,DA6,HA1,HA2,HA3,HA4,HA5,HA6 from table";
$results = $result->query($sql);
while($row = $results->fetch_assoc())
{
   $id = $row['student_id'];
   $marks1 = $row['DA1'] + $row['HA1']/2 ;
   $marks2 = $row['DA2'] + $row['HA2']/2 ;
   $marks3 = $row['DA3'] + $row['HA3']/2 ;
   $marks4 = $row['DA4'] + $row['HA4']/2 ;
   $marks5 = $row['DA5'] + $row['HA5']/2 ;
   $marks6 = $row['DA6'] + $row['HA6']/2 ;

   $i = 1;
   while($i <= 6)
   {
      $sql = "SELECT `grade`,`point` FROM `grades` where  ${'marks' . $i} BETWEEN min and max";
      $results = $result->query($sql);
      $row = $results->fetch_assoc();
      ${'grade' . $i} = $row['grade'];
      ${'point' . $i} = $row['point'];
      $i++;
    }  

    $totalcredit = 20;
    $sgpa= ($point1*$c1 + $point2*$c2 + $point3*$c3 + $point4*$c4 + $point5*$c5 + $point6*$c6) / $totalcredit ;

    $sql = "UPDATE table SET `G1` = '$grade1', `G2` = '$grade2' ,`G3` ='$grade3',`G4` = '$grade4',`G5` = '$grade5',`G6` = '$grade6', `SGPA` = '$sgpa' WHERE student_id = '$id'";
    $result->query($sql);
 }

In this code i am trying to calculate grade and sgpa of a class. In the first query selecting all the details and calulating total marks in six subjects. In the second query i am storing the grade and point in variables with respect to the marks and then calculating the sgpa. Then the update query is performed to store the details in database.

The table consist of 100 rows. The code is working for the first row but does not work for another rows and returns empty values. I am recursively trying to calculate and update the table with data of all students. I would be highly grateful if anybody can help in running the code for all the rows.

Check this out: http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query — Felippe Duarte, May 03 '16 at 20:14
change $results to $results2 inside the sql loop. Looks like you are overriding one for the other. — arcee123, May 03 '16 at 20:15
Your inner WHILE is overwriting the contents of your variable $results — Webomatik, May 03 '16 at 20:15

score 1 · Accepted Answer · answered May 03 '16 at 20:20

1

Change the inner while loop like this:-

$i = 1;
while($i <= 6)
{
  $sql = "SELECT `grade`,`point` FROM `grades` where  ${'marks' . $i} BETWEEN min and max";
  $results2 = $result->query($sql);
  $row = $results2->fetch_assoc();
  ${'grade' . $i} = $row['grade'];
  ${'point' . $i} = $row['point'];
  $i++;
}

In your code $results is getting overrided so changing it to $results2 in inner loop will solve your problem.

answered May 03 '16 at 20:20

Kushal Srivastava

60
1
13

@cmorrissey $result does not need to be changed as it as database connection object. – Kartikey Vishwakarma May 03 '16 at 20:24
Thank you very much @Kushal Srivastava changing $results solved the problem. – Kartikey Vishwakarma May 03 '16 at 20:26

How to run a sql query inside a while of another sql query?

1 Answers1