You should have a people table and you should have a relationship types
table and you should have a people's relationship types table.
people
-userid
-blah
-blah
relationship_types
-typeid
-typeDesc
people_relationship_types
-userid
-typeid
then name the checkboxes the same with different values and then:
select distinct userid
from people p
left join people_relationship_types prt on p.userid = prt.userid
left join relationship_types rt on rt.typeid = prt.typeid
where rt.typeid in(#form.relationshipTypes#)
and I'm sure you can do it in other db's too with different syntax and
there are probably other ways to accomplish the same goal, but if you're
using MySQL, you could just do:
select distinct p.userid, group_concat(rt.typedesc) as typeDesc
from people p
left join people_relationship_types prt on p.userid = prt.userid
left join relationship_types rt on rt.typeid = prt.typeid
group by p.userid
where rt.typeid in(#form.relationshipTypes#)
Someone may not like my left join query there, but that's my first thought.
--Ferg
Saturday (Stuart Kidd) wrote:
>Thanks Deanna,
>
>So do you mean i should create a table and in that i should have the
>different types of relationships, ie Love, Friendship, Travelmate,
>Casual as separate rows?
>
>Thanks,
>
>Saturday
>
>
>On 7 Jul 2005, at 21:05, Deanna Schneider wrote:
>
>
>
>>I yi yi...it sounds like your DB design could use a good kick in the
>>pants, is what it sounds like. If I were doing it, I'd make the db
>>design a many to many relationship, and I'd name the checkboxes all
>>the same thing, and then I'd have
>>
>>SELECT ...
>>FROM maintable m, typejointable t
>>WHERE m.id = t.id
>>AND t.relationshipTypes IN (<cfqueryparam cfsqltype="cf_sql_integer"
>>list="yes" value="#form.relationshipTypes#">)
>>
>>
>>
>>On 7/7/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>>
>>
>>
>>>Hi guys,
>>>
>>>I've got a form which has 4 checkboxes - relationshipType1,
>>>relationshipType2, relationshipType3 and relationshipType4. Getting
>>>this information I then use it in a select statement.
>>>
>>>In my WHERE clause i put something like:
>>>
>>>WHERE gender = '#form.genderSeeking#' AND genderSeeking =
>>>'#form.gender#'<cfif form.relationshipType1 eq 'true'> AND
>>>relationshipTypes LIKE '%1%'</cfif><cfif form.relationshipType2 eq
>>>'true'> AND relationshipTypes LIKE '%2%'</cfif><cfif
>>>form.relationshipType3 eq 'true'> AND relationshipTypes LIKE '%3%'</
>>>cfif><cfif form.relationshipType4 eq 'true'> AND relationshipTypes
>>>LIKE '%4%'</cfif>
>>>
>>>In my DB there relationshipType may equal something like '1,2,3,4' or
>>>'1,3' etc, any of the combinations of the 4 types.
>>>
>>>I have realised that my logic isn't correct... is there an easy way
>>>to check whether relationshipTypes (in my DB) = one of my four form
>>>checkboxes?
>>>
>>>Thanks for your help,
>>>
>>>Saturday
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211386
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
