Full Text Search Query - Help, I'm stuck

[swto]

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:

<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:

"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!

[Kevin Yank]

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.

[swto]

OK - so within my SELECT query, I use php?

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

[Kevin Yank]

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.

[swto]

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]:

$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!

[swto]

bump