Creating a Forum

[stuart1231]

Hi there,

Im creating a forum and which to create a topic entry page, I want to send the
topic name to the 'topics' table and the message to the 'messages'

BUT! I will have to get the new topic id from the 'topics' table, which is an
auto number, so how do i go around it?

Current code is:




<cfquery name="inserttopic" datasource="scd">
INSERT INTO topics (ForumID, TopicName, CreatedBy, DateCreated)
VALUES ('#form.ForumID#', '#form.TopicName#', '#form.Username#',
'#DateFormat(Now())#');

</cfquery>




<cfquery name="topicID" datasource="scd">
SELECT TopicID
FROM topics
WHERE TopicID = '#inserttopic.TopicID#';
</cfquery>

<cfquery name="insertmessage" datasource="scd">
INSERT INTO messages (TopicID, TopicName, Message, DateCreated)
VALUES ('#TopicID#', '#form.Username#', '#form.Message#',
'#DateFormat(Now())#');

</cfquery>

Thus I have tried to retrieve the TopicID after the topic has been created,
but its not working.

Any help you be great


<cfquery name="inserttopic" datasource="scd">
INSERT INTO topics (ForumID, TopicName, CreatedBy, DateCreated)
VALUES ('#form.ForumID#', '#form.TopicName#', '#form.Username#',
'#DateFormat(Now())#');

</cfquery>




<cfquery name="topicID" datasource="scd">
SELECT TopicID
FROM topics
WHERE TopicID = '#inserttopic.TopicID#';
</cfquery>

<cfquery name="insertmessage" datasource="scd">
INSERT INTO messages (TopicID, TopicName, Message, DateCreated)
VALUES ('#TopicID#', '#form.Username#', '#form.Message#',
'#DateFormat(Now())#');

</cfquery>

[mxstu]

It depends on what database you're using. In SQL Server or ACCESS you could
use CFTRANSACTION
with @@IDENTITY to retrieve the ID of the last topic inserted. CFTRANSACTION
ensures that you will retrieve the ID of the correct "topic".

I noticed you're using single quotes around the "forumID". If "forumID" is a
numeric, you do not need the single quotes. Single quotes are not required when
inserting into a numeric column. Note, I did not remove the single quotes from
your code sample.

You could also use the database date functions to insert the current date,
instead of CF functions.
For Access the syntax is...

--- note, there are no quotes or # signs around the function name
VALUES (...., now() )

Or for SQL Server ...

VALUES (...., getDate() )



<!--- not tested --->
<cftransaction (...etc...)>
<cfquery name="inserttopic" datasource="scd">
INSERT INTO topics (ForumID, TopicName, CreatedBy, DateCreated)
VALUES ('#form.ForumID#', '#form.TopicName#', '#form.Username#',
'#DateFormat(Now())#');
</cfquery>

<cfquery name="topicID" datasource="scd">
SELECT @@IDENTITY AS NewTopicID
</cfquery>

<cfquery name="insertmessage" datasource="scd">
INSERT INTO messages (TopicID, TopicName, Message, DateCreated)
VALUES ('#topicID.NewTopicID#', '#form.Username#', '#form.Message#',
'#DateFormat(Now())#');
</cfquery>

</cftransaction>

[stuart1231]

thanks for the reply, i have changed the date formating many thanks.

I am using an access database.

I entered your code and it is now saying:

CFTRANSACTION is unsupported.

I just have <cftransaction> at the beginning,

Do u know whats wrong

(never used cftransaction before)

[mxstu]

The version you are using may not support this tag ... exactly what version of
CF are you using ?


[url]http://livedocs.macromedia.com/coldfusion/7/htmldocs/wwhelp/wwhimpl/common/html/[/url]
wwhelp.htm?context=ColdFusion_Documentation&file=0 0000346.htm

[stuart1231]

ahh there is the problem!!!

I am using Allaire ColdFusion 4.5

I see my fault!

[mxstu]

I believe CFTRANSACTION existed even in CF 4, so the version number might not
be the problem. Which version of CF 4.5 are you using? If you're using the
"Express" version then I think it is probably not supported. "Express" was
very limited and did not support a number of tags.

[stuart1231]

it is express,

Can i not just download the new coldfusion mx 7 developers as i am just using this pc as a server before the website is launched on a hosted coldfusion server somewhere else

[mxstu]

Yep!


On another note, I just took a closer look at your sql statements and you may
want think about modifying your table structure.

A) It's not a good idea to store the "TopicName" in two places (Topics and
Messages) because if the topic name in one table changes, then the records in
the other table get out-of-synch

B) If the "CreatedBy" column stores a text username (like "aJohnson"), it
would be better to use store a numeric user ID instead (for the same reason
stated above, plus a search on a numeric column is usually faster than
searching a "text" column).


A better structure might be ...

Table (YourUserTableName)
====================
UserID (AutoNumber)
UserName (text)
... other fields...


Table [Topics]
====================
TopicID (Autonumber)
ForumID (number)
TopicName (Text)
CreatedBy (number)
DateCreated (date/time) * Default = now()

Table [Messages]
====================
MessageID (Autonumber)
TopicID (number)
Message (Text or Memo)
CreatedBy (number)
DateCreated (date/time) * Default = now()

[stuart1231]

thanks very much, i have taken you comments and changed parts

Yes that typo caused alot of problems!!!

Thanks again

Stu

[mxstu]

You're welcome. Best of luck!