insert statement query

[seannie]

I have a table called members and a number of foreign keys in this table for the purpose of joining from other tables.

Examples of the foreign ids are ageid,genderid. In the age and gender tables there are accompanying values for the ids (ie. agevalue = 24, gendervalue=female)

When I'm doing a sql insert query can I allow for someone to complete a form by adding the values rather than ids and then populate the member table with the respective id.

Would I have to use a subquery within the insert select using a where clause to specify inserting id where value is returned.

[guido2004]

In the case of gender, I would put a select box in the form with the two allowed values (male and female) displayed to the user to choose from, and the corresponding id in the value property of the options.

In the case of age, I really don't see the use of foreign keys and an "age" table?

[gk53]

I think to keep age in database is not really good idea. Age is variable DOB never changes and should be in database if you need to know age...

[seannie]

Thanks for your help.

I'm still getting an issue when I'm trying to insert foreign keys when respective value is entered. Here's what I have do. I'd appreciate any assistance:

-Values posted from form which I put into variables:

$type_desc=$_POST['type_desc'];
$dept_desc=$_POST['dept_desc'];
$sect_desc=$_POST['sect_desc'];

-Extract foreign key values usiing seperate sql statements:

$type_result="select type_id from tblType where type_desc='$type_desc'";
$dept_result="select dept_id from tblDept where dept_desc='$dept_desc'";
$sect_result="select sect_id from tblSect where sect_desc='$sect_desc'";

-Insert foreign key values where the respecftive value was entered:

$result = "INSERT INTO tblTest (type_id,dept_id,sect_id)
VALUES
($type_result,$dept_result,$sect_result)";

[guido2004]

How do the users enter the values in the form? Do they type them in? Or do they choose from a list?

[seannie]

They type the values into an input box.

[guido2004]

They type the values into an input box.

Then how can you be sure the value exists in the respective tables?
If you want to give the user the possibility to add any value, then there's no need for foreign keys and other tables.
If you want them to choose one of the existing values from the type, dept or sect table, then just load those values in a select box, and put the foreign key in the value property of the option, like this:

<select name="type">
  <option value="key1">value 1</option>
  <option value="key2">value 2</option>
  <option value="key3">value 3</option>
</select>

This way, in your script you'll already have the foreign key value.