My problem is that i have a form on a dating site where when users
sign up i create a row for them in my profile table. When collating
their profile I also ask them what type of dates they'd like to meet,
some of which are love, casual, friendship, travel mate. Each of
these attributes are held in an attributes table. When i have taken
all the user info i create the rows in the two tables. For the
profile info it's pretty straight forward, but for the attributes i
am finding it quite hard.
i'll show you what i am doing.
<!--- Insert profile details --->
<cfquery name="InsertProfileDetails" datasource="user020">
SET NOCOUNT ON
INSERT INTO tbl_020publicProfiles (profileText,
profileTitle, screenName, userID, acceptImages, dateSubmitted,
location, age, gender, genderSeeking, confirmationID, profileIPAddress)
VALUES ('#Form.profileText#', '#Form.title#',
'#Form.screenName#', #CheckUser.userID#, #Form.acceptImages#,
#UKtodayDate#, #Form.location#, '#Form.age#', '#Form.gender#',
'#Form.genderSeeking#', '#confirmationID#', '#cgi.REMOTE_ADDR#')
SELECT @@IDENTITY as profileID
SET NOCOUNT OFF
</cfquery>
<!--- find all date types --->
<cfquery name="FindAttributes" datasource="user020">
SELECT attributeID
FROM tbl_020publicAttributes
</cfquery>
<!--- roll through FindAttributes query inserting the values
into the table --->
<cfoutput query="FindAttributes">
<cfif evaluate('form.attribute' & '_' &
FindAttributes.attributeID) eq 'true'>
<cfquery name="InsertProfilesAttributes"
datasource="user020">
INSERT INTO tbl_020publicProfilesAttributes (profileID,
attributeID)
VALUES (#InsertProfileDetails.profileID#, evaluate
('form.attribute_' & 'FindAttributes.attributeID'))
</cfquery>
</cfif>
</cfoutput>
As you can see i'm having problems with putting the attributes values
in as i am finding that you can't use the CF attribute function in an
SQL statement.
I am also wondering if there is any easier way to all this then the
way i am doing it. Anyone have any ideas or am i doing it right?
Any help at this point would be really helpful.
Thanks,
Stuart
On 14 Jul 2005, at 23:47, Aaron DC wrote:
> It sounds like you are enforcing a 1 to 1 restriction but storing the
> link in a 1 to many or many to many db design. If table1 can only ever
> be linked to 1 table2 record and vice versa, wouldnt it be easier to
> store the table1ID in the table2 as a field (or vice versa)? All your
> queries would have 1 less join, etc.
>
> Aaron
>
> Saturday (Stuart Kidd) wrote:
>
>
>> Hi Jordan,
>>
>> Yes, more than one field needs to be a primary key. I'm using MS-SQL
>> (on shared hosting).
>>
>> Stuart
>>
>>
>> On 14 Jul 2005, at 23:07, Jordan Michaels wrote:
>>
>>
>>
>>
>>> Saturday (Stuart Kidd) wrote:
>>>
>>>
>>>
>>>
>>>
>>>> Hi guys,
>>>>
>>>> I want to add a row in a join table which i don't want to be
>>>> duplicated ever.
>>>>
>>>> So i have my tableID, table1ID, table2ID - if table1ID = 24 and
>>>> table2ID = 35 i don't want there to ever be a reoccurence of
>>>> them. I
>>>> guess i have to set both of those fields (table1ID, table2ID) to
>>>> primary keys, but how can i do that? Do i do it in ms-sql or via
>>>> Coldfusion somehow? If i do it via MS-SQL then won't it pop an
>>>> error
>>>> up in the code if it happens?
>>>>
>>>> Thanks,
>>>>
>>>> Saturday
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>> Are you talking about compound keys? Where more then one field is
>>> the
>>> primary key? This can be done in most databases - even access if I
>>> remember correctly.
>>>
>>> -JM
>>>
>>> --
>>> Warm regards,
>>> Jordan Michaels
>>> Vivio Technologies
>>> http://www.viviotech.net/
>>> [EMAIL PROTECTED]
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
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:211935
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=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
