Snitz Forums 2000 - Sort Database with 2 fields

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Community Forums

Community Discussions (All other subjects)

Sort Database with 2 fields

New Topic

Topic Locked

Printer Friendly

Author

Topic

Page: of 2

Classicmotorcycling
Development Team Leader

Australia
2084 Posts

Posted - 02 January 2005 : 21:32:31

That would be cool Dayve..

quote:
Originally posted by dayve

quote:
Originally posted by D3mon

Oh dear. that's not good at all. Quick and dirty fix might be to use a query for each day of the week (means 7 calls to the DB though!)

strSQL = "... WHERE new_semester_day = 'monday'" etc..

I have a better method than that which will not put such a burden on the database (using the memory recordset).
I have family over right now, but I can give you the code needed to accomplish this tomorrow.

I hang out until then. Thanks heaps to both of you, D3mon and Dayve..

Cheers,

David Greening

dayve
Forum Moderator

USA
5820 Posts

Posted - 02 January 2005 : 21:34:09

I had to do something similar awhile ago, not a date situation, but a calulated field based on an existing recordset:

http://forum.snitz.com/forum/topic.asp?TOPIC_ID=55505

This is the approach I am going to use.

D3mon
Senior Member

United Kingdom
1685 Posts

Posted - 02 January 2005 : 21:35:10

My advice would be to run a query on that column in your DB, converting "monday" to 1, "tuesday" to 2 etc. Depends how many records you've got I guess.

"UPDATE [tablename] SET new_semester_day = '1' WHERE new_semester_day = 'monday'"
"UPDATE [tablename] SET new_semester_day = '2' WHERE new_semester_day = 'tuesday'"...

Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won"

dayve
Forum Moderator

USA
5820 Posts

Posted - 02 January 2005 : 21:41:08

quote:
Originally posted by D3mon

My advice would be to run a query on that column in your DB, converting "monday" to 1, "tuesday" to 2 etc. Depends how many records you've got I guess.

"UPDATE [tablename] SET new_semester_day = '1' WHERE new_semester_day = 'monday'"
"UPDATE [tablename] SET new_semester_day = '2' WHERE new_semester_day = 'tuesday'"...

I was suggesting that early in this thread, but then the code would need to be changed for future insertions into this database, but I would definitely consider changing it from the way it is being done now.

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 03 January 2005 : 05:38:42

Another solution would be to add a small table for Semester_Days, with two fields: the day name and an sort_order column. You could then just join this table to your current query (joining by day name) and sort by the sort_order column. It would have the advantage of not changing your existing code.

Snitz 3.4 Readme | Like the support? Support Snitz too

dayve
Forum Moderator

USA
5820 Posts

Posted - 03 January 2005 : 08:12:02

I actually started thinking last night that a lookup table would do the trick. why can't I think faster?

D3mon
Senior Member

United Kingdom
1685 Posts

Posted - 03 January 2005 : 08:18:11

quote:
Originally posted by dayve

quote:
Originally posted by D3mon

My advice would be to run a query on that column in your DB, converting "monday" to 1, "tuesday" to 2 etc. Depends how many records you've got I guess.

"UPDATE [tablename] SET new_semester_day = '1' WHERE new_semester_day = 'monday'"
"UPDATE [tablename] SET new_semester_day = '2' WHERE new_semester_day = 'tuesday'"...

I was suggesting that early in this thread, but then the code would need to be changed for future insertions into this database, but I would definitely consider changing it from the way it is being done now.

Agreed, badly stored data will cause problems later on for sure.

Snitz 'Speedball' : Site Integration Mod : Friendly Registration Mod
"In war, the victorious strategist only seeks battle after the victory has been won"

Classicmotorcycling
Development Team Leader

Australia
2084 Posts

Posted - 03 January 2005 : 15:38:03

Worked on something just then that is giving the desired results. Thanks for your help guys.
Used numbers to represent days:

1 = Monday
2 = Tuesday
3 = Wednesday and so on.....

I then used the following SQL statment:

strSQL = "SELECT PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Group, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Day, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Time_B, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Time_E, PIVOT_NEW_SEMESTER_DETAILS.New_Semester_Cost FROM PIVOT_NEW_SEMESTER_DETAILS ORDER BY New_Semester_Day, New_Semester_Time_B ASC;"

I then used an if statment in the display page as follows:

if rsNewSemester("New_Semester_Day") = "1" then
	Response.Write ("			<font class=""text3f"">Monday</font>")
elseif rsNewSemester("New_Semester_Day") = "2" then
	Response.Write ("			<font class=""text3f"">Tuesday</font>")
elseif rsNewSemester("New_Semester_Day") = "3" then
	Response.Write ("			<font class=""text3f"">Wednesday</font>")
elseif rsNewSemester("New_Semester_Day") = "4" then
	Response.Write ("			<font class=""text3f"">Thursday</font>")
elseif rsNewSemester("New_Semester_Day") = "5" then
	Response.Write ("			<font class=""text3f"">Friday</font>")
elseif rsNewSemester("New_Semester_Day") = "6" then
	Response.Write ("			<font class=""text3f"">Saturday</font>")
elseif rsNewSemester("New_Semester_Day") = "7" then
	Response.Write ("			<font class=""text3f"">Sunday</font>")
end if

And that seems to of done the trick. As the site will have multiples of the same day, and some days will not be in the DB at times then I needed to get it sorted by day, then starting time. This way seems to of done what I needed.

Thanks once again for pointing me in the right direction.

Cheers,

David Greening

Page: of 2

Topic

New Topic

Topic Locked

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.3 seconds.