SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    insert statement query

    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.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,394
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    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?

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2011
    Location
    OH, USA
    Posts
    72
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  4. #4
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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)";

  5. #5
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,394
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    How do the users enter the values in the form? Do they type them in? Or do they choose from a list?

  6. #6
    SitePoint Zealot
    Join Date
    Nov 2006
    Posts
    155
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    They type the values into an input box.

  7. #7
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,394
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by seannie View Post
    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:
    Code:
    <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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •