Hyperlink with drop down links from a mysql database

[mramsay]

HI,

I'm having alot of problems trying to figure out how to create drop down links from a hyperlink on my homepage.

I have a hyperlink called Programs (this is for a community centre)

When the Programs hyperlink is selected I would like a drop down of link from my programs mysql table to be displayed. So far, it takes my mysql data and puts it as link but on it's own. I'm not sure how to put it under my Programs hyperlinks. Also description is just the field name in my table. I do not want it to appear under my Program Hyperlink. I'm scared to mess around too much because as of now the hyperlinks do show up on their own.

Help would be greatly appreciated.

Here is my current code below.

<%   Dim conn 
'Create an ADODB connection
 Set conn = Server.CreateObject("ADODB.Connection")
 
'Open the database
 conn.Open "Driver={MySQL ODBC 3.51 Driver};Server=24.79.93.78;Database=communitycentre;UID=mona.ramsay;PWD=puke_9;"
 
'Execute the SQL and assign to our Recordset
set rs = Server.createobject("ADODB.recordset")
sql = "SELECT description FROM programtbl"
 
 Dim selected, query
' get the item that was passed in the querystring.
selected = Request.QueryString("LinkChosen")
 
<a href="http://localhost/ValourCommunityCentre/ValourCC.asp?LinkChosen=Programs"> Programs</a>
 
rs.Open sql, conn%>
 
<% Do While NOT RS.EOF 
    Response.Write("<a href='http://" & rs.Fields.Item("description").Value & "'>" & rs.Fields.Item("description").Value & "</a>")%>
 
<% 
RS.Movenext
loop%>
 
<table>
 
    <tr>
    <% for each x in rs.fields
    response.write("<th>" & x.name & " </th>")    
   next%>
  </table>
 
  <%  do until rs.EOF%>
 
    <%for each x in rs.fields
    style="width: 2px"> Response.write(x.value)%>
    <%next
 
    rs.movenext%>
 
   <%loop
 
 rs.close
 conn.close
%>

[jhardman]

It looks like there are some errors in your code. I guess I may have messed something up when I was editing it, but I thought I just removed a little white space. One problem is that you are scrolling through all of the files to write the description, this leaves you at the last record (or past it) when you want to list things again. The first do...loop is completely unnecessary if you don't want to show the descriptions.

Anyway, showing the program names in a drop-down select is pretty easy, but it depends on what it is named in the database. For example, if the db has a field called "programNam e" then you can write it like this:

 <select>
<%
rs.movefirst
'this line might cause an error depending on your cursor type
'it just moves to the first record in the recordset and isn't necessary if you
'haven't moved through them yet
 
do until rs.eof
   response.write "<option value=" & chr(34) & rs("programName") & chr(34)
   response.write ">" & rs("programName") & "</option>" & vbNewLine
   rs.movenext
loop %>

Notice, this just shows a list in a dropdown select, I didn't put in any functionality. Where do you want to go from here? Did this help?

Jared

[mramsay]

Hi there,

I am really confused. I am a beginner with ASP and do not follow you.

I've changed my code, but when I run it, I can see the values from the description field and the field name description at the bottom of my webpage.

I can't seem to get the values to be hyperlinks when I select the Program hyperlink.

Here's my code now, I know I am missing something somewhere.

<%   Dim conn 
'Create an ADODB connection
 Set conn = Server.CreateObject("ADODB.Connection")
 
'Open the database
 conn.Open "Driver={MySQL ODBC 3.51 Driver};Server=24.79.93.78;Database=communitycentre;UID=myUID;PWD=myPWD;"
 
'Execute the SQL and assign to our Recordset
set rs = Server.createobject("ADODB.recordset")
sql = "SELECT description FROM programtbl"
 
 Dim selected, query
' get the item that was passed in the querystring.
selected = Request.QueryString("LinkChosen")
 
 
  '<a href="http://localhost/ValourCommunityCentre/ValourCC.asp?LinkChosen=Programs"> Programs</a>
 
rs.Open sql, conn%>
 
<table>
 
    <tr>
    <% for each x in rs.fields
    response.write("<th>" & x.name & " </th>")   
 
   next%>
  </table>
 
 
  <%  do until rs.EOF%>
 
    <%for each x in rs.fields
    style="width: 2px"> Response.write(x.value)%>  
 
    <%next
 
 
    rs.movenext%>
 
 
   <%loop%>
 
 
 
<select>
 
<%    
 
 
 
'this line might cause an error depending on your cursor type
'it just moves to the first record in the recordset and isn't necessary if you
'haven't moved through them yet
 
