[thelist] SQL problem

Steve Cook steve.cook at evitbe.com
Fri Aug 16 09:01:02 CDT 2002


Hi Kev!

I can think of at least a couple of ways of doing this, though here's a
straightforward method you can use for lots of similar structures.
Preferably you want to avoid the "Recordset in a loop" method - if you had
100 categories you would have to do 100 separate calls to the database -
processor intensive -especially against Access.

Instead, you should match up the info in the database, taking full advantage
of the relational nature of your database.

SQL = "SELECT PKey, Catagory, PrimaryID, Description from Descs JOIN
FaveList ON PKey = pkmatch ORDER BY PKey, PrimaryID"

I open my DB slightly differenlty to you, but you can use any method that
returns a recordSet.

set dbConnection = Server.CreateObject("ADODB.Connection")
dbConnection.Open "DSN=DSNName;uid=login;pwd=password"
set RS = Server.CreateObject("ADODB.Recordset")

RS.Open SQL, dbConnection

This will return a recordset with info formatted like this:

PKey ¦ Catagory                    Primary_ID      ¦ Description
----------------------------------------------------------------------
1       ¦ Music                    1               ¦ Cardiacs Homepage
1       ¦ Music                    2               ¦ Offspring Homepage
1       ¦ Music                    3               ¦
http://antennaradio.com/
2       ¦ Humour                   3               ¦ Popbitch
3       ¦ Computer stuff           4               ¦ Evolt


You then can step through the recordset in your page doing something like
this:

oldKey = 0

do while not RS.EOF
	if RS("PKey") <> oldKey then ' Check whether it's a new category
		Response.Write RS("Catagory")
		Response.Write "<select size=""1"" name=""dropdown_menu"">"
& vbCrlf
	end if

	Response.Write "<option>"
	Response.Write RS("Description")
	Response.Write "</option>" & vbCrlf


	RS.MoveNext
	if RS("PKey") <> oldKey then ' Check whether it's a new category
		Response.Write "</select><br>" & vbCrlf
	end if
loop


(This code isn't tested). Basically you are looping through the recordset.
When you get a new category you close any previous SELECT, print out the
category name and start a new SELECT. This way you make 1 db query and
process it in 1 loop. Nifty huh?

BTW - I've suggested a fun music link for you in the database info above.
Check out their cool and very eclectic stations - should be right up your
street mate ;-)


.steve


-------------------------------------
 Cookstour - http://www.cookstour.org
-------------------------------------

> -----Original Message-----
> From: Kevin Stevens [mailto:kjs at ratking.co.uk]
> Sent: den 16 augusti 2002 15:23
> To: thelist at lists.evolt.org
> Subject: Re: [thelist] SQL problem
>
>
<SNIP>
> OK, sorry, I think I've been getting my jargon wrong. I have
> 2 tables, 1
> called Descs which contains just two columns...
> <SNIP>
>
> Hope I've explained myself properly this time :)
>



More information about the thelist mailing list