SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy Really need help with search functionality (probably a typical newbie question)

    I'm doing a project for school and that involves a website with database functions. I'm new to this forum and quite new to CF so please be a little patience with me I have to take care of a search and results page! These pages are ment for other students to find out if a certain book is present in the schools Library. So I started off with setting up a database withe following tables and fields:

    Table: books Fields: (bookID, bookName, cover, descrption, keywords, writerID, genreID)

    Table: writers Fields: (writerID, writerName)

    Table: genres Fields: (genreID, genreName)

    As you can see are the tables writers and genres related to the table books by their ID.

    (Note: This database is based on something I found in a tutorial)

    I have a search page/form with the following search options:


    writer, genre and keywords (<form action="results.cfm">
    (note: the form fields writer and genre are dynamiccaly generated by the tables writers en genres)

    I have the following querys

    <cfquery name="results" datasource="cf_books">
    SELECT bookName, descrption, keywords, writerID, genreID
    FROM books, writers, genres
    WHERE writers.writerID = books.writerID
    AQND genres.genreID = books.genreID
    </cfquery>
    <cfquery name="writers" datasource="cf_books">
    SELECT * FROM writers
    </cfquery>
    <cfquery name="genres" datasource="cf_books">
    SELECT * FROM writers
    </cfquery>

    But from this point it's really getting con(old)fusing(sion) for me, because what do I have to do from here

    On the results page I used the same querys as above but what do I use the to display the data the right way depending on which search criteria was used in the search page.

    Right now I have the following values in the <cfoutput query> #results.bookName#, #results.description#</cfoutput> but I don't know what to use to output the writer and the genre the right way?

    Do I use writerID and genreID from the results query or do I use them from their own querys. I tried both but nothing seems to work and how do I make sure that when a studend chooses a certain writer in the search form that only the books from that writer are dipayed.

    I really hope that there is somebody is willing to help me in the right direction.

    Thanks in advance

  2. #2
    SitePoint Enthusiast
    Join Date
    Oct 2006
    Location
    Orange County, CA
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you have a form field I am going to assume someone has to enter a keyword such as the title or description, so you have a form like this..

    <form action="results.cfm" method="post">
    <input type="text" name="searchcrit"> (say I enter in Hockey)
    <input type="submit" name="SearchForm" value="Search">
    </form>

    Then, on results.cfm you would do the query using the form values (assuming you are using some version of SQL)

    <cfquery name="results" datasource="cf_books">
    SELECT b.bookName, b.descrption, b.keywords, w.writerName, genreName
    FROM books b
    JOIN writers w on b.writerid = w.writerid
    JOIN genres g on b.genreid = g.genreid
    WHERE b.bookname LIKE '%#FORM.searchcrit#%'
    OR b.Description LIKE '%#FORM.searchcrit#%'
    </cfquery>

    This may seem a little confusing, but i'll try my best to explain it. I'm using the letters b,w, and g to create a "shortcut" of each word so I don't have to put books, writers, and genres before each word, and the JOIN will connect the tables based the writerid and genreid, so you can retrieve data from those tables by just using the letter and the column name.

    LIKE is going to search the two columns for whatever you put in the search field, however it's a good idea to sanitize any data someone can puts in there for security reasons.

    After your query, you can do a <cfdump var="#results#"> and see what you are getting. You will only see results if you have a bookname or description with your search critera.

  3. #3
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Thank you so much

    Thank you so much I'm going to try it right away and for sure let you know how it works out

  4. #4
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cyrk View Post
    If you have a form field I am going to assume someone has to enter a keyword such as the title or description, so you have a form like this..

    <form action="results.cfm" method="post">
    <input type="text" name="searchcrit"> (say I enter in Hockey)
    <input type="submit" name="SearchForm" value="Search">
    </form>

    Then, on results.cfm you would do the query using the form values (assuming you are using some version of SQL)

    <cfquery name="results" datasource="cf_books">
    SELECT b.bookName, b.descrption, b.keywords, w.writerName, genreName
    FROM books b
    JOIN writers w on b.writerid = w.writerid
    JOIN genres g on b.genreid = g.genreid
    WHERE b.bookname LIKE '%#FORM.searchcrit#%'
    OR b.Description LIKE '%#FORM.searchcrit#%'
    </cfquery>

    This may seem a little confusing, but i'll try my best to explain it. I'm using the letters b,w, and g to create a "shortcut" of each word so I don't have to put books, writers, and genres before each word, and the JOIN will connect the tables based the writerid and genreid, so you can retrieve data from those tables by just using the letter and the column name.

    LIKE is going to search the two columns for whatever you put in the search field, however it's a good idea to sanitize any data someone can puts in there for security reasons.

    After your query, you can do a <cfdump var="#results#"> and see what you are getting. You will only see results if you have a bookname or description with your search critera.
    Hi cyrk and anybody else who can maybe help,

    @ cyrk: As promised a update on your suggestion! For test purpose I gave it a try with just one formfield (keywords) on the search.cfm page. On the results.cfm page I have the following query:

    Code:
    <cfquery name="results" datasource="cf_books">
    SELECT b.bookName, b.descrption, b.keywords, w.writerName, g.genreName
    FROM books b
    JOIN writers w on b.writerID = w.writerID
    JOIN genres g on b.genreID = g.genreID
    WHERE b.bookname LIKE '%#FORM.keywords#%'
    </cfquery>
    and I dumbed straight after the query:
    Code:
    <cfdump var="#results#">
    But I'm getting the following Coldfusion error on the results.cfm page after submitting the form:

    Error Executing Database Query.
    Syntax error in FROM clause


    What am i doing wrong?

    Thank you all in advance

  5. #5
    minister of propaganda silver trophy Rynoguill's Avatar
    Join Date
    Feb 2004
    Location
    Midsouth
    Posts
    1,373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try making it INNER JOIN instead of just JOIN, but I doubt thats the problem. What database are you using?
    rynoguill
    Ryan Guill, AKA Mark Roman

  6. #6
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rynoguill View Post
    try making it INNER JOIN instead of just JOIN, but I doubt thats the problem. What database are you using?
    Locally i'm using Access but on-line it will be mySQL

  7. #7
    minister of propaganda silver trophy Rynoguill's Avatar
    Join Date
    Feb 2004
    Location
    Midsouth
    Posts
    1,373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats really not a good idea. The differences in SQL between access and mysql is enough that you are sure to run into it when you push it online.

    Did the Inner Join help?
    rynoguill
    Ryan Guill, AKA Mark Roman

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by newbiedon View Post
    Locally i'm using Access but on-line it will be mySQL
    are you familiar with the differences in the sql for these different databases?

    mysql: WHERE b.bookname LIKE '%#FORM.searchcrit#%'
    access: WHERE b.bookname LIKE '*#FORM.searchcrit#*'

    mysql: WHERE dateadded > '#FORM.datefilter#'
    access: WHERE dateadded > ###FORM.datefilter###
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    are you familiar with the differences in the sql for these different databases?

    mysql: WHERE b.bookname LIKE '&#37;#FORM.searchcrit#%'
    access: WHERE b.bookname LIKE '*#FORM.searchcrit#*'

    mysql: WHERE dateadded > '#FORM.datefilter#'
    access: WHERE dateadded > ###FORM.datefilter###
    Hi,

    To be honest I wasn't familiar with that But maybe you know if there is some kind of reference table describing those differeces in syntax? But anyway it is working now indeed Thank you so much

  10. #10
    malloc
    Join Date
    Nov 2006
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the minimal effort it would take - install a local mysql server for development too. It'll make your life much easier. Access has pretty poor SQL support, on top of the compatibility issues mentioned.


Bookmarks

Posting Permissions

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