do until rs.eof
   response.write "<option value=" & chr(34) & rs("description") & chr(34)
   response.write ">" & rs("description") & "</option>" & vbNewLine
   rs.movenext
loop %>
 
<%rs.close
 conn.close %>
 
</select>

[jhardman]

Hi there,

I am really confused. I am a beginner with ASP and do not follow you.

I've changed my code, but when I run it, I can see the values from the description field and the field name description at the bottom of my webpage.

I can't seem to get the values to be hyperlinks when I select the Program hyperlink.

Here's my code now, I know I am missing something somewhere.

I think it's clear that you are missing something, there are lines in your code that don't make any sense. Partly because of that I don't know what you are trying to turn into a hyperlink. I don't even know what you are talking about when you say "when I select the Program hyperlink", What does that mean?

I'm not trying to be a jerk, I just don't understand you and it seems apparent from your code that you don't know how to get what you want. Don't get too stressed out about this. Let's go back over your code and I'll try to explain exactly what it is doing.

<%   Dim conn 
'the asp script will connect to a database with an ADODB connection
 Set conn = Server.CreateObject("ADODB.Connection")
 
'gives the script the details of how to connect, and tells it to connect to the db
 conn.Open "Driver={MySQL ODBC 3.51 Driver};Server=24.79.93.78;Database=communitycentre;UID=myUID;PWD=myPWD;"
 
'a recordset is just that, a set of records, and you are going to put the info from 
'the db into a recordset to make it easier to use.  "SQL" is a standard way to 'ask databases for information, it can be used by most types of databases
set rs = Server.createobject("ADODB.recordset")
sql = "SELECT description FROM programtbl"
'the only thing you are looking up in the db is one field called "description" in
'one table called "programtbl".  Everything else in the database is being ignored
'If you want to look up more data, I would change
'this to "SELECT * FROM programtbl" this puts all of the data into your recordset
 
Dim selected, query
' get the item that was passed in the querystring.
'reading through your code, although you act like someone would send data to
'this page, you are not using this data in any way.
selected = Request.QueryString("LinkChosen")
 
  '<a href="http://localhost/ValourCommunityCentre/ValourCC.asp?LinkChosen=Programs"> Programs</a>
'this line is a link, but since it begins with an apostrophe it will be ignored.  
'This is how we write comments for others who read our code.
 
'this line is where you actually ask the db for information which you put
'in your recordset, it specifies which db connection you are using, and the sql 
'query you wrote above for what parts of the db you want
rs.Open sql, conn%>
 
<table>
 
    <tr>
    <% for each x in rs.fields
   'this for each ... next loop looks through every field in your recordset
   'and prints the name of the field into the header of the table. This is 
   'kind of a standard way to list everything in your recordset
    response.write("<th>" & x.name & " </th>")   
   'since your recordset only has one field, it is only printing the following:
   '"<th>description</th>".  The great thing is that if you changed the SQL 
   'query to "SELECT * FROM ..." then this would have more fields, or if it said 
   '"SELECT programName FROM ..." then it would show programName.
 
   next %>
  </table>
   <!-- The above line ends the table.  it doesn't make sense to me to end the 
   table here.  Surely the other fields need to be in the same table, right?  
   Anyway, this is how we write comments in HTML -->
 
  <%
  do until rs.EOF
   'this says you expect to go through every record in your recordset
 
   'there appears to be a line missing here which says:
   'response.write "<tr>", this would start a new line in the table %>
 
    <%for each x in rs.fields
   'this for each ... next loop should list the contents of every field in one
   'record, but the next line appears to be garbled.  I believe it should say 
   'something like this:
   'response.write "<td>" & x.value & "</td>" & vbNewLine
    style="width: 2px"> Response.write(x.value)
 
   next
 
   'there appears to be a line missing here which says:
   'response.write "</tr>" 
   'this would finish off the row of your table in preparation to move to the next
   'record
 
    rs.movenext
   'this moves to the next record in your recordset %>
 
    <%
   loop
   'this sends you back to the point marked "do" %>
   <!-- I would end the table here when you finish going through the loop rather 
   than the place mentioned above -->
 
<!-- this next line starts a dropdown select.  I wrote this code because I thought
you wanted one.  Unfortunately, I didn't understand why you wanted it, so
it probably doesn't show you what you want and I didn't make this select do anything, it just sits there -->
<select>
<%    
rs.movefirst
'this line moves you back to the first record, otherwise you would be at the end
'and that wouldn't do you any good.  I notice you took it out but left in the 
'comment I made.  That is puzzling.  did it cause an error?
 
