select from 66 tables using checkboxes

gilgalbiblewheel · August 3rd, 2004, 06:04 PM

Ok. I have a 66 table database. I want the viewer to choose his table to search in. I have an example of the design but need help in the ASP response page:

http://wheelofgod.tripod.com/ASPSearchamos2.htm

the first 6 options are for table columns. Then there is 2 radion buttons which enable/disable the 66 tables.

I was wondering if
quote:
--------------------------------------------------------------------------------
select * from
--------------------------------------------------------------------------------

can come in an "if" statement? If so how would the code be written?

rajanikrishna · August 3rd, 2004, 08:31 PM

Hello,
I understand that u want to retrieve the fields which the user checked.

if you give same name to all checkboxes it will create an array.
And i suggest that you give "value" for each checkbox as columnname (Ex:book)

Then you can read it as

'*************At the submitted page
dim strFields
strFields=request("checkboxname")

strQry="select " & strFields & " from tablename"

i think this is what you need.

surendran · August 4th, 2004, 12:41 AM

hi gilgalbiblewheel
try this

strFields=request("checkboxname")

strQry="select * from " & strFields & ""

surendran
(Anything is Possible)

happygv · August 4th, 2004, 08:12 AM

Hi gilgalbiblewheel,

If I am not wrong, you are trying to search for the KEYWORD (entered in the textbox) against all CHECKED columns among those 6 checkboxes, from all the tables CHECKED among those 66 table Checkboxes.

This is actually taking all the columns that are checked and compare for search keyword in it for every table.

Assume you have CHECKED the BOOK, SPOKE and TEXT from the COLUMNS and CHECKED the tables as Genesis, Isaiah, Romans... then you should contruct an sql statement that would check it this way.

Select <DISPLAY_COLUMNs>
from Genesis
where BOOK like '%' + strSearchKeyWord + '%' OR
SPOKE like '%' + strSearchKeyWord + '%' OR
TEXT like '%' + strSearchKeyWord + '%'

Now replace GENESIS with Isaiah in the next query and then with ROMANS in the next query and finally you should UNION ALL and that should contruct your recordset. Which is round about way of acheiving.

And Surendran's solution would not fit this. coz' as per his code, the sql statement would look like

Quote:

quote:strFields=request("checkboxname")
strQry="select * from " & strFields & ""

select * from Genesis, Isaiah, Romans
this would not solve the purpose, instead result in huge result set of 9 columns(imaging if all 66 table are checked, it would result in 66 * 3 = 198 columns per row

) because, all the three tables have the same column names(book, spoke, text) in it that woudl result in repetion and you cannot differentiate which has come from which table. And you are not using object referencing there, instead using a *. Also WHERE clause is missing, so what would you be searching for atlast?

Same as the case of RajaniKrisna's solution, only difference is that he has suggested to use that variable in the COLUMNs list, which would not solve the purpose.

I feel that there is a flaw in designing this database structure. When all those 66 tables are having same number of columns all with same names too(I believe so, as per the UI design I could see there), which are similar to all 66 tables, having the data stored in different tables is a real blunder, that makes things complex for you.

You could have designed a single table to hold all that.
BooksMastertable
BOOKID
BookName

SearchStringMatchTable (name it the rightway, that suits the context)
BOOKID
Book
Spoke
Book Title
Chapter
Verse
Text

So there would be 66 different values for BOOK column each having a reference to different BOOKID, similary all other columns would have its own value pertaining to each BOOKID.

So based on the selection made in the asp page, you could easily contruct you SELECT query that would look like...

Code:

strSearchKeyWord = Request.Form("txtSearch")

strColumns would hold "Book, Spoke, Text" separated with comma,
provided that all those 6 checkboxes are given the same name.
strColumns = Request.Form(chkColumns)

Similarly strBooks would hold BOOKID of Genesis, Isaiah, Romans as per selection made, 
where the checkboxes are already set with value as BOOKID. 
Assume that the value of strBooks finally would have "1,2,3"
strBooks = Request.Form(chkBooks)

strSql = "Select <YourColumnName(s)> from SearchStringMatchTable Where "

arrSearchColumns = split(strColumns,",")
For i = 0 to Ubound(arrSearchColumns)
    strSql = strSql & arrSearchColumns(i) & " like '%" & strSearchKeyWord & "%'"
    If i < Ubound(arrSearchColumns) then 
        strSql = strSql & " OR "
    Else
        strSql = strSql & " AND "
    End If
Next

strSql = strSql & "BookId in (" & strBooks & ")"
Response.write strSql

Assuming your Seach Keyword is "King" then the output of strSql string would look like
Select <YourColumnName(s)> from SearchStringMatchTable
Where Book like '%King%' OR Spoke like '%King%' OR Text like '%King%'
AND BookId in (1,2,3)

Does this make sense?
Hope this helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 4th, 2004, 11:41 AM

I changed my code yesterday. Instead of having 66 tables I reunited them into one table. But I don't know how to apply the radio buttons and checkboxes of the 66 selections. Here's the sample:

http://wheelofgod.tripod.com/ASPSearchbible.htm
and
http://wheelofgod.tripod.com/bible.asp

Unfortunately the database is too big for me to upload it. The first is the htm page and the second the answer or response page.

As you see the 8 first checkboxes represent 8 fields in the table and work perfectly.

My problem is the radio buttons and the 66 checkboxes. The radio buttons are for yes (search the entire table) or no (select your preferense). I guess each of the 66 will be divided with a range of ID (genesis: 1-546, exodus: 547-876...). But I need more guidance.

Thanks.

happygv · August 5th, 2004, 08:04 AM

So now you should be having the BooksMaster Table entries in another table. You got to generate the checkboxes for 66 books based on the value in that table. Something like

Code:

strString = "select BookId, BookName from BooksMaster"
...
...
While not rs.EOF
    <input type="Checkbox" name="Books" value='<%=rs("BookId")%>'><%=rs("BookName")
Wend

And for RadioButtons...

The entire King James Bible
<INPUT TYPE="Radio" VALUE='EntirTable' onClick=disable(true); name="Card">
Your selection of books
<INPUT TYPE="Radio" VALUE='Books' onClick=disable(false); name="Card">

If value is BOOKS then enable all 66 checkboxes, If Value is EntireTable then disable all those 66 checkboxes

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 5th, 2004, 10:38 AM

I changed the 66 tables and formed one table (I don't think I'll be able to find free webspace in any website).

As far as organization goes how should my asp/database answer page has to be structured? Because I'm a newbie and looking at it makes me confused.

happygv · August 7th, 2004, 03:15 AM

If the page is not too long to have posted here, can you post your code, along with the database structure of that Table(that you merged together into one) and the BooksMaster Table if you have one. It is hard to suggest how the structure has to be, without looking at what you are missing.

Cheers!

_________________________
- Vijay G
Strive for Perfection

gilgalbiblewheel · August 7th, 2004, 03:19 PM

Here it is:

Code:

<%
Option Explicit

'************************************************************************************

'* Declaration section
'************************************************************************************
' Mode contstants
Const MODE_DEFAULT     = 1
Const MODE_RESULTS     = 2

Const DB_NAME        = "kjv.mdb"    ' Name of our database file
Const SCRIPT_NAME    = "bible2.asp"    ' Name of this script

Const RECORDS_PER_PAGE     = 20            ' Number of records per page

Dim nMode    ' Current Mode

'************************************************************************************
'* End of Declaration section
'************************************************************************************

'************************************************************************************
'* Main section
'************************************************************************************

' Find out what mode we are in
nMode = CLng(Request.QueryString("Mode"))

' Depending on our mode we will do different things
Select Case nMode

    Case MODE_RESULTS
        ' This is where all the results will show        
        ShowResults

    Case Else     ' This one is for MODE_DEFAULT or invalid modes all the same
        ' By default display the search form
        ShowSearchForm    
End Select

'************************************************************************************
'* End of Main section
'************************************************************************************

'************************************************************************************
'* Functions section
'************************************************************************************

' This function will generate our connection string
' it assumes that Access database is in the same folder as this script
Private Function GetConnectionString()
    GetConnectionString =     "Driver={Microsoft Access Driver (*.mdb)};" & _
                "DBQ=" & Server.MapPath(DB_NAME) & ";" & _
                "UID=;PWD=;"
End Function

' Shows HTML page header 
Public Function OutputPageHeader()
    %>
    <HTML>
    <HEAD><TITLE>ADO Recordset Paging Sample</TITLE></HEAD>
    <BODY>
    <H2>ADO Recordset Paging Sample</H2>
    <H3><A HREF="<%=SCRIPT_NAME%>">Back Home</A></H3>
    <%
End Function

' Shows HTML page footer 
Public Function OutputPageFooter()
    %>
    </BODY>
    </HTML>
    <%
End Function

' This function will display the search form
Private Function ShowSearchForm()
    OutputPageHeader
    %>
    <!--
        This form will direct user to itself with MODE_RESULTS mode
    -->
    <FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
    Item Name: <INPUT TYPE="text" NAME="Keyword" VALUE="Item"> <INPUT TYPE="submit" VALUE=" Search ">
    <INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
    </FORM>
    <%
    OutputPageFooter
End Function

' This function will display the results of the search
Private Function ShowResults()
    Dim strConn    ' Database connection string
    Dim SQL     ' String that will have our SQL statments
    Dim RS        ' Recordset object
    Dim Keyword    ' Keyword for search
    Dim nRecCount    ' Number of records found
    Dim nPageCount    ' Number of pages of records we have
    Dim nPage    ' Current page number

    ' Let's see what page are we looking at right now
    nPage = CLng(Request.QueryString("Page"))

    ' Let's see what user wants to search for today :)
    Keyword = Trim(Request.QueryString("Keyword"))

    ' define our SQL statement
    ' we will be looking for all the records in tblItem table 
    ' where ItemName contains our Keyword
    ' do not forget to fix tick marks (single quotes) in our Keyword
    SQL = "SELECT * FROM bible WHERE text_data LIKE '%" & Replace(Keyword, "'", "''") & "%'"

    ' Create our connection string
    strConn = GetConnectionString()

    ' Time to create and open recordset
    Set RS = Server.CreateObject("ADODB.Recordset")
    RS.CursorLocation = 3 ' adUseClient
    RS.Open SQL, strConn ' adOpenKeyset CursorType

    ' Start outputing HTML
    OutputPageHeader

    ' Did we find anything?
    If Not RS.Eof Then
        ' Let's deal with our findings

        ' Get records count
        nRecCount = RS.RecordCount

        ' Tell recordset to split records in the pages of our size
        RS.PageSize = RECORDS_PER_PAGE

        ' How many pages we've got
        nPageCount = RS.PageCount

        ' Make sure that the Page parameter passed to us is within the range
        If nPage < 1 Or nPage > nPageCount Then
            ' Ops - bad page number
            ' let's fix it
            nPage = 1            
        End If

        ' Time to tell user what we've got so far
        Response.Write nRecCount & " records found matching """ & Keyword & """.<br>"
        Response.Write nPageCount & " pages of results.<br>"
        Response.Write "Current page is " & nPage & ".<p>"

        ' Give user some navigation

        ' first page
        ' we link to this page with Page parameter = 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & 1 & _
                """>First Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Previous Page
        ' we link to this page with Page parameter = Current Page - 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage - 1 & _
                """>Prev. Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Next Page
        ' we link to this page with Page parameter Current Page + 1
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPage + 1 & _
                """>Next Page</A>"
        Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;"

        ' Last Page
        ' we link to this page with Page parameter = nPageCount
        Response.Write     "<A HREF=""" & SCRIPT_NAME & _
                "?Keyword=" & Keyword & _
                "&Mode=" & MODE_RESULTS & _
                "&Page=" & nPageCount & _
                """>Last Page</A>"

        ' Start Results
        Response.Write "<p><b>Results:</b><br>" & String(20,"-")

        ' Position recordset to the page we want to see
        RS.AbsolutePage = nPage

        ' Let's output our records                
        ' Loop through records until it's a next page or End of Records
        Do While Not (RS.Eof OR RS.AbsolutePage <> nPage)

            ' All we do here is just show the records
            Response.Write "<br>" & RS("text_data")

            ' Move on to the next record
            RS.MoveNext
        Loop
    Else
        ' We did not find anything
        Response.Write "Nothing found. Try again.<p><A HREF=""" & SCRIPT_NAME & """>Back</A>"
    End If

    ' Be nice - close the recordset
    RS.Close

    ' Finish this page
    OutputPageFooter
End Function
'************************************************************************************
'* End of Functions section
'************************************************************************************
%>

happygv · August 8th, 2004, 01:50 AM

Hi there,

This is how your Function ShowSearchForm() would look.

Code:

Private Function ShowSearchForm()
    OutputPageHeader
    %>
    <!--
        This form will direct user to itself with MODE_RESULTS mode
    -->
    <FORM ACTION="<%=SCRIPT_NAME%>" METHOD="GET">
    Search For: <INPUT TYPE="text" NAME="Keyword" VALUE="Item"> <INPUT TYPE="submit" VALUE=" Search ">
    <INPUT TYPE="hidden" NAME="Mode" VALUE="<%=MODE_RESULTS%>">
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Book">Book<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Book Spoke">Book Spoke<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Book Title">Book Title<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Chapter">Chapter<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Chapter Spoke">Chapter Spoke<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Verse">Verse<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Verse Spoke">Verse Spoke<br>
    <INPUT TYPE="CheckBox" NAME="chkColumnNames" VALUE="Text">Text<br>

    <!-- 
    Code here .... for Radio Button and Javascript function to disable/enable the following checkboxes.
    !-->

    Where do you want to search?<br><br>
<%
    Set Conn = Server.CreateObject("ADODB.Connection") 
    Conn.ConnectionString = GetConnectionString()

    Set RS = Server.CreateObject("ADODB.Recordset")
    Not sure about the ColumnNames and TableName at your end. Use the relavant names marked in red below.
    strSQL = "Select BookId, BookName from BooksMaster Order by BookName"
    RS.Open strSQL, Conn
    Response.write "<Table border='1'><tr>"
    If Not RS.EOF Then
        i = 1
        While Not RS.EOF
            Response.Write "<td>"
            Response.write "<input type='checkbox' name='chkSearchFrom' value='" & RS("BookId") & "'>" & RS("BookName")
            Response.Write "</td>"
            RS.MoveNext
            If (CInt(i) mod 3) = 0 then 
                Response.Write "</tr>"
                If NOT RS.EOF then Response.Write "<tr>"
            End If
            i = CInt(i) + 1
        Wend
    End If
    Response.write "</Table>"
    RS.Close
    Conn.Close
    Set Conn = Nothing
    Set RS = Nothing
%>
    </FORM>

    <%
    OutputPageFooter
End Function

Hope this helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection