0

When users in my system register they provide a 'school username', which identifies their school. I'm trying to modify my register system so that it populates the users table, and creates a corresponding row in the school_tasks table using the school username provided. The schoolID is the foreign key used in school_tasks to link the users table.

Expected outcome: I want one row to exist in the school_tasks table for every school.

Actual outcome: A user is created correctly, but the school_tasks table never gets populated with data. I get the stmtFailed error.

I've tried creating a temporary 'test' column and inserting the school username to it, but the statement still fails.

I'm stumped as to why this is not working, as the code to populate both tables is essentially the same, but does not work for the school_tasks table.

Any help will be greatly appreciated, and I will include a screenshot of my code below. TIA

Users table structure: enter image description here

School table structure: enter image description here li_1, nu_1, and to_1 will be used at a later stage for assignment.

Data insertion:

// Insert new school into database
    function createSchool($conn, $sid) {
        // sql for creating school
        $sql = "INSERT INTO school_tasks (schoolUID) VALUES (?);";

        $stmt = mysqli_stmt_init($conn);
        if (!mysqli_stmt_prepare($stmt, $sql)) {
            header("location: ../html/register.php?error=stmtFailed");
            exit();
        }

        // binding variables to statement parameter markers
        mysqli_stmt_bind_param($stmt, "s", $sid);
        mysqli_stmt_execute($stmt);
        mysqli_stmt_close($stmt);
        mysqli_close($conn);
    }
Cameron
  • 9
  • 1
  • 3
  • 'schoolID is the foreign key used in school_tasks to link the users table.' - looks like bad design surely school is an entity with it's own attributes (eg location). Also please publish table definitions as text. – P.Salmon Apr 08 '22 at 08:51
  • I'm only using a school to identify a group of users uniquely and to assign or de-assign tasks for that group of users. I will edit in table definitions now, thanks :) – Cameron Apr 08 '22 at 08:58
  • Please post code as text, not as screenshot. Extra points for turning the tables into text as well. – deceze Apr 08 '22 at 09:27
  • Done, thanks (not the tables, sorry) – Cameron Apr 08 '22 at 09:31
  • The problem was caused as I closed the database connection in the method before... oops. – Cameron Apr 08 '22 at 10:12

0 Answers0