do until rs.eof
   'notice I am just going to scroll through the records and put the description
   'from the db into the options of the select.  This probably isn't very useful
   'but it does demonstrate how info from the db can be incorporated into
   'the web page.  I'm going to write it a little different this time just because
   'it might make more sense, but it actually does the exact same thing
   %>
   <option value="<%=rs("description")%>"><%=rs("description")%></option>
   <%
   rs.movenext 'this just moves to the next record
loop ' this just goes back to the beginning of the do ... loop %>
 
<%rs.close
 conn.close
'these lines close the recordset and the db connection.  They are actually not 
'necessary because ASP scripts automatically close all open connections at 'the end of the script, but some programmers include these lines to be safe %>
 
</select>
<!-- this line ends the drop down select -->

I hope my explanation helps you understand a little of what is going on. I feel like I know pretty well what your script is doing, but it isn't what you want and I don't understand what you want. You said you want hyperlinks, but what part do you want to make into hyperlinks and where should they take you? I give you full marks for trying this out on your own, a lot of us started out like that before we fully understood what we were doing. However, if you don't do better at explaining what you need, I will not be able to help you any more.

Jared

[mramsay]

Hi there,

I've read over your comments a few times. I guess I'm not being clear enough in explaining what I want my code to do.

Well here goes.

I am working on a Community Centre Website.

I am using visual studio 2005 coding with asp and html. My home page consists of the name of the Community Centre and I have hyperlinks on the right had side, very common in websites.

One of my hyperlinks on the webpage is Programs. What I am trying to do is when a user or myself selects the Programs hyperlink, he/she will see a list of other hyperlinks related to Programs such as Hockey, Baseball, etc. for an example. Then the user can select Hockey or baseball and it will bring them to a page of information regarding the Hockey program.

I do not want a drop down list.

Example:

Progams - hyperlink when selected
Baseball - hyperlinks will be displayed on the home page right under
Hockey Programs.

My program table consists of more information than description, but, I only want the records under description to be displayed when Programs is clicked. Description is the column heading. Baseball and Hockey are my fields under description.

I hope this is easier to understand.

[jhardman]

Hi there,

I've read over your comments a few times. I guess I'm not being clear enough in explaining what I want my code to do.

Well here goes.

I am working on a Community Centre Website.

I am using visual studio 2005 coding with asp and html. My home page consists of the name of the Community Centre and I have hyperlinks on the right had side, very common in websites.

One of my hyperlinks on the webpage is Programs. What I am trying to do is when a user or myself selects the Programs hyperlink, he/she will see a list of other hyperlinks related to Programs such as Hockey, Baseball, etc. for an example. Then the user can select Hockey or baseball and it will bring them to a page of information regarding the Hockey program.

I do not want a drop down list.

Example:

Progams - hyperlink when selected
Baseball - hyperlinks will be displayed on the home page right under
Hockey Programs.

My program table consists of more information than description, but, I only want the records under description to be displayed when Programs is clicked. Description is the column heading. Baseball and Hockey are my fields under description.

I hope this is easier to understand.

OK, I think this makes a little more sense. One of the things that confused me was your use of the word "select" which in HTML refers to a drop-down where a user can select an option from a list.

There are a couple ways to achieve similar effects. It might make most sense to put these all on one page like this:

<html><head><title></title></head><body>
 
<div style="position: absolute; left: 0px; top: 0px; height: 100%; width: 150px;">
<ul>
<li>Calendar</li>
<li><a href="thisPage.asp?display=programs">Programs</a></li>
<li>Contact Info</li>
 
</ul>
</div>
<div style="position: absolute; left: 150px; top:0px; height: 100%;">
<%
dim query, conn, rs
 
if request.querystring("display") = "programs" then
    Set conn = Server.CreateObject("ADODB.Connection")
    'gives the script the details of how to connect, and tells it to connect to the db
     conn.Open "Driver={MySQL ODBC 3.51 Driver};Server=24.79.93.78;Database=communitycentr  e;UID=myUID;PWD=myPWD;"
 
    'a recordset is just that, a set of records, and you are going to put the info from
    'the db into a recordset to make it easier to use.  "SQL" is a standard way to 'ask databases for information, it can be used by most types of databases
    set rs = Server.createobject("ADODB.recordset")
    query = "SELECT description FROM programtbl"
 
    rs.open query, conn
 
    response.write "Programs:<br>"& vbNewLine & "<ul>"
 
    do until rs.eof %>
        <li><a href="detailPage.asp?program=<%=rs("description")%>"><%=rs("description")%></a>
        </li>
        <%
        rs.moveNext
    loop
%>
</div>
 
</body></html>

Try this and see if it comes close to what you want.

Jared