Please Help: Inserting and Updating tables from form input

[nwabunnia]

I have been having a hard time trying to solve this but no way before I stumbled on this forum.

My problem is this: I'm trying to create a simple web-based schools management app using PHP and MySql.

Now I want to assign students to a subject using a select form field like this:

item1.png (4.05 KiB) Viewed 222 times

I am able to get the list of available students using this:

Code: Select all

 $sql = "SELECT * FROM students ";
      
      mysql_connect($host, $user, $password) or die(mysql_error());
      mysql_select_db("dbname") or die(mysql_error());
   
      $result = mysql_query($sql)or die(mysql_error()); 
      while ($row = mysql_fetch_array( $result)) {
         $student_firstname = $row['student_firstname'];
         $student_lastname = $row['student_lastname'];
         $va = $student_firstname . " " . $student_lastname ;
       echo "<option value='$va'>$va\n "; }
      echo "</option></select>\n";

The Sql syntax I used to create the subjects and assigned students table is:

Code: Select all

CREATE TABLE `subjects` (
  `subject_id` int(11) NOT NULL,
  `subject_name` varchar(50) NULL,
  PRIMARY KEY  (`subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE `assigned_students` (
  `subject_id` int(11) NOT NULL,
  `student_id` int(7) NULL,
  PRIMARY KEY  (`subject_id`),
  KEY `student_id` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

alter table assigned_students
      add constraint foreign key (subject_id)
                         references subjects (subject_id) on delete cascade;
alter table assigned_students
                add constraint foreign key (student_id)
                         references students (student_number) on delete cascade;


please I want if I save the form, for the assigned students table to be inserted with ALL the students ids(student_id) and the corresponding subject ids(subject_id). Also, when I want to edit a subject, for the form select to be populated with the students assigned to the subject being edited. Something like this:

item2.png (3.94 KiB) Viewed 222 times

Please could someone help me with the sql and PHP code to show it in the form <select></select>.
Thanks.

[johnj]

Please read about <select> tag.

[nwabunnia]

Please read about <select> tag.

Thanks for your reply but using <select> tag is not really the problem (see the attached images).

What is given me headache is how to insert/update the database table with the values in the <select> form field.

PS: The add and remove buttons work perfectly. I used JavaScript to code them.
Thanks.

[johnj]

Once you submit a form, every form element value can be accessed from the $_GET or $_POST array. Get the form values and use a sql to insert/update your table.

[nwabunnia]

Once you submit a form, every form element value can be accessed from the $_GET or $_POST array. Get the form values and use a sql to insert/update your table.

Thanks for your response.

I know how to use $_GET and $_POST; that is not the problem. From the first attached screen shot, you'll see that I was able to get the list of students from the students table. In fact, I'm able to insert/update the subjects table (subject_id and subject_name).

My problem is that because I am using <select multiple="multiple"> and use the "add" and "remove" buttons to add or remove students from a subject (let's assume it's a class), how do I insert into/update the assigned students table with the students id of all the students selected in the <select> tag.

I know I have to use an array but I have always been having problems with arrays no matter how hard i study them.

Okay, I used this sql statement to insert into the assigned_students table:

Code: Select all

$sql = "INSERT INTO assigned_students (subject_id, student_id) VALUES ($subid, $stuid)";

$stuid being the name of the <select> tag that has the list of assigned students.

Let's say I selected 5 students. When I run the statement, instead of getting 5 rows with subject id and student id, I get 1 row with the correct subject id and NULL in the student_id column.

Please I know this may sound a bit confusing but I hope I have been able to be as clear and cohesive as possible.

Thanks