Creating DB codes for forms "Dynamically"?

[eluser]RaGe10940[/eluser]
I have a page that has 39 check boxes. The check boxes in my example resemble form names. My problem is that with 39 check boxes I need a way to store what forms were given to a student. Currently what I have set up is that each form is separated with a comma and a quote so that when a report is run the Administrator can use a CSV download option and group which forms a student has received. This works but is very rudimentary and also gives a bad side affect that before each form name a / is present because mysql escapes quotes.

This is what I currently have :

Code:

if ($this->input->post('action') == 'additional') {
      $givenforms = "";
    
         foreach ($this->input->post('form') as $forms) {
       $givenforms .= ', "' . $forms . '"';
      }
      $comments = 'This student was given' . $givenforms . '';
    
      if (($this->input->post('action') == 'additional') && ($this->input->post('other') == 'OTHER')) {
          $comments .= ', '.$this->input->post('counselorcomments');
      }
     }

Again in the database the results will look like : This student was given "xyz", "eoe", "wwo",

Pretty much I just need ideas on how to store which forms a student was given, and if needed if all 39 forms are given to a student I need to store all forms the student was given for later reporting. (even though 39 forms wont be given)

[eluser]TheFuzzy0ne[/eluser]
Are there likely to be more forms added? Can you not just have a table with a column for comments, one for date, one for student ID and a column for each form?

Alternatively, you could have a table for transaction ID, date, student ID and comments, and another table with transaction ID and form ID, and maybe another table of form IDs and form names..

[eluser]RaGe10940[/eluser]
Yes the plausibility for more forms to be added is very very high!

Currently this is my schema :

Session Table -> PK ->Session ID
Support Table PK -> Session ID & Counsellor Username -> Fields included are : Start Time, Finish Time, and Counsellor Comments.

Now what your suggesting is to make another table called Forms, within that table the FK will be session ID, and then then the Form Id's will be a FK's of the Form Table?

I have thought of that but the question is how can I have it so that one Session (student in the session) gets multiple forms? Do I make it so that the PK's are Session ID and Form ID? So that we have a reoccuring session ID with different form ID's?

[eluser]TheFuzzy0ne[/eluser]
The session should have nothing to do with anything.

I assume you have a table which contains all of the students?

Students - Contains all the student data.

Code:

+----+------------+-----------+-----
| id | first_name | last_name | ...
+----+------------+-----------+-----
| .. |    ...     |    ...    | ...

Forms - Contains data for all the forms.

Code:

+----+-----------+-----
| id | form_name | ...
+----+-----------+-----
| .. |    ...    | ...

Issued Forms - To keep track of what was issued to who, and when.

Code:

+-----------------+--------------+-----------+---------+-----
| student_id (FK) | form_id (FK) | issued_on | comment | ...
+-----------------+--------------+-----------+---------+-----
|       ...       |      ...     |    ...    |   ...   | ...

Now you can easily see who was sent what form and when. There will be more than one row for students who have been given more than one form. You can add extra columns too. Maybe one so you can see who issued the form to the student?

[eluser]RaGe10940[/eluser]
Awesome, this is what I was exactly thinking about as well. I will report back when I am finished. Thanks Fuzzy

[eluser]RaGe10940[/eluser]
Couple hours of re-factoring what I had with the CSV paid off quite well. I am very very pleased with the reporting/analytical possibilities of the knew information and the way I got it stored now.

Couple snippets of code for any one else looking into doing something like this! :

Code:

if ($this->form_validation->run() == FALSE) { // This stuff is self explanatory RT(F)M if you will :)
     $this->cont();
} else {
     $this->load->model('queue_model'); // Load model

     $session = $this->uri->segment(3); // Gets the session id
     $counselor = $this->session->userdata('username'); // I get counsellor names from the username they log in by joining between the two tables

     if ($this->input->post('action') == 'Additional') { // If additional forms is checked do the following

  foreach ($this->input->post('form') as $form_id) { // for each form submitted take the session Id from above and insert it into the table forms with the foreach $form_id variable
      $this->queue_model->forms($session, $form_id);
  }

  if (($this->input->post('action') == 'Additional') && ($this->input->post('addother') == 'addotherinfo')) { // If forms were submitted and a addotherinfo was [checked] add comments
      $comments = ''.$this->input->post('action'). ' - '.$this->input->post('counselorcomments').'';
  } else {
      $comments = $this->input->post('action');
  }
     }

I am super happy with the way the page turned out! Thanks so much!

[eluser]TheFuzzy0ne[/eluser]
Excellent. It looks good.