0

I've two tables (examresults and gradings). I'm trying to give grades according to score value. here are my tables

examresults

ID SUBJECT SCORE
1 English 100
1 Math 80
1 Bios 40

gradings

GNAME MAXSCORE
A 100
B 80
C 64
D 40
F 34

and here is what I've tried

my code

$counter = 0;
$stid=1;
        if($sql=("SELECT * from `examresults` where  `id`='$stid'"))
            {
                $stmt = $conn->prepare($sql);
                // execute the query
                $stmt->execute();
                echo '<h3 class="text-center">Examination Results</h3>
                ';
                echo'
                <table class="table table-bordered">
                <thead>
                        <tr style="background-color: #F2F2F2;">
                          <td class="text-bold">Sl No</td>
                          <td class="text-bold">Subject</td>
                          <td class="text-bold">Score</td>
                          <td class="text-bold">Grade</td>
                        </tr>
                      </thead>
                ';
                while($row = $stmt->fetch(PDO::FETCH_ASSOC))
                {
                    $score=$row['score'];
                            $check_for_grade = $conn->prepare("SELECT IF(maxscore>='$score',gname,IF(maxscore<='$score',gname,'')) FROM gradings");
                $check_for_grade->execute();
                $mypy = $check_for_grade->fetch();
                $grade = $mypy[0];  
                  echo'
                  <tbody>
                  <tr role="row" class="odd">
                  <td>'.++$Counter.'</td>
                 <td style="padding-left: 50px;">'.$row['subject'].'</td>
                 <td>'.$row['score'].'</td>
                <td>'.$grade.'</td>
                 </tr>
                        
                  ';
                }
            }
            echo'
                      </thead>
                      </tbody>
                      </table>
                ';

The problem is that, it gives me wrong info and I think problem exist in my IF statement, please any help.

Here is Output

ID SUBJECT SCORE GRADE
1 English 100 A
1 Math 80 A
1 Bios 40 A

What I need is

ID SUBJECT SCORE GRADE
1 English 100 A
1 Math 80 B
1 Bios 40 D
Mostan
  • 17
  • 3
  • What results did you get? What results did you expect? Please provide clearer details. – ADyson Jun 22 '22 at 07:04
  • It gives 'A' score in all subjects I expect to give me this A,B,D – Mostan Jun 22 '22 at 07:05
  • Rather than querying grades for each exam result, I would first select all grades in an array once. Then, for each exam result, I'll iterate through the grade array to find the match. – Monnomcjo Jun 22 '22 at 07:11
  • As an aside, `if($sql=("SELECT * from `examresults` where `id`='$stid'"))` - 1) this makes no sense to go in an `if`, it will always be true. and 2) There is absolutely no point in using prepared statements if you don't also use **parameters** with it. That code is still vulnerable to SQL injection. Parameterise the `$id` bit. – ADyson Jun 22 '22 at 07:11
  • @Monnomcjo please can you show me how? – Mostan Jun 22 '22 at 07:15
  • I don't know exactly, but in the idea SELECT * from gradings, then while($row = mysql_fetch_assoc( $result)){$gradings[] = $row;}, then SELECT * from examresults, and inside do foreach($gradings as grade){if($row['score]...$grade['maxscore'])}... – Monnomcjo Jun 22 '22 at 07:24
  • **Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/32391315) – Dharman Jun 22 '22 at 09:17
  • What I asked for help was not helped, instead I was given a SQL injection warnings, I know that, and I know how to solve that, but it is not something I asked for. Anyway Thanks. – Mostan Jun 24 '22 at 07:28

0 Answers0