Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    New Coder
    Join Date
    Jul 2008
    Posts
    31
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Inserting Date of Birth into Database

    Hello, wonderful helpful people.

    I've got a register form, in which people input things like username, password, etc. At the moment, for the date of birth, I've just got it as a regular date field which is really useless as it comes up with a calendar showing the current month and it's gona take everyone ages to find their DOB on that!

    I would like to have the standard three separate drop downs for day, month, year - but I'm having trouble thinking - would these have to be all separate values inserting into separate columns in my user table in my database?

    How would I merge them all together so they'd fit into my one DOB column?
    Should my Access database field be a 'date' type, or just a regular text?

    Thanks for any light you can shed, it is very much appreciated.

  • #2
    Supreme Master coder! abduraooft's Avatar
    Join Date
    Mar 2007
    Location
    N/A
    Posts
    14,852
    Thanks
    160
    Thanked 2,223 Times in 2,210 Posts
    Blog Entries
    1
    I would like to have the standard three separate drop downs for day, month, year - but I'm having trouble thinking - would these have to be all separate values inserting into separate columns in my user table in my database?
    No. After proper validation of the values from the drop-downs, just concatenate them to form the required date format.
    How would I merge them all together so they'd fit into my one DOB column?
    Should my Access database field be a 'date' type, or just a regular text?
    It should be a date type.
    The Dream is not what you see in sleep; Dream is the thing which doesn't let you sleep. --(Dr. APJ. Abdul Kalam)

  • #3
    New Coder
    Join Date
    Jul 2008
    Posts
    31
    Thanks
    3
    Thanked 0 Times in 0 Posts
    I've changed that bit of the form into the separate fields, but where am I concatenating the values? I tried it in the destination page by writing

    Code:
    <CFSET dob = Form.birthday & Form.birthmonth & form.birthyear>
    then

    Code:
    <cfinsert datasource="062105cs06sr" tablename="users" formfields="username,createDate,password,firstname,lastname,#dob#,town,postcode,bio">
    But it's not working. As you might be able to tell I'm a bit of a novice at this..! The form worked fine before trying this.

  • #4
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    Hey Suzy. I gotta tell ya, in all my 9 years of ColdFusion development, I have never once used the <cfinsert> tag! I had to look that one up for ya lol. Always just used direct querying instead.

    But after checking it out, I don't think that it's going to work for you in this case. You might have to write out a full database INSERT statement in a <cfquery> tag.

    Try this code:
    Code:
    <cfset dob = form.birthMonth & "/" & form.birthDay & "/" & form.birthYear>
    
    <cfquery datasource="062105cs06sr">
        INSERT INTO users (
            username,
            createDate,
            password,
            firstName,
            lastName,
            dob,
            town,
            postcode,
            bio
        ) VALUES (
            <cfqueryparam value="#form.username#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.createDate#" cfsqltype="CF_SQL_DATE">,
            <cfqueryparam value="#form.password#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.firstname#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#dob#" cfsqltype="CF_SQL_DATE">,
            <cfqueryparam value="#form.town#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.postcode#" cfsqltype="CF_SQL_VARCHAR">,
            <cfqueryparam value="#form.bio#" cfsqltype="CF_SQL_VARCHAR">
        )
    </cfquery>
    The <cfqueryparam> tags are to validate data, and protect you from any SQL injection attacks. You can google that if ya like (can be a big database security issue), but suffice it to say, it is always best to use them

    Hope that helps.

    -Greg

  • Users who have thanked Gjslick for this post:

    suzierthanyou (04-12-2010)

  • #5
    New Coder
    Join Date
    Jul 2008
    Posts
    31
    Thanks
    3
    Thanked 0 Times in 0 Posts
    yes yes yes! Thank you thank you thank you.

  • #6
    Regular Coder
    Join Date
    Feb 2009
    Location
    NJ, USA
    Posts
    476
    Thanks
    2
    Thanked 70 Times in 69 Posts
    No problem Let me know if you need anything else!

    -Greg

  • #7
    New to the CF scene
    Join Date
    Jul 2010
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One thing, after you download the date, or upload it, you can extract the month, day and year from your date field. You could even set up the left(Birthday,2) for month, and go to all that trouble. Dates are strored in long binary numbers. You can use date functions like month() day() or year() to extract just that piece of information. But to SEARCH by just the month, the best way I've found, is to explode the date into three separate fields for storing. I have an online history newspaper, as well as a calendar. You can search for any day of the year, or any year, etc. But it sure helps to explode the date. Love the example above using cfqueryparam. I'm spoiled. I like using the CFINSERT tag, which means all my form fields and values must be set prior (not during) inserting them into the database. ... just sayin'


  •  

    Tags for this Thread

    Posting Permissions

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