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 and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. 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. Escaping is not enough! — Dharman
– Dharman ♦, Commented Apr 8, 2022 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
– Barmar, Commented Apr 8, 2022 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
– Barmar, Commented Apr 8, 2022 at 16:23

Barmar · Accepted Answer · 2022-04-08 16:35:34Z

0

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);

answered Apr 8, 2022 at 16:35

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Yerkin Over a year ago

Could you explain to me why you used '&' in the foreach opening lines before the variables $student_info and $teacher_info?

Barmar Over a year ago

I explained in in the answer -- it needs to be a reference variable. See stackoverflow.com/questions/8769288/…

Collectives™ on Stack Overflow

Condition not working with PHP and mysqli

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related