Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Handling HEIGHT field, ie. X 'XX "
- Thread starter tlieu
- Start date
Hey Tlieu
I'm not aware of any Height datatype, you could though convert the inches to/from the decimal like this:
where 'heightfeet' is the height in feet and 'heightinches' is the inches and 'Dheight' is the Decimal equivelant.
<cfset heightfeet = 5>
<cfset heightinches = 11>
<cfset cv = heightinches / 12 >
<cfset Dheight = heightfeet + cv>
Dheight would now be ready to enter into a DB and will sort correctly for the heighest or lowest.
you simply change your 12 inch scale into a 10 0.91666th of an inch scale making it store easily in a decimal world.
you would convert it back from the DB like this:
<cfset heightfeet = #int(DBheight)# >
<cfset cv = DBheight - heightfeet >
<cfset heightinches = cv * 12 >
Then you can display them anyway you want. These conversions could probably be written more compactly but I didn't want to think about it.
Hope it helps.
Have fun......
I'm not aware of any Height datatype, you could though convert the inches to/from the decimal like this:
where 'heightfeet' is the height in feet and 'heightinches' is the inches and 'Dheight' is the Decimal equivelant.
<cfset heightfeet = 5>
<cfset heightinches = 11>
<cfset cv = heightinches / 12 >
<cfset Dheight = heightfeet + cv>
Dheight would now be ready to enter into a DB and will sort correctly for the heighest or lowest.
you simply change your 12 inch scale into a 10 0.91666th of an inch scale making it store easily in a decimal world.
you would convert it back from the DB like this:
<cfset heightfeet = #int(DBheight)# >
<cfset cv = DBheight - heightfeet >
<cfset heightinches = cv * 12 >
Then you can display them anyway you want. These conversions could probably be written more compactly but I didn't want to think about it.
Hope it helps.
Have fun......
- Thread starter
- #3
Tlieu
The method I suggested lends itself well to that format, the database is going to see it as one number, but your app is going to see it as 2 numbers one for feet and one for inches. Then it is up to you to display it as X'XX" . Just insert those characters. I think it would look like this with the above conversions:
<cfset outheight = "#heightfeet#''#heightinches#""" >
#outheight#
something like that. just figure out how to escape the ' and the " I haven't had coffee yet. If you search this Forum you will find it.
The method I suggested lends itself well to that format, the database is going to see it as one number, but your app is going to see it as 2 numbers one for feet and one for inches. Then it is up to you to display it as X'XX" . Just insert those characters. I think it would look like this with the above conversions:
<cfset outheight = "#heightfeet#''#heightinches#""" >
#outheight#
something like that. just figure out how to escape the ' and the " I haven't had coffee yet. If you search this Forum you will find it.
The previous answer pointed to processing a conversion
prior to storage.
You can process calculations in an order by clause
of your SQL.
<cfquery>
SELECT Height
FROM Table1
ORDER BY CAST(LEFT(Height, CHARINDEX(CHAR(39), Height) - 1) AS INT) * 12 + CAST(REPLACE(RIGHT(Height, LEN(Height) - LEN(LEFT(Height,
CHARINDEX(CHAR(39), Height)))), CHAR(34), '') AS INT)
</cfquery>
This was written in MSSQL2000
Must be a simpler way, but off the top of my head
prior to storage.
You can process calculations in an order by clause
of your SQL.
<cfquery>
SELECT Height
FROM Table1
ORDER BY CAST(LEFT(Height, CHARINDEX(CHAR(39), Height) - 1) AS INT) * 12 + CAST(REPLACE(RIGHT(Height, LEN(Height) - LEN(LEFT(Height,
CHARINDEX(CHAR(39), Height)))), CHAR(34), '') AS INT)
</cfquery>
This was written in MSSQL2000
Must be a simpler way, but off the top of my head
The previous answer pointed to processing a conversion
prior to storage.
You can process calculations in an order by clause
of your SQL.
<cfquery>
SELECT Height
FROM Table1
ORDER BY CAST(LEFT(Height, CHARINDEX(CHAR(39), Height) - 1) AS INT) * 12 + CAST(REPLACE(RIGHT(Height, LEN(Height) - LEN(LEFT(Height,
CHARINDEX(CHAR(39), Height)))), CHAR(34), '') AS INT)
</cfquery>
This was written in MSSQL2000
Must be a simpler way, but off the top of my head
prior to storage.
You can process calculations in an order by clause
of your SQL.
<cfquery>
SELECT Height
FROM Table1
ORDER BY CAST(LEFT(Height, CHARINDEX(CHAR(39), Height) - 1) AS INT) * 12 + CAST(REPLACE(RIGHT(Height, LEN(Height) - LEN(LEFT(Height,
CHARINDEX(CHAR(39), Height)))), CHAR(34), '') AS INT)
</cfquery>
This was written in MSSQL2000
Must be a simpler way, but off the top of my head
- Thread starter
- #8
- Thread starter
- #9
PAGE1:
<form name="height" method="post" action="saveheight.cfm">
<input type="text" name="heightfeet">Feet
<input type="text" name="heightinches">Inches
<input type="submit" value="Submit">
</form>
PAGE2:
<cfset heightfeet = #heightfeet#>
<cfset heightinches = #heightinches#>
<cfset cv = heightinches / 12 >
<cfset dheight = heightfeet + cv>
<cfquery datasource="measure">
INSERT INTO height(dheight) values(#dheight#)
</cfquery>
<!-- cfinsert datasource="test"
tablename="height"
formfields="dheight">
QUESTION: Why does the INSERT method work whereas the cfinsert datasource does not? In the cfinsert case, I end up inserting nothing into the database. I actually wanted to just use the line <cinfsert datasource="measure" tablename="height"> to make things simple but it kept telling me that I should have a heightfeet and heightinches in my DB, which I didn't; I wanted to only have dheight...so I thought by using the formfields="dheight" I could keep things syntactically correct...
Comments and suggestions from the CFM gurus?
<form name="height" method="post" action="saveheight.cfm">
<input type="text" name="heightfeet">Feet
<input type="text" name="heightinches">Inches
<input type="submit" value="Submit">
</form>
PAGE2:
<cfset heightfeet = #heightfeet#>
<cfset heightinches = #heightinches#>
<cfset cv = heightinches / 12 >
<cfset dheight = heightfeet + cv>
<cfquery datasource="measure">
INSERT INTO height(dheight) values(#dheight#)
</cfquery>
<!-- cfinsert datasource="test"
tablename="height"
formfields="dheight">
QUESTION: Why does the INSERT method work whereas the cfinsert datasource does not? In the cfinsert case, I end up inserting nothing into the database. I actually wanted to just use the line <cinfsert datasource="measure" tablename="height"> to make things simple but it kept telling me that I should have a heightfeet and heightinches in my DB, which I didn't; I wanted to only have dheight...so I thought by using the formfields="dheight" I could keep things syntactically correct...
Comments and suggestions from the CFM gurus?
- Status
Similar threads
