Condition not working with PHP and mysqli

Question

I have two tables in my database: students and teachers. I am trying to assign each teacher with 4 students that are writing their essay on that teacher's subject. For that, I have created a third table: assignment. I made two conditions: if the student is assigned (his or her assigned table value is 1), then the code should skip that student AND if the teacher has more than 3 assigned students (his or her assigned table value is less than 4), then the code should skip that teacher. This is the code that I have tried:

<?php

    require('config_teachers.php');
    require('config_students.php');

    // We get all the info as associative arrays: $students_info and $teachers_info.

    $serverName = "localhost";
    $user = "root";
    $password = "";
    $dbName = "yerkindb";
    
    foreach($students_info as $student_info){
        foreach($teachers_info as $teacher_info){
            if($student_info['assigned'] == 0 && $student_info['ee_subject'] == $teacher_info['ee_subject_1_choice'] && $teacher_info['assigned'] < 4 ){

                $conn = mysqli_connect($serverName, $user, $password, $dbName);

                if(mysqli_connect_errno()){
                        
                    echo "Failed to connect to mysqli.".mysqli_connect_errno();
                        
                } else {
                        
                    $name_student = $student_info['name'];
                    $name_teacher = $teacher_info['name'];
                    $subject = $student_info['ee_subject'];

                    $query = "INSERT INTO assignment(student_name, advisor_name, subject) values('$name_student', '$name_teacher', '$subject')";
                            
                    mysqli_query($conn, $query);

                    $assigned_teacher = $teacher_info['assigned'];

                    $assigned_teacher = $assigned_teacher + 1;

                    $query = "UPDATE teachers SET assigned = $assigned_teacher WHERE name = '$name_teacher'";

                    mysqli_query($conn, $query);

                    $query = "UPDATE students SET assigned = 1 WHERE name = '$name_student'";

                    mysqli_query($conn, $query);

                    mysqli_close($conn);
                }
            }
        }
    }

?>

The line

if($student_info['assigned'] == 0 && $student_info['ee_subject'] == $teacher_info['ee_subject_1_choice'] && $teacher_info['assigned'] < 4 ){

has the conditions that I want to assign, however, when I run the code, I get each student getting assigned to every single teacher that has the same subject as the subject he or she is writing her essay on.

Can someone explain why are those conditions getting skipped, even though the

$student_info['ee_subject'] == $teacher_info['ee_subject_1_choice']

condition is not getting overlooked.

**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, Apr 08 '22 at 16:21
Unrelated to the problem: Don't connect to the database every time through the loop. Make one connection at the beginning and use that all the time. — Barmar, Apr 08 '22 at 16:22
`$assigned_teacher = $assigned_teacher + 1;` doesn't add to `$teacher_info['assigned']`. So that part of the condition will always succeed. — Barmar, Apr 08 '22 at 16:23

score 0 · Answer 1 · answered Apr 08 '22 at 16:35

Once you've assigned a student to a teacher, you should break out of the teacher loop, so you don't try to assign the student to another teacher.

When you assign a student to a teacher, you need to increment $teacher_info['assigned']. And in order to do this, your iteration variable needs to be a reference to the array element; otherwise you get a copy of the array, which won't affect future iterations.

In the code below, I've converted all the queries to prepared statements.

$conn = mysqli_connect($serverName, $user, $password, $dbName);
if(mysqli_connect_errno()){
    echo "Failed to connect to mysqli.".mysqli_connect_errno();
} else {
    $insert_assignment_stmt = $conn->prepare("INSERT INTO assignment(student_name, advisor_name, subject) values(?, ?, ?)");
    $insert_assignment_stmt->bind_param("sss", $name_student, $name_teacher, $subject);
    $update_teacher_stmt = $conn->prepare("UPDATE teachers SET assigned = assigned + 1 WHERE name = ?");
    $update_teacher_stmt->bind_param("s", $name_teacher);
    $update_student_stmt = $conn->prepare("UPDATE students SET assigned = 1 WHERE name = ?");
    $update_student_stmt->bind_param("s", $name_student);

    foreach($students_info as &$student_info){
        foreach($teachers_info as &$teacher_info){
            if($student_info['assigned'] == 0 && $student_info['ee_subject'] == $teacher_info['ee_subject_1_choice'] && $teacher_info['assigned'] < 4 ){
                $name_student = $student_info['name'];
                $name_teacher = $teacher_info['name'];
                $subject = $student_info['ee_subject'];

                $insert_assignment_stmt->execute();
                $update_teacher_stmt->execute();
                $update_student_stmt->execute();

                $teacher_info['assigned']++;

                break;
            }
        }
    }
}
mysqli_close($conn);

Could you explain to me why you used '&' in the foreach opening lines before the variables $student_info and $teacher_info? — Yerkin, Apr 10 '22 at 16:23
I explained in in the answer -- it needs to be a reference variable. See https://stackoverflow.com/questions/8769288/php-ampersand-before-the-variable-in-foreach-loop — Barmar, Apr 10 '22 at 22:34

Condition not working with PHP and mysqli

1 Answers1