Use LEFT JOIN inserting into MySQL via Form

Question

I have made a form with some text fields that are inserted manualy.

All these fields are filled in Table 1, except 1. I have made a dropmenu menu which is calling information from table 2.

<div>Geslacht</div>
<select name='geslacht'>
<?php 
    while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { ?>
    <option value="<?php echo $line['Geslacht'];?>"> <?php echo $line['Geslacht'];?> </option>

So far so good, the drop down menu does select the information.

What i want to accomplish:

I want to link the field in table 1 to the field in table 2, so when i change the value in table 2, say like: change Man to Male, every Person in table 1, gets the value Man changed to Male.

Table 1 structure: PersonID Name Gender 1 John link to table 2

Table 2 structure: ID Gender 1 Male 2 Female

I have been told to use a LEFT JOIN for this, but i'm lost, i have no clue where to start this. Can anyone give me starts/heads up?

My current INSERT code is:

mysql_query("INSERT INTO Persoon (voornaam, tussenvoegsel, achternaam,
        straatnaam, huisnummer, toevoeging, bsnnummer, geboortedatum, geslacht) VALUES ('".$persoon_voornaam."', '".$persoon_tussenvoegsel."', '".$persoon_achternaam."',
         '".$persoon_straatnaam."', '".$persoon_huisnummer."', '".$persoon_toevoeging."', '".$persoon_bsnnummer."', '".$persoon_geboortedatum."', '".$persoon_geslacht."')") or die (mysql_error());

Josh Toth Josh Toth · Accepted Answer · 2015-01-15T20:26:57

So the join doesn't come into play until you're SELECTing data back :)

Table 1 fields:

id INT(11) UNSIGNED AUTO_INCREMENT
name VARCHAR(100) NOT NULL
gender INT(11) UNSIGNED

Table 2 fields:

gender_id INT(11) UNSIGNED AUTO_INCREMENT
gender_name VARCHAR(20) NOT NULL

So let's say you have two entries in table 2: 1 = male and 2 = female.

When you insert into Table 1, you'll utilize the 1 and 2 instead of 'male' and 'female.'

INSERT INTO table1 (name, gender) VALUES ('Josh', 1);

When you go to SELECT the information back, you'll use the LEFT JOIN:

SELECT * FROM table1 t1 LEFT JOIN table2 t2 ON t1.gender = t2.gender_id WHERE t1.name = 'Josh';

Then in your result set, you'll have all of your normal data PLUS whatever was linked from table2, i.e. gender_name.

Hopefully that made some sense.

Use LEFT JOIN inserting into MySQL via Form

1 Answers