Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Please Help: Inserting and Updating tables from form input

Codes here !

Moderators: macek, egami, gesf

Please Help: Inserting and Updating tables from form input

Postby nwabunnia » Fri Jul 06, 2012 10:03 pm

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
item1.png (4.05 KiB) Viewed 339 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
item2.png (3.94 KiB) Viewed 339 times


Please could someone help me with the sql and PHP code to show it in the form <select></select>.
Thanks.
Last edited by nwabunnia on Sat Jul 07, 2012 11:07 am, edited 2 times in total.
nwabunnia
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon Jul 02, 2012 1:00 pm

Re: Please Help: Inserting and Updating tables from form inp

Postby johnj » Fri Jul 06, 2012 10:21 pm

Please read about <select> tag.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: Please Help: Inserting and Updating tables from form inp

Postby nwabunnia » Fri Jul 06, 2012 10:30 pm

johnj wrote: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.
nwabunnia
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon Jul 02, 2012 1:00 pm

Re: Please Help: Inserting and Updating tables from form inp

Postby johnj » Sat Jul 07, 2012 3:22 am

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.
johnj
php-forum Super User
php-forum Super User
 
Posts: 1805
Joined: Thu Mar 10, 2011 5:07 pm

Re: Please Help: Inserting and Updating tables from form inp

Postby nwabunnia » Sat Jul 07, 2012 11:57 am

johnj wrote: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
nwabunnia
New php-forum User
New php-forum User
 
Posts: 4
Joined: Mon Jul 02, 2012 1:00 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.