bring together MySQL hits in ASP script

crmpicco · June 1st, 2006, 05:35 AM

Code:

<%
set rsLadd = conn.execute("SELECT ladder_name,game_name,area FROM `ladders` WHERE power = '0'", ,adCmdText)
set rsPwrLadd = conn.execute("SELECT ladder_name,game_name,area FROM `ladders` WHERE power = '1'", ,adCmdText)
set rsTour = conn.execute("SELECT tournament_name,game_name,area FROM `tournaments`", ,adCmdText)
set rsLeague = conn.execute("SELECT league_name,game_name,area FROM `leagues`", ,adCmdText)
%>

        <tr>
            <td class="font_Sec_Headings" width="10%">
            <select name="pwr_ladd" id="pwr_ladd">
                <option>Please Select</option>
                <% if (NOT rsPwrLadd.eof) AND (NOT rsPwrLadd.bof) AND (Response.IsClientConnected) then %>
                    <% do until rsPwrLadd.eof %>                
                        <option><%=Trim(rsPwrLadd("game_name"))%>&nbsp;-&nbsp;<%=Trim(rsPwrLadd("area"))%>&nbsp;-&nbsp;<%=Trim(rsPwrLadd("ladder_name"))%></option>
                    <% rsPwrLadd.movenext
                       loop %>
                <%
                end if 
                rsPwrLadd.close
                Set rsPwrLadd = Nothing
                %>
            </select>
            </td>
        </tr>

I have this code all on one ASP script, what i was wondering was is there a quicker way to get it out the database and refer to it in the script?

I just felt that four SQL hits may be time consuming in real-time environment!

Picco

www.crmpicco.co.uk
www.ie7.com

woodyz · June 1st, 2006, 04:10 PM

I am not sure what you are asking... but you have created four recordsets but you are only using one of them: rsPwrLadd.
A little tip: you probably don't need the following line (unless you are really concerned with the IsClientConnected... which I suggest you shouldn't be).

Code:

<% if (NOT rsPwrLadd.eof) AND (NOT rsPwrLadd.bof) AND (Response.IsClientConnected) then %>

This line isn't needed because the do until rsPwrLadd.eof is sufficient - it will only loop if there is anything to loop through.

Perhaps you are asking if there is some way to cache the data that the recordsets are retrieving from the database? If this is the case, there are a number of ways to do this. One way to do this would be to retrieve the data for each select box once, use the data to create the HTML for each select box, and store each one in its own application variable. Then, whenever you need it just retrieve the HTML and insert it into your page as you render it.

Make sense?

Woody Z http://www.learntoprogramnow.com