Filtering out the "category name" from a database

[karen987]

I have a webpage called "topicview.asp" on a news website with ASP pages, it's a simple news publishing software. You add the news from the Admin section and all the details are stored in a database.

This page shows all the categories filtered out, (10 to a page) but it doesn't show the NAME of the category anywhere on the page. How can i add the name of the category? In the database the title of the secction that stores the

nm_tbl_cate and the field name is "fldNAME"

Let me give an example,

The news category, "Business and finance" is CID 13 and the browser window shows

http://www.myweb/TopicView.asp?CID=13

However nowhere in the page does it say, "Business and finance"

Because i have many categories, it will be tedious for me to physically write out a page for each. Isn't there anything i can add to the code of this page, so that i automatically filters out the Name of the category too? Here is how the name is stored in the database:

nm_tbl_cate and the field name is "fldNAME"

and here is the code of my page,

<!--#include file="inc_header.asp"-->
 
    <TABLE WIDTH=750 BORDER=0 CELLPADDING=4 CELLSPACING=0 align="center">
  <TR> 
 
    <TR> 
          <TD width="600" valign="top" class="leftcol"> 
 
        Channels.
 
    <%Dim iStart, iOffset, SQL, RS, EOF_VAL, strRETURNED_DATA, iRows, iCols, iStop, iRowLoop, strPREV_LINK, strNEXT_LINK, CID, CNAME, AUTHOR, NID, SUMMARY, IMAGE, CATEGORIES, TITLE, POSTED, XI
 
    CID     = Trim(Request.QueryString("CID"))
    iStart  = Request.QueryString("Start")
    iOffset = Request.QueryString("Offset")
 
    IF IS_VALID_ID(CID) THEN
 
        If Not IsNumeric(iStart) or Len(iStart) = 0 then
          iStart = 0
        Else
          iStart = CInt(iStart)
        End If
        If Not IsNumeric(iOffset) or Len(iOffset) = 0 then
          iOffset = ALL_ARTICLES_PAGE_SIZE
        Else
          iOffset = Cint(iOffset)
        End If    
 
        IF DB_TO_USE = 1 OR DB_TO_USE = 3 THEN    
            SQL = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldSUMMARY AS SUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (Now() BETWEEN fldPOSTED AND fldEXPIRES) AND (nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & ")) ORDER BY fldPOSTED DESC"
        ELSE
            SQL = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldSUMMARY AS SUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED FROM nm_tbl_news, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (GetDate() BETWEEN fldPOSTED AND fldEXPIRES) AND (nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & ")) ORDER BY fldPOSTED DESC"
        END IF
 
        Call OPEN_DB()
 
        Set RS = MyConn.Execute(SQL)
        IF NOT RS.EOF THEN
            strRETURNED_DATA = RS.getrows
            EOF_VAL = False
        ELSE
            EOF_VAL = True    
        END IF    
        RS.close
        Set RS = Nothing
        ' Get settings
        Dim DATE_F
        DATE_F = GET_SETTINGS(False, "fldDATE_F")
 
 
        IF not EOF_VAL = True THEN
            iRows = UBound(strRETURNED_DATA, 2)
            iCols = UBound(strRETURNED_DATA, 1)
            If iRows > (iOffset + iStart) Then
               iStop = iOffset + iStart - 1
            Else
               iStop = iRows
            End If            
            FOR iRowLoop = iStart to iStop
                NID = strRETURNED_DATA(0, iRowLoop)
                SUMMARY = strRETURNED_DATA(1, iRowLoop)
                IMAGE = strRETURNED_DATA(2, iRowLoop)
                TITLE = strRETURNED_DATA(3, iRowLoop)
                AUTHOR = strRETURNED_DATA(4, iRowLoop)
                POSTED = strRETURNED_DATA(5, iRowLoop)
                TITLE = PROCESS_SHORTCUTS(False, TITLE)
                SUMMARY = PROCESS_SHORTCUTS(False, SUMMARY)    
                CATEGORIES = GET_CATES(NID)        
                            %>
 
 
                 <table cellpadding="2" cellspacing="0" border="0" width="100%"><tr>    
                 <td><a href="view.asp?ID=<%=NID%>" class="aTITLE"><%= TITLE %></a></td>
                </tr>
                <tr><td colspan="2" class="divPOSTEDON"><div align="justify"><%= FormatDateTime(POSTED,DATE_F) %>
                  <% IF SHOW_AUTHOR = True THEN %>
&nbsp;&nbsp; By:<%= AUTHOR %>
<% Else %>
&nbsp;
<% End If %>
                </div></td></tr>
 
                <tr><td colspan="2" class="tdSUMMARY"><div align="justify">
                    <%IF NOT (IMAGE = "" OR IsNull(IMAGE)) THEN%>
                      <img src="<%=IMAGE%>" width="50" height="50" border="1" align="left" />
                  <% END IF %>
                  <%= SUMMARY %></div></td></tr>
                </table>
                <br /><br />
 
                <%
            NEXT
             ' Close DB        
            MyConn.close            
            Set MyConn = Nothing        
 
            If iStart > 0 Then
             strPREV_LINK = "<A class=""clsPAGING"" HREF=""TopicView.asp?Start=" & iStart-iOffset & "&Offset=" & iOffset & "&CID=" & CID & """>Previous " & iOffset & "</A>"
            Else
                strPREV_LINK = "&nbsp;"
            End If    
            If iStop < iRows Then
             strNEXT_LINK = " <A class=""clsPAGING"" HREF=""TopicView.asp?Start=" & iStart+iOffset & "&Offset=" & iOffset & "&CID=" & CID & """>Next " & iOffset & "</A>"
            Else        
             strNEXT_LINK = "&nbsp;"    
            End If %>                    
 
            <table width="100%" align="center" cellpadding="2" cellspacing="0" border="0"><tr class="trPAGING">
             <td width="50%" align="left"><%=strPREV_LINK%></td>
             <td width="50%" align="right"><%=strNEXT_LINK%></td>
            </tr></table>    
 
        <%ELSE%>    
 
                <table width="100%" align="center" cellpadding="2" cellspacing="0" border="0"><tr><td>No articles have been found.</td></tr></table>
 
        <%END IF%>        
    <%END IF%>
 
<!--#include file="inc_footer.asp"-->

[karen987]

Also, i wuoldn't mind if the title of the category showed in the browser too. At the moment, in the browser, it shows the ID, but not the name of the category.

I don't think the one you mentioned is the one, i think this needs to be added to the sql statement, because the names of the categories are stored in this column called "fldNAME"



nm_tbl_cate and the field name is "fldNAME"

What i need to do, is to somehow filter each "fldNAME" as well as the ID (which already shows in the browser)

I'd like to show the fldNAME as Category name on the webpage. If it can be shown in the browswer too, good, if not i'd like it on the webpage.

[jhardman]

Karen,

In the database query there is a "Select" clause. This tells the asp page which fields in the db to look up. You need to add the field you want to this list using this syntax:
SELECT tableName.fieldName, tableName2.fieldName2, etc

then after the db connection is opened, you can refer to this db field as:
RS("tableName.fieldName")

So for example, if you have a field named "itemNames" in a table named "breakfastFoods" and you wanted this to be the title of the web page you could say:

response.write "<title>"
response.write RS("breakfastFoods.fieldName")
response.write "</title>" & vbNewLine

this would print out:[html]<title>Toast</title>[/html]Does this make sense?

Jared

[karen987]

Karen,

In the database query there is a "Select" clause. This tells the asp page which fields in the db to look up. You need to add the field you want to this list using this syntax:
SELECT tableName.fieldName, tableName2.fieldName2, etc

then after the db connection is opened, you can refer to this db field as:
RS("tableName.fieldName")

So for example, if you have a field named "itemNames" in a table named "breakfastFoods" and you wanted this to be the title of the web page you could say:

response.write "<title>"
response.write RS("breakfastFoods.fieldName")
response.write "</title>" & vbNewLine

this would print out:[html]<title>Toast</title>[/html]Does this make sense?

Jared

Hi jared,

So that means,

i need to add

nm_tbl_cate.fldName

do i need to repeat this for example i have added it to the sql statement here,

   IF DB_TO_USE = 1 OR DB_TO_USE = 3 THEN    
            SQL = "SELECT nm_tbl_news.ID AS NID, nm_tbl_news.fldSUMMARY AS SUMMARY, nm_tbl_news.fldIMAGE AS NIMAGE, nm_tbl_news.fldTITLE AS TITLE, nm_tbl_agent.fldNAME AS ANAME, nm_tbl_news.fldPOSTED AS POSTED FROM nm_tbl_news, nm_tbl_cate.fldName as CATEGORY, nm_tbl_agent WHERE (nm_tbl_agent.ID = nm_tbl_news.fldAID) AND (nm_tbl_news.fldACTIVE=1) AND (Now() BETWEEN fldPOSTED AND fldEXPIRES) AND (nm_tbl_news.ID IN (SELECT fldNEWS_ID FROM nm_tbl_news_cate WHERE fldCATE_ID = " & CID & ")) ORDER BY fldPOSTED DESC"

and then i have to add
RS("nm_tbl_cate.fldName") , do you mean in the call open DB part below? and where exactly should i do this?

Call OPEN_DB()
 
        Set RS = MyConn.Execute(SQL)
        IF NOT RS.EOF THEN
            strRETURNED_DATA = RS.getrows
            EOF_VAL = False
        ELSE
            EOF_VAL = True    
        END IF    
        RS.close
        Set RS = Nothing
        ' Get settings
        Dim DATE_F
        DATE_F = GET_SETTINGS(False, "fldDATE_F")
 
 
        IF not EOF_VAL = True THEN
            iRows = UBound(strRETURNED_DATA, 2)
            iCols = UBound(strRETURNED_DATA, 1)
            If iRows > (iOffset + iStart) Then
               iStop = iOffset + iStart - 1
            Else
               iStop = iRows
            End If            
            FOR iRowLoop = iStart to iStop
                NID = strRETURNED_DATA(0, iRowLoop)
                SUMMARY = strRETURNED_DATA(1, iRowLoop)
                IMAGE = strRETURNED_DATA(2, iRowLoop)
                TITLE = strRETURNED_DATA(3, iRowLoop)
                AUTHOR = strRETURNED_DATA(4, iRowLoop)
                POSTED = strRETURNED_DATA(5, iRowLoop)
                TITLE = PROCESS_SHORTCUTS(False, TITLE)
                SUMMARY = PROCESS_SHORTCUTS(False, SUMMARY)    
                CATEGORIES = GET_CATES(NID)        
                            %>

and then to use it in the table in the page i would use

response.write "<title>"
response.write RS("nm_tbl_cate.fldName")
response.write "</title>" & vbNewLine[/code]

is this correct? someone gave me a join statement at this website, I'll post the link so you may understand what their solution was that didnt work.
http://www.webdeveloper.com/forum/sh...386#post771386
but it doesn't work and i'm stuck. It seems like such a simple thing,

Thanks for your help.

[karen987]

no responsees to this? i'm disappointed!!:(

[jhardman]

Hi jared,

So that means I need to add

nm_tbl_cate.fldName

do i need to repeat this for example i have added it to the sql statement here,

you need to add it to what ever sql statement you use to open the db when you want to use it. Your project is becoming so complicated that it will be very difficult for anyone but you to figure out what is going on. If you have relied too much on other people's help, then it will be that much harder for you to straighten it out. I have tried to avoid giving you the exact code that will solve the problem partly because I think it would be a good idea if you were the ultimate authority on how your site is programmed. re-read my earlier post, I was pretty clear.

and then i have to add
RS("nm_tbl_cate.fldName") , do you mean in the call open DB part below? and where exactly should i do this?

and then to use it in the table in the page i would use

response.write "<title>"
response.write RS("nm_tbl_cate.fldName")
response.write "</title>" & vbNewLine[/code]

You only have to do the last part. When you add the field name to the select clause, that means you can access that field every time you want by typing

RS("nm_tbl_cate.fldName")

I then showed the exact way to use that field in the title bar in order to illustrate its use. I'm sorry if that confused you, it was your original question.

is this correct? someone gave me a join statement at this website, I'll post the link so you may understand what their solution was that didnt work.
http://www.webdeveloper.com/forum/sh...386#post771386
but it doesn't work and i'm stuck. It seems like such a simple thing,

Thanks for your help.

I don't like using JOIN, I am not an SQL expert, so I try to keep my SQL as simple and as easy to troubleshoot as I can.

Jared

[karen987]

thanks Jared,

it's kinda working now, i'm just tidying it up a bit, thank you