SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Full Text Search Query - Help, I'm stuck

    Hi All,

    This has been discussed a bit before, but I am trying to write a search/SELECT query for a database query. I am using full-text for this (php and mysql).

    I have a number of input boxes that I am trying to combine into one select statement, but it is not working. The issue is that not all inputs will need to be filled in and my form includes a date range search option.

    Here is the html form I am using - I have a number of drop-downs but these aren't causing any problems:

    HTML Code:
    <input type="text">
    
    <select name="drop1">
    		<option value="A">A</option>
    		<option value="B">B</option>
    		<option value="C">B</option>
    </select>
    		
    <select name="operator">
    		<option value="and">and</option>
    		<option value="or">or</option>
    		<option value="not">not</option>
    </select>
    		
    <input type="text">
    			
    <select name="drop2">
    		<option value="A">A</option>
    		<option value="B">B</option>
    		<option value="C">C</option>
    </select>
    
    //select year
    from:<input type="text"> to:<input type="text">
    So far my SELECT query works but it does not work if I use only one keyword. Also, I do not know how to make a date range query.
    Here it is:

    Code:
    "SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('keyword1*' IN BOOLEAN MODE)
    AND MATCH(column_name) AGAINST('keyword2*' IN BOOLEAN MODE)");
    My question: can you point me in the right direction or refer me to readings for this? Do I need to you an 'else' somewhere in here?

    Thanks in advance!

  2. #2
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    The key will be to build the SQL query dynamically using PHP. So if the user only enters one keyword, you build an SQL query that leaves out the whole AND MATCH section of for the second query (or to put it another way, you only add the second AND MATCH if the user enters a second keyword).

    An example of this is provided about halfway through my book, Build Your Own Database Driven Web Site Using PHP & MySQL, 4th Edition.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  3. #3
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK - so within my SELECT query, I use php?

    Thanks very much Kevin (btw, you're book is fantastic)

  4. #4
    SitePoint Author Kevin Yank's Avatar
    Join Date
    Apr 2000
    Location
    Melbourne, Australia
    Posts
    2,571
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Using PHP code, build a PHP string that contains the SQL query required, given the inputs received from the user's form submission.

    If you have the 4th edition of my book, read the "Searching for Jokes" section in Chapter 7.
    Kevin Yank
    CTO, sitepoint.com
    I wrote: Simply JavaScript | BYO PHP/MySQL | Tech Times | Editize
    Baby’s got back—a hard back, that is: The Ultimate CSS Reference

  5. #5
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi All,

    I've followed the book as Kevin suggested and it is mostly working: I am unable to get the date range query working properly, however.

    Here is the code I am using ["year" is my column_name]:
    Code:
    $where .= " AND year >= $value1 and year <= $value2";
    Is this query correct? I can get a result if $value1 is entered but if $value2 is also submitted in the form, it simply displays a blank page not my results page.

    Thanks!

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    125
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    bump


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
  •