#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Location
    Atlanta, GA, USA (unfortunately)
    Posts
    59
    Rep Power
    5

    Lightbulb Age Range Lookup (2nd Table or All PHP)


    Hello all,

    This thread is to discuss the way I am going to write the user's age range to the database. Apparently, writing an age range straight from the HTML markup (even though the ranges are selected from a drop-down) isn't the best way to promote data integrity.

    So, here's what I have done. A number, 0-4, is being written to the `emailcollection` table instead of the actual value that the user selects from. I made a new table in the same database, with the name of agerange, which contains four rows of data inside its two columns, id and description. It looks like this:

    id | description
    0 | -unspecified-
    1 | 18-35
    2 | 36-55
    3 | 55+
    4 | 17 or younger

    Code:
                <label for="age" class="fixedwidth">Select your age range:</label>
                <select name="age" id="age" class="inputwidth">
                  <option selected="selected" value="0">Select an Age Range</option>
                  <option value="1">18-35</option>
                  <option value="2">36-55</option>
                  <option value="3">55+</option>
                  <option value="4">17 or younger</option>
    Then, my friend had me do a JOIN query on the two tables, which apparently I have to do each and every time to get the actual age range to show.

    What I want is for the age ID (The 0-4 digit) to be converted to its corresponding age range and have the actual age range, say 18-35, be written to the `emailcollection` table in a way that promotes data integrity. I want this to occur every single time without doing a JOIN query. Maybe all I should do is check the value received from the form in PHP and then change that number to the age range I want to see in the database (I care nothing for the 0-4 digit), accomplishing all of this without the need for a 2nd table, and doing everything in PHP before anything is written to the database.

    Many thanks for insight and knowledge,
    Tyler
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,300
    Rep Power
    7170
    Using a second table and a JOIN is the correct approach.

    This is a pretty minor JOIN, but if typing it is too much trouble you could create a view.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by etidd
    Apparently, writing an age range straight from the HTML markup (even though the ranges are selected from a drop-down) isn't the best way to promote data integrity.
    no, apparently you've misunderstood something

    i recommended an integer because it's simpler, and easier to check for sql injection

    it's also slightly more efficient to store an integer in the table rather than the actual string value

    Originally Posted by etidd
    What I want is for the age ID (The 0-4 digit) to be converted to its corresponding age range and have the actual age range, say 18-35, be written to the `emailcollection` table in a way that promotes data integrity.
    if you want the age range itself to be stored, don't use the integer at all, but do keep the age ranges table

    data integrity is ensured using a foreign key

    do some research on foreign keys and then ask again if you don't understand how a foreign key will help in this situation
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2009
    Location
    Atlanta, GA, USA (unfortunately)
    Posts
    59
    Rep Power
    5
    Ugh! So, I spoke with my web host (FatCow) today, and I was told they don't support foreign keys. I tried to follow some tutorials to set up some foreign keys, starting by switching the storage engine to InnoDB (which, apparently, they don't support either )

    Now, I need a new method to go about this, or maybe this whole objective is just dissolving before our eyes.

    What was this talk about setting up a view?

    I don't know where to go from here as of this point.

    -Tyler
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,026
    Rep Power
    4210
    Originally Posted by etidd
    I don't know where to go from here as of this point.
    new host
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo