[De.camm]

Here I want to get the status for every selected people "id", the problem is the column gender in the master table had messy data, means, some had "F" status for Female, and some had "Female", also happen to male status.
To get the data for the new interface (update part), I make the status in the text field as "read-only" field, and the data sometime not appear.

My code

<cfquery name="QgenDer" datasource="#Variables.fw.Config.DSN#">
	SELECT * FROM Stu_jantina
        WHERE description='#qStuD.stu_gender#'
      </cfquery>
      <cfoutput>
        <input name="GenDer" type="text" id="GenDer" value="#QgenDer.description#" style="border:none;background:none"
	<cfif #qStuD.stu_gender# EQ #QgenDer.description#>#description#</cfif> />
      </cfoutput>

[Craig328]

This is a good example (along with a few of your previous questions) of how, while you're doing CF work, that you really need to acquire skills in other disciplines like Javascript, SQL, CSS and so on. CF all by itself is a very capable and flexible language and, even in this case, could solve your issue. However there's usually more than one way to solve a problem and this is a good example.

Sometimes as CF devs we inherit a project/application that was less than perfectly written. A good example is what De.camm here is dealing with: crappy data. Some CF devs either only ever acquire basic SQL skills or they don't bother to acquire them at all, leaving the SQL work to perhaps a staff DBA. Since the very strength of CF is in working with data and databases, a solid SQL skillset is, in my opinion, probably the most indispensable complimentary skill a CF dev can have.

In this case, you have a table that was probably defined poorly to begin with and was haphazardly populated with data as time went on. Gender in particular is a pretty easy column to define in a database. You really have only three options for a gender column: male, female and null (not answered yet or unknown). This means you could set the column's datatype as something as simple as Bit and then use 1 for male, 0 for female and default the column to null. Data integrity is something that's all too often ignored and/or abused by devs and it results in problems like you're having. That said, there is a way to make it all work.

You could use CF to build logic that essentially says if X = "M" or "Male" or "1" or "Dude" or "Hombre" then "M". You'd use a CFSWITCH/CFCASE or a series of CFIF/CFELSEIF/CFELSE to do that with to default your form value and this would/could work. There is a second way though and that's by using a special kind of SQL to format the data retrieval. Same concept, different place...and it would look something like this:

<cfquery name="QgenDer" datasource="#Variables.fw.Config.DSN#">
   SELECT Stu_jantina.*,
     CASE WHEN Stu_jantina.description = 'F' THEN 'F'
     WHEN Stu_jantina.description = 'Female' THEN 'F'
     WHEN Stu_jantina.description = 'M' THEN 'M'
     WHEN Stu_jantina.description = 'Male' THEN 'M'
     ELSE Stu_jantina.description
     END AS genderDesc
   FROM Stu_jantina
   WHERE description='#qStuD.stu_gender#'
</cfquery>

Now, that code above is based on a MySQL database. SQL Server, Oracle, Informix and pretty much every other SQL based database has similar functionality. The concept is that you filter what's in the column and output a particular value based on possible values you're aware are in that column to begin with and you name them something unique (in this case "genderDesc") and you use the unique named field in your CF output.

This by itself does not standardize the data but it does separate where you handle and manage the bad data. In my opinion, if it's a data problem and not a CF problem the more appropriate place to handle it is on the database side. In addition to personal preference, a database will actually process the data filtering/sorting faster than a comparable CFSWITCH or CFIF solution. Basically, it pushes the crunching cycles onto the database rather than have the webserver do it...and since the function exists in databases and this is what databases are built to do, the process runs quicker there. That and, if you ever get around to writing additional SQL to update your database so that the data is all standardized, you won't need to alter your CF to reflect that. As far as your CF is concerned, the only values you should be seeing/expecting out for gender will be 'F' and 'M'.

Anyway, that should help you get over the hurdle and you might consider pulling a list of distinct values for the description field describing gender and then doing a mass UPDATE SQL to change all occurrances of, for instance, 'Male' to 'M' and so forth.

Good luck!

[De.camm]

Thanks Craig, its really helping me..