For my system, students are required to create a user account by completing a registration form (personal, contact and course details; also username and password).

I need to collect registration data through a single form and insert the collected data into two related tables on my database (all student details in student table and login details in users table).

How best can I code this in php?

Entity–relationship model

1 Answer 1

  • Use mysqli or PDO libraries for PHP, do not use mysql, as it is deprecated
  • Use prepared statements: they are cleaner, less prone to SQL injection and, in some cases, faster to execute
  • Use exceptions to capture errors throughout your code -query fails, query gets killed, database crashes, unable to connect, ...
  • If you feel intimidated by SQL, an ORM can help you build your queries, but they are not without issues
  • Students' id should have a foreign key referencing user_id, assuming you want a 1:1 relationship. To use foreign keys on MySQL you will want to use InnoDB stored engine.
  • You cannot insert to two tables with a single statement, so you should use transactions. Start a transaction before inserting to the first table, then insert to the second one, then COMMIT the changes. That way, either the two inserts succeed or both fail. Transactions also require InnoDB.
  • student_id/user_id are probably going to be unique arbitrary keys. You can use the auto_increment property for that column and do:

    INSERT INTO student (last_name, fist_name, ...) VALUES ('Crespo', 'Jaime', ...);
    INSERT INTO users VALUES (last_insert_id(), 'jynus', 'hashedpassword', 'student');
  • Thanks for the reply, I am not familiar with PDO, this means i have to start from scratch... I have a foreign key “user-id” in the student table and also using the InnoDB engine; the student_id(PK) and user_id(PK) are both set to auto-increment, but as a foreign key the user_id in the student table is not set to auto_increment. The PDO seems quite intimidating given i have very limited time to complete this. I am new to programming my approach, can i use mysqli? What are my options?
    – Tweet
    Commented Jul 19, 2014 at 20:48
  • The php manual has long examples you can look at: php.net/manual/en/mysqli.quickstart.prepared-statements.php
    – jynus
    Commented Jul 20, 2014 at 1:29
  • how can i use this with the ON DELETE CASCADE? i think the foreign keys should be in the child table.. am I right? @jynus
    – AAEM
    Commented Feb 9, 2019 at 20:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.