Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Jul 2004
    Posts
    494

    Unanswered: database/asp - join tables - what kind is this one?

    I have a set of 66 tables which are the 66 books of the bible. When I perform a "search" I want the search to go through all the 66 tables. What category is this considered?

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by gilgalbiblewhee
    I have a set of 66 tables which are the 66 books of the bible. When I perform a "search" I want the search to go through all the 66 tables. What category is this considered?
    If you can do a stored procedure, that would be best. But why do you have to go through all 66 tables? Shouldn't the user select what types of data they want to search?

    In SQL, you can also use the "UNION" clause between statements to join the result sets together. The only catch is that each statement must have the same result string (same number of columns with the same column names).
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by gilgalbiblewhee
    I have a set of 66 tables which are the 66 books of the bible. When I perform a "search" I want the search to go through all the 66 tables. What category is this considered?
    this is in the category of "sub-optimal design"

    you will need 66 queries, which could be combined with UNION, but nevertheless each table wouyld be queries separately

    by far, a better approach here would be one table instead of 66
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jul 2004
    Posts
    494

    66 tables

    I would rather have 66 tables because I want to leave the option or set of options to the viewer.

    Can the
    select * from
    come in an "if" statement? If so how would the code be written?

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    leave the option to do what? why don't you put them all in one table, and have a column that designates what type of data this is.. then you can simply include that data code, or combination of data codes (based upon user selections), in your query.. I like that idea much better...
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Jul 2004
    Posts
    494

    Ok I already have one db like that

    How would that work?
    I have a db like that and the search page is like this:

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

    How would I be able to use the radio buttons (to search the entire db or to select where to search)?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    that page works very easily with one table

    each book gets its own id, and i would suggest sequential numbers 1 through 66, if in fact that's how many there are (sorry, don't know)

    your radio buttons (The entire King James Bible, or Your selection of books) would be used to tailor the sql

    (nice, by the way, how the "entire" option grays out the checkboxes for the various books)

    if selected individual books are checked, you pass those id numbers to the query, as in

    SELECT ...
    FROM bibletable
    WHERE ...
    AND bookid in ( 3, 24, 47 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Ok.. take all 66 tables, find a common schema between them all, and create a new table (based on that common schema), and add a new column called "recordType". It'll be a character field.. how long is up to you, but 2 characters should be enough. Now when you insert from the 66 tables into the one table, you'll include the record type (Example: table "book" may have a recordType of "bk", and psalm might be "ps", etc).

    Now take your radio buttons and apply the recordType values to their respective radio buttons (or checkboxes for that matter - this is what you'd use if you want to be able to query multiple record types at the same time).

    In your ASP, you'd take the recordType from the form and apply that to your SQL:

    SQL = "select * from bibleTable where query LIKE '%" & Request.Form("Query") & "%' AND recordType = '" & Request.Form("recType") & "'"

    Now, if you use checkboxes, you'll have to Split the Request.Form("recType") value on the "," character into an array, then loop through it adding multiple "OR recordType..." clauses to the SQL

    Edit: I may be off on my radio button names, my office blocks tripod, but the idea is roughly the same
    Last edited by Seppuku; 08-03-04 at 20:54.
    That which does not kill me postpones the inevitable.

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice post, Seppuku

    book number, book code, recordType -- it doesn't really matter, does it

    (omigod, did i just accidentally open up the surrogate-versus-natural can of worms? )

    .
    ... then loop through it adding multiple "AND recordType..." clauses to the SQL
    OR, shurely
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Eeps! Righty-o...
    That which does not kill me postpones the inevitable.

  11. #11
    Join Date
    Jul 2004
    Posts
    494

    how to keep 001,002...

    I want to convert the column number to 001,002,003, 004 but it automatically converts to 1,2,3,4. If I use the search to search for numbers example:
    search 1
    result would be 1, 10,11,12,13...

    But if I would search 001
    result would be 001

  12. #12
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    the field is probably a character field then... you should make sure it's a number field..
    That which does not kill me postpones the inevitable.

  13. #13
    Join Date
    Jul 2004
    Posts
    494

    i've pasted from excel 2000

    Is there a easy way to fix that by Excel or MSAccess?

    By the way I'm doing what you said and it's easier than I thought. Thanks.

  14. #14
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Quote Originally Posted by gilgalbiblewhee
    Is there a easy way to fix that by Excel or MSAccess?
    If you're doing this in Access, all you should need to do is change the column type to an integer.

    Quote Originally Posted by gilgalbiblewhee
    By the way I'm doing what you said and it's easier than I thought. Thanks.
    Isn't normalization a wonderful thing? You'll save yourself a lot of headache this way.
    That which does not kill me postpones the inevitable.

  15. #15
    Join Date
    Jul 2004
    Posts
    494

    can you be more specific on recType?

    I'm a newbie and I don't know the terms like looping.

    This is what I have so far:

    <%@ LANGUAGE="VBSCRIPT" %>
    <html>
    <head>
    <TITLE>bible.asp</TITLE>
    </head>

    <body>
    <%
    SqlBible = "SELECT * FROM bible WHERE query LIKE '%" & Request.Form("Query") & "%' AND recordType = '" & Request.Form("recType") & "'"

    Set dbGlobalWeb = Server.CreateObject("ADODB.Connection")
    dbGlobalWeb.Open("kjv")

    dim mySearch, iCounter
    mySearch=Request.QueryString("mySearch")
    iCounter = 0

    'number
    If request.QueryString("book")="yes" then

    SqlBible = SqlBible & "book LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    'number
    If request.QueryString("book_spoke")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "book_spoke LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if


    'text
    If request.QueryString("book_title")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "book_title LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    'number
    If request.QueryString("chapter")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "chapter LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    'number
    If request.QueryString("chapter_spoke")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "chapter_spoke LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    'number
    If request.QueryString("verse")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "verse LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    'number
    If request.QueryString("verse_spoke")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "verse_spoke LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    'number
    If request.QueryString("text_data")="yes" then

    If iCounter > 0 Then

    SqlBible = SqlBible & " AND "

    End If

    SqlBible = SqlBible & "text_data LIKE '%" & mySearch & "%'"

    iCounter = iCounter + 1

    end if

    Set rsGlobalWeb = Server.CreateObject("ADODB.Recordset")
    rsGlobalWeb.Open SqlBible, dbGlobalWeb, 3%>

    <%
    If rsGlobalWeb.BOF and rsGlobalWeb.EOF Then%>

    <h2 align="center">We did not find a match!</h2>
    <%Else%>


    <%If Not rsGlobalWeb.BOF Then%>

    <h2>These are the results:</h2>

    <table BORDER="0" width="100%" cellpadding="3">
    <tr>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book Spoke </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Book Title </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Chapter </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Chapter Spoke</font></th>
    </td>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse </font></th>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Verse Spoke</font></th>
    </td>
    <th bgcolor="#800000"><font face="Arial" color="#FFFFFF">Text </font></th>
    </tr>
    <%
    Do While Not rsGlobalWeb.EOF
    %>
    <tr>
    <td><%=rsGlobalWeb("book")%>
    </td>
    <td><%=rsGlobalWeb("book_spoke")%>
    </td>
    <td><%=rsGlobalWeb("book_title")%>
    </td>
    <td><%=rsGlobalWeb("chapter")%>
    </td>
    <td><%=rsGlobalWeb("chapter_spoke")%>
    </td>
    <td><%=rsGlobalWeb("verse")%>
    </td>
    <td><%=rsGlobalWeb("verse_spoke")%>
    </td>
    <td><%=rsGlobalWeb("text_data")%>
    </td>
    </tr>
    <% rsGlobalWeb.MoveNext
    Loop
    %>
    </table>
    <%End If%>
    <%End If%>
    <%
    rsGlobalWeb.Close
    dbGlobalWeb.Close
    %>
    </body>
    </html>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •