CodingForums.com - Inserting Date of Birth into Database

CodingForums.com (http://www.codingforums.com/index.php)

- ColdFusion (http://www.codingforums.com/forumdisplay.php?f=45)

- - Inserting Date of Birth into Database (http://www.codingforums.com/showthread.php?t=193578)

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.

Quote:

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.

Quote:

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.

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.

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 :p 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

yes yes yes! Thank you thank you thank you. :)

No problem :) Let me know if you need anything else!

-Greg

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'