POST data updating MySQL tables with one-to-many relationship

[terrid25]

Hi all

 

I have 3 tables

Table_1, Table_2 and Table_3

 

Table_1 is a list of countries, with name and country_id

Table_2 is a table that has 3 fields, id, name and description

Table 3 is a table that has name, Table_2_id

 

So what I need to do:

Display the name and description field of Table_2 in a form

Loop through the countries table and display each as an input box and display on the same form

When I fill out the form details, the name/description must be inserted into Table_2, creating an id

The input boxes data then also needs inserting into Table_3, with the foreign key of Table_2_id

 

So a small example would be:

Name: testing

Description:  this is a test

Country of Australia: Hello

Country of Zimbabwe: Welcome

 

This means that in Table_2, I will have the following:

=============================

| id  | name    | description

| 1    |  testing | this is a test

=============================

 

Table_3

=============================

| Table_2_id |  name        | country_id

| 1                    | Hello          | 20

| 1                    | Welcome  | 17

=============================

 

20 is the country_id of Australia

17 is the country_id of Zimbabwe

 

Code:

 

Generating the input fields dynamically:

                           $site_id = $this->settings['site_id'];

                           $options = '';
                           $country_code = '';
		   $query = $DB->query("SELECT country_code, country_id, IF(country_code = '".$country_code."', '', '') AS sel FROM Table_1 WHERE site_id='".$this->settings['site_id']."' ORDER BY country_name ASC");
		   
                               foreach ($query->result as $row)
                               {
                                        $options .= '<label>' . 'Test for ' . $this->settings['countries'][$row['country_code']] . '</label>' . '<br />';
                                        //$row['country_id'] is the country_id from Table_1
                                        $options .= '<input style="width: 100%; height: 5%;" id="country_data" type="text"  name="' . $row['country_id'] . '"  value="GET_VALUE_FROM_DB" />' . '<br /><br />';
                               }
                              echo $options;

 

This outputs:

Textareas go here......
<label>Test for Australia</label>
<input type="text" value="" name="20" id="country_data" style="width: 100%; height: 5%;">
<label>Test for Zimbabwe</label>
<input type="text" value="" name="17" id="country_data" style="width: 100%; height: 5%;">

 

Now, I need to insert the value of the input field and it's country_id (20 or 17) into Table_3 and also Table_2_id.

This then means I could get the value from Table_3 to populate 'GET_VALUE_FROM_DB'

 

But I'm at a loss on how I'd do this.

 

Could someone help me with this?

 

Thanks