SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Dealing with ages and heights?

    Hi

    How would you suggest dealing with user ages and heights?

    What I thought of doing is inserting ages and heights into a MySQL table along the lines of:

    PHP Code:
    $sql10 mysql_query("CREATE TABLE Height (
                        heightID int(2) auto_increment PRIMARY KEY,
                        heightValue ***"
    );

    $sql12 mysql_query("CREATE TABLE Age (
                        ageID int(2) auto_increment PRIMARY KEY,
                        ageValue int(2)"
    ); 
    What I was wondering is:

    1. Is this okay?
    2. What type would be best to use for 'heightValue' which would contain something like: '6ft 1in - 185cm', for example.
    3. Is the type for 'ageValue' okay? A typical value would be '22', for example.

    Thanks.

    Mak.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    height in cm will need three digits, you can convert it on output

    age in years should have three digits

    i would not put these attributes into their own tables

    instead, just make them part of the people table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I suggest inserting dates of birth rather than ages, otherwise you'll have to update each age once a year to avoid it being out of date. If you store birth dates, they are correct for ever, and it's child's play turning them into ages upon demand!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!

  4. #4
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Firstly, regarding heights. I litrerally want people to select a height in the form '6ft 1in - 185cm', for example. So what type do you suggest I use for that? Just varchar? Should I have these pre-stored in a height table ot just use option values that are inserted each time into my main 'member' table with a 'height' field? I just thought it be better in a seperate table from an abstraction point of view and also would make searching for people by height somewhat easier?

    In regards to ages, I want the user to select the day, month and year of birth. i.e. '31' 'January' '1980' from drop downboxes. I then want to get the results and do: $DOB = $year . $month . $day; and insert $DOB into the dB.
    (Does this make the 'age' table redundant?)
    I also want to calculate the age from the DOB and use it so that users can search by age.. how would I go about that?

    Thanks.

    Mak.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i agree, put your heights into a separate table, and use this to populate your dropdown select options

    you'll find it easier writing searches if you code them with the cm first (i.e. you can't say "where height between 5ft 9in and 5ft 11in" because they don't collate the right way)

    you don't need a separate age table, just store the dob for each person in a date field, and calculate ages on the fly, i.e. in the select statement
    Code:
    select (YEAR(CURRENT_DATE)-YEAR(birth))
          - (RIGHT(CURRENT_DATE,5) < RIGHT(birth,5))
              AS age
    see 3.3.4.5 Date Calculations
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    Thanks.. that sounds good.. shall give it a go.

    One query, however. Suppose I include heights in a seperate table, in ascending order (CM). How then do I populate a drop down selection box to also display the equivalent in feet and inches along with the CM measurement?

    Thanks.

    Mak.
    Last edited by mak-uk; Aug 12, 2002 at 16:39.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, you said "I literally want people to select a height in the form '6ft 1in - 185cm', for example" so that's why i suggesting storing these customized strings in a table

    i assumed it would be just as meaningful to store '185cm - 6ft 1in' as the other way around

    if you use the cm value as the primary key for this table, this would allow you to use the foreign key in the person table as actual data when that makes sense
    Code:
    create table heights
     ( heightcm int
     , height varchar(15)
     );
    insert into heights values
     (160, '160cm - 5ft 3in')
    ,(165, '165cm - 5ft 5in')
    ,(170, '170cm - 5ft 7in')
    then to populate the dropdown,
    Code:
    select heightcm, height
      from heights 
    order by heightcm
    and then use
    Code:
    <select name=heights>
    <option value=heightcm>height</option>
    </select>
    for the dropdown list, passing back the heightcm value selected

  8. #8
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Hi

    Thank you.

    Now, supposing I have the following:

    PHP Code:
    <TR VALIGN="middle"
                  <
    TD>Date Of Birth:</TD>
                  <
    TD> <SELECT NAME="day_of_birth" alt="select">
                      <
    OPTION SELECTED VALUE="">---------------</OPTION>
                      <
    OPTION>01</OPTION>
                      <
    OPTION>02</OPTION>
                      <
    OPTION>03</OPTION>
                      <
    OPTION>04</OPTION>
                      <
    OPTION>05</OPTION>
                      <
    OPTION>06</OPTION>
                      <
    OPTION>07</OPTION>
                      <
    OPTION>08</OPTION>
                      <
    OPTION>09</OPTION>
                      <
    OPTION>10</OPTION>
                      <
    OPTION>11</OPTION>
                      <
    OPTION>12</OPTION>
                      <
    OPTION>13</OPTION>
                      <
    OPTION>14</OPTION>
                      <
    OPTION>15</OPTION>
                      <
    OPTION>16</OPTION>
                      <
    OPTION>17</OPTION>
                      <
    OPTION>18</OPTION>
                      <
    OPTION>19</OPTION>
                      <
    OPTION>20</OPTION>
                      <
    OPTION>21</OPTION>
                      <
    OPTION>22</OPTION>
                      <
    OPTION>23</OPTION>
                      <
    OPTION>24</OPTION>
                      <
    OPTION>25</OPTION>
                      <
    OPTION>26</OPTION>
                      <
    OPTION>27</OPTION>
                      <
    OPTION>28</OPTION>
                      <
    OPTION>29</OPTION>
                      <
    OPTION>30</OPTION>
                      <
    OPTION>31</OPTION>
                    </
    SELECT> <SELECT NAME="month_of_birth" alt="select">
                      <
    OPTION SELECTED VALUE="">---------------</OPTION>
                      <
    OPTION VALUE=01>Jan</OPTION>
                      <
    OPTION VALUE=02>Feb</OPTION>
                      <
    OPTION VALUE=03>Mar</OPTION>
                      <
    OPTION VALUE=04>Apr</OPTION>
                      <
    OPTION VALUE=05>May</OPTION>
                      <
    OPTION VALUE=06>Jun</OPTION>
                      <
    OPTION VALUE=07>Jul</OPTION>
                      <
    OPTION VALUE=08>Aug</OPTION>
                      <
    OPTION VALUE=09>Sep</OPTION>
                      <
    OPTION VALUE=10>Oct</OPTION>
                      <
    OPTION VALUE=11>Nov</OPTION>
                      <
    OPTION VALUE=12>Dec</OPTION>
                    </
    SELECT> <SELECT NAME="year_of_birth" alt="select">
                      <
    OPTION SELECTED VALUE="">---------------</OPTION>
                      <
    OPTION>1912</OPTION>
                      <
    OPTION>1913</OPTION>
                      <
    OPTION>1914</OPTION>
                      <
    OPTION>1915</OPTION>
                      <
    OPTION>1916</OPTION>
                      <
    OPTION>1917</OPTION>
                      <
    OPTION>1918</OPTION>
                      <
    OPTION>1919</OPTION>
                      <
    OPTION>1920</OPTION>
                      <
    OPTION>1921</OPTION>
                      <
    OPTION>1922</OPTION>
                      <
    OPTION>1923</OPTION>
                      <
    OPTION>1924</OPTION>
                      <
    OPTION>1925</OPTION>
                      <
    OPTION>1926</OPTION>
                      <
    OPTION>1927</OPTION>
                      <
    OPTION>1928</OPTION>
                      <
    OPTION>1929</OPTION>
                      <
    OPTION>1930</OPTION>
                      <
    OPTION>1931</OPTION>
                      <
    OPTION>1932</OPTION>
                      <
    OPTION>1933</OPTION>
                      <
    OPTION>1934</OPTION>
                      <
    OPTION>1935</OPTION>
                      <
    OPTION>1936</OPTION>
                      <
    OPTION>1937</OPTION>
                      <
    OPTION>1938</OPTION>
                      <
    OPTION>1939</OPTION>
                      <
    OPTION>1940</OPTION>
                      <
    OPTION>1941</OPTION>
                      <
    OPTION>1942</OPTION>
                      <
    OPTION>1943</OPTION>
                      <
    OPTION>1944</OPTION>
                      <
    OPTION>1945</OPTION>
                      <
    OPTION>1946</OPTION>
                      <
    OPTION>1947</OPTION>
                      <
    OPTION>1948</OPTION>
                      <
    OPTION>1949</OPTION>
                      <
    OPTION>1950</OPTION>
                      <
    OPTION>1951</OPTION>
                      <
    OPTION>1952</OPTION>
                      <
    OPTION>1953</OPTION>
                      <
    OPTION>1954</OPTION>
                      <
    OPTION>1955</OPTION>
                      <
    OPTION>1956</OPTION>
                      <
    OPTION>1957</OPTION>
                      <
    OPTION>1958</OPTION>
                      <
    OPTION>1959</OPTION>
                      <
    OPTION>1960</OPTION>
                      <
    OPTION>1961</OPTION>
                      <
    OPTION>1962</OPTION>
                      <
    OPTION>1963</OPTION>
                      <
    OPTION>1964</OPTION>
                      <
    OPTION>1965</OPTION>
                      <
    OPTION>1966</OPTION>
                      <
    OPTION>1967</OPTION>
                      <
    OPTION>1968</OPTION>
                      <
    OPTION>1969</OPTION>
                      <
    OPTION>1970</OPTION>
                      <
    OPTION>1971</OPTION>
                      <
    OPTION>1972</OPTION>
                      <
    OPTION>1973</OPTION>
                      <
    OPTION>1974</OPTION>
                      <
    OPTION>1975</OPTION>
                      <
    OPTION>1976</OPTION>
                      <
    OPTION>1977</OPTION>
                      <
    OPTION>1978</OPTION>
                      <
    OPTION>1979</OPTION>
                      <
    OPTION>1980</OPTION>
                      <
    OPTION>1981</OPTION>
                      <
    OPTION>1982</OPTION>
                      <
    OPTION>1983</OPTION>
                      <
    OPTION>1984</OPTION>
                    </
    SELECT></TD>
                </
    TR
    What would be the easiest way to insert data into the DOB=$DOB field in the database?

    Would it be as follows:

    $DOB = $year_of_birth . $month_of_birth . $day_of_birth;

    If so, at each stage of the seletion process (using the drop-down boxes), how do I store the choice in a particular variable, i.e. $year_of_birth , $month_of_birth and $day_of_birth ?

    Thanks.

    Mak.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'm going to have to let someone who knows php take over

    don't forget to edit the selections, because they could come back as zero-length strings if the form's SELECTED values are accidentally submitted without being changed

    you don't store them at each stage of the selection process, you store them when the entire collection of form fields is transmitted by the SUBMIT button

    rudy

  10. #10
    SitePoint Addict mak-uk's Avatar
    Join Date
    Dec 2001
    Location
    Midlands, UK
    Posts
    284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    Hi

    Can anyone else help?

    Thanks.

    Mak.


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
  •