ASP / SQL ... - Tech Support Forums

Agent_Embryo · January 18th, 2002, 06:11 AM

Well. I'm trying to make a simple(well we'll see how that turns out...hehe) forum using ASP and an Access database(this is just for learning, so Access will do, and converting the forum for use with like MySQL isn't all that difficult (just changing the connection code) as far as I understand.

I need help to write a function that queries the database for the last 20 or so topics that have been replied to. I think it would be something like:

I have one table for topics and one for replies. Both have date/time fields.

Well advice is greatly appriciated. If you need to know anything just ask. I'm gonna make the form to post topics/replies now.

vass0922 · January 18th, 2002, 03:43 PM

Well in T SQL you could

SELECT TOP 20
FROM Replies
ORDER BY Date

Dont think mysql supports TOP, but not sure about that...

There is a way to do it pl/sql as well, but can't remember that one...

SO your best bet will probably to do it in the asp function

Private Function TopListings() AS SomeDataType

Getsomedata ordered by date from replies
While data.Recordcount <=30
showsomedata
endloop

End Function

If you wanted to show more data next time you could modify it to have a Static variable in there to keep the value of the variable when you leave the function, that way the function knows to show the 2nd group of 30 next time...

That help?
Or totally off base?

qball · January 18th, 2002, 09:02 PM

That is a good question.

Iffin your DBMS supports "TOP 20" or "SHOW FIRST 10 ROWS ONLY", then you have your answer right there (need ORDER BY date field).

I would question why you feel you need a:

Quote:

write a function that queries the database for the last 20 or so topics...

Most BBs query back 30 days (good ones by user preference, btw) and then shows a given number of results per page (again, good ones by user preference, btw) with links to extra results, if any.

I personally happen to like this behavior/functionality. It is very scalable and always works. It can be written generically and used for as many forums you want, thus you write it once and use everywhere.

Now here's why I don't like last 20/30/40 result function. What if your forums are very popular and get 800 posts in 40 seconds, your display 20 will only show a very limited number of posts and not very current.

Ok, let's suppose we use last 20 function for first page and some other function to see iffin more than 20 posts are current and add links for them. This seems more trouble than it is worth, as described above handles this situation quite easily.

Good luck.

Agent_Embryo · January 21st, 2002, 04:28 AM

Good point qball.

I'm about to go head to head with this problem. And I think I'll go for active topics in the last 10(or something) days...Thanks for the advice!

Agent_Embryo · January 23rd, 2002, 08:39 AM

Update:

Well the basic Forum is now working(maybe not in the most efficient way, but it works

). The topic listing page is still simple and shows all topics. They're still sorted by when the topic was added and not after when the latest reply was made, with the latest showing up on top of the page.

Right now one can register, post topics and replies and delete ones own replies.

If anyone has any suggestions feel free to post them here.

The site isn't public (and might not be ever) so unfortunately you can't visit it.

qball · January 23rd, 2002, 06:00 PM

I've always found whomever 'uses' the software can most often make the best suggestions for improvements. Though I've also found a lot of developers that seem to think they know all (btw, they t'aint very good developers). So monitor user feedback. Sometimes the best suggestions come from the least technical people...

vass0922 · January 23rd, 2002, 06:07 PM

I agree with qball.

Also.. dont try to debug you're own software alone

If you were to really produce that for the public you have to let others test it.. the developer knows what to avoid and will do so

^hyd^ · January 24th, 2002, 02:23 AM

I totally agree with qball and vass0922, the best way to find (hidden?) errors is to let someone who has never used it to have a go at it!! While doing programs in college, myself and a couple good buddies used to send the executables to each other and say:
"Here, try to crash it, and if you do, tell me what you did to crash it so I can fix it!!"

Worked pretty good, but we were all coders, so there's still less chance of user error.

anyways, cyas!

Agent_Embryo · February 7th, 2002, 05:32 AM

Ok. I need more help. Been pondering this for a while now...

I have a table called Topics where all the topics are stored, anda table called Replies where I store all replies corresponding to the Topics(Replies have a column called TopicID linked to the column TopicID in Topics). Both tables have dates.

Right now the main forum page lists Topics in order of the Date the Topic was added.

How do I write SQL that sorts the Topics by Date of the latest reply(or the TopicDate when there's no replies)? Can I even do this in SQL or do I have to do it somehow else?

vass0922 · February 7th, 2002, 06:12 PM

What DB are you using?

Not sure if you can use this sytanx by lemme give it a try...

Code:

SELECT * 
FROM Topics top
    INNER JOIN Replies rep
         ON top.TopicId = rep.TopicId
ORDER BY MAX(rep.Date)

See if that works, if you're in MySQL not sure if it'll like the max

If this doesn't work you could put a DateModified column in your threads table, and have it updated each time there is a reply to a thread.

Hope it helps