Please Help: Inserting and Updating tables from form input

Dev Shed Forums Sponsor:

July 7th, 2012, 01:05 AM

Nwabunnia

Registered User

Join Date: Nov 2009

Posts: 5

Time spent in forums: 2 h 2 m 16 sec
Reputation Power: 0

Please Help: Inserting and Updating tables from form input

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:

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

Code:

 $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:

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:

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

July 7th, 2012, 05:03 AM

CVDubey

Registered User

Join Date: Jul 2012

Posts: 3

Time spent in forums: 43 m 12 sec
Reputation Power: 0

Elaborate your question/problem.

It's not making much sense, what you want as output..?

July 7th, 2012, 07:22 AM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526

Could a student only be assigned to one subject? If so, you might want to get rid of the assigned_students table and add column "subjects_id" to the students table. If not, then assigned_students PK should be a composite subjects_id/students_id.
$sql = "SELECT s.student_firstname, s.student_lastname FROM students AS s INNER JOIN assigned_students AS as ON as.students_id=s.students_id WHERE as.subject_id=123";

Instead of writing your queries like "SELECT stud.* FROM * from students", do it like "SELECT student_firstname, student_lastname FROM * from students". I know it seems redundant, but doing so will help prevent you from making silly mistakes. If you have more than one table, include the table name (either students or an alias. Note that your use of "stud" won't work since it wasn't set up as an alias and is not a table name)

I too am having a difficult time determining what your question is. Please elaborate.

July 7th, 2012, 03:09 PM

Nwabunnia

Registered User

Join Date: Nov 2009

Posts: 5

Time spent in forums: 2 h 2 m 16 sec
Reputation Power: 0

Quote:

Originally Posted by NotionCommotion

I know how difficult the question may seem and I apologize. I wanted to attach a screen shot of what I have in mind but couldn't.

Quote:

$sql = "SELECT s.student_firstname, s.student_lastname FROM students AS s INNER JOIN assigned_students AS as ON as.students_id=s.students_id WHERE as.subject_id=123";

This is okay if you are retrieving data. I'm more concerned with insert/update.

Let me try to elaborate on what I have in mind. Since 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:

Quote:

$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 hope I have been able to be as clear and cohesive as possible.

Thanks

July 7th, 2012, 05:25 PM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526

You know I never used <select multiple="multiple">, but am almost certain that it returns an array. So, what I want you to do is add the following to your script that processes the submission.

PHP Code:


		
			
echo('<pre>'.print_r($_POST,1).'</pre>');

Use print_r() very often when testing as it helps you understand what is going on. The <pre> tags are not needed, but improve readability for non-ajax responses. Your form is using POST and not GET, right? If not, it should. Use POST whenever you are changing something (i.e. the database, a session, someone's bank account, etc).

Next, add to your script <input type="hidden" name="subid" value="<?php echo($subid); ?>" />

Check it again, and verify that your print_r() is showing the subject ID.

Now you just want to enter the data to your database. You never answered my question whether a student only be assigned to one subject. I am assuming they could, in which your database schema is not correct. ALWAYS get your database schema correct before you deal with PHP. While the specific queries will be different, the approach is basically the same. Add something like the following:

PHP Code:


		
			
foreach ($_POST['my_name_for_the_select_menu'] AS $stuid)

{$sql = "INSERT INTO assigned_students (subject_id, student_id) VALUES ($_POST['subid'], $stuid)";  

//do the query

}

I personally use PDO. If you decide not to use PDO, escape your inputs, or I am going to hack your site because you deserve it (Just kidding

).

Good luck.

PS. How did you belong to this forum for 3 years, and only now posted your first question?

Last edited by NotionCommotion : July 7th, 2012 at 05:28 PM.

July 7th, 2012, 06:59 PM

karim6400

Registered User

Join Date: Jul 2012

Location: tunis

Posts: 1

Time spent in forums: 26 m 5 sec
Reputation Power: 0

this help me too

thanks

July 8th, 2012, 02:12 AM

Nwabunnia

Registered User

Join Date: Nov 2009

Posts: 5

Time spent in forums: 2 h 2 m 16 sec
Reputation Power: 0

Quote:

Originally Posted by NotionCommotion

You know I never used <select multiple="multiple">, but am almost certain that it returns an array. So, what I want you to do is add the following to your script that processes the submission.

PHP Code:


		
			
echo('<pre>'.print_r($_POST,1).'</pre>');

PHP Code:


		
			
foreach ($_POST['my_name_for_the_select_menu'] AS $stuid)

{$sql = "INSERT INTO assigned_students (subject_id, student_id) VALUES ($_POST['subid'], $stuid)";  

//do the query

}

I personally use PDO. If you decide not to use PDO, escape your inputs, or I am going to hack your site because you deserve it (Just kidding

).

Good luck.

PS. How did you belong to this forum for 3 years, and only now posted your first question?

Thanks. I'll try it out later.

Yes, a student can be assigned more than one subject.

As to why I am just posting now after three years, well, I was ill for a very long time and didn't use the internet. The rest of the time, I never had an issue I needed solution to until now.

Thanks a lot.

July 8th, 2012, 09:57 AM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526

I am glade you are feeling better.

You will need a "many-to-many" table. Search the phrase on the Internet, and see below for an example.

I typically echo my sql queries first to make sure that PHP is rendering them they way I expect.

Also, if you want, you could use MySQL "IN" to update all your students at once, however, I think looping over each student is simpler.

Code:

CREATE  TABLE IF NOT EXISTS students (
  idstudents INT NOT NULL AUTO_INCREMENT ,
  name VARCHAR(45) NULL ,
  PRIMARY KEY (idstudents) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS subjects (
  idsubjects INT NOT NULL AUTO_INCREMENT ,
  name VARCHAR(45) NULL ,
  PRIMARY KEY (idsubjects) )
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS students_has_subjects (
  students_idstudents INT NOT NULL ,
  subjects_idsubjects INT NOT NULL ,
  PRIMARY KEY (students_idstudents, subjects_idsubjects) ,
  INDEX fk_students_has_subjects_subjects1 (subjects_idsubjects ASC) ,
  INDEX fk_students_has_subjects_students (students_idstudents ASC) ,
  CONSTRAINT fk_students_has_subjects_students
    FOREIGN KEY (students_idstudents )
    REFERENCES mydb.students (idstudents )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_students_has_subjects_subjects1
    FOREIGN KEY (subjects_idsubjects )
    REFERENCES mydb.subjects (idsubjects )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

July 8th, 2012, 08:53 PM

Nwabunnia

Registered User

Join Date: Nov 2009

Posts: 5

Time spent in forums: 2 h 2 m 16 sec
Reputation Power: 0

Thank you NotionCommotion. You've really been of immense help and it's working smoothly - for now.

BTW, using "auto increment" has one drawback that I don't like. If, for instance, you have 5 rows of data with auto incremented values 1 to 5. If you delete row 5 and insert another row, the values would start from 6 and not 5.

I don't find it very tidy especially when I want my output to be ordered numerically. Is there a way around this?

#10

July 8th, 2012, 09:12 PM

NotionCommotion

Contributing User

Join Date: Sep 2006

Posts: 1,464

Time spent in forums: 2 Weeks 1 Day 6 h 21 m 36 sec
Reputation Power: 526

If you are a compulsive tidy person, do not use autoincrement keys. They are a crutch for the lazy. I personally use them often. If you decide to use them (i.e. a surrogate key), recognize that they are meant for internal use only and should not be displayed to the user.

Please Help: Inserting and Updating tables from form input

Developer Shed Advertisers and Affiliates