Hi,
I have built a database with MySQL and wish to use a FULLTEXT Boolean search to get results from one of the tables. Utilising dreamweaver to create a search results page, the recordset SQL statement reads:

SELECT *
FROM test
WHERE MATCH(bizName, occupation, county, details) against ('$textfield' IN BOOLEAN MODE)


'textfield' is a variable which has been passed to the results page from a textfield search box (Run time value = $HTTP_POST_VARS['textfield']).

The issue im having with this search is that when the variable has been passed to the Boolean search statement it does not appear in a useful boolean form. For example, if 'plumber shropshire' was typed into the search page, it appears in the search statement as ('plumber shropshire' IN BOOLEAN MODE), when i really need it to use ('+plumber +shropshire' IN BOOLEAN MODE). If anybody can give me a bit of advice on this subject it would be greatly appreciated.

Thanks in advance.

This has been discussed before <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> Try to see if this topic is useful.
www.dmxzone.com/forum/topic.asp?topic_id=34807
if not then come back n i'll help you out.

yeah, i checked that topic out already, its kind of the same problem but still dont know how to get a string of words that are typed into a search box to be passed with the boolean + in front of each, so that the sql query works properly.

For example, at the moment if 'plumber shropshire' is typed into the search box i have just one variable ($textfield) which i can put a + in front of. Doing this will not ensure that both words have to be present in the search results. Instead results with either plumber or shropshire are returned.

Ideally i need the variable to be passed with a + in front of each word (e.g. +plumber +shropshire) for any amount of words that the user of the site types in. Is this possible, or shal i get MX site search to make life easy.

<pre id=code><font face=courier size=2 id=code>
$keywords = explode(" ", $textfield);

$query = "(SELECT *
FROM test
WHERE MATCH(bizName, occupation, county, details) against ('";
for ($i = 0; $i &lt; count($keywords); $i++)
{
$query.="+$keywords[$i] ";
}
$query.="' IN BOOLEAN MODE);
</font id=code></pre id=code>
Try it out.

Edited by - roders22 on 17 May 2006 17:48:14

Edited by - roders22 on 17 May 2006 17:49:08

Cheers for the last post, i think this is along the right lines (and probably is right, i just cant implement it properly).

I've entered the sql query into the dreamweaver recordset now but am getting an error in the code when testing it. The code now reads:

$textfield_Recordset1 = "%";
if (isset($HTTP_POST_VARS['textfield'])) {
$textfield_Recordset1 = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['textfield'] : addslashes($HTTP_POST_VARS['textfield']);
}
mysql_select_db($database_TFBcon3, $TFBcon3);
$keywords = explode(" ", $textfield);
$query_Recordset1 = sprintf("SELECT * FROM test WHERE MATCH(bizName, occupation, county, details) against ('"; for ($i = 0; $i &lt; count($keywords); $i++) { $query.="+$keywords[$i] "; } $query.="' IN BOOLEAN MODE);", $textfield_Recordset1);
$query_limit_Recordset1 = sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $TFBcon3) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);


Can you see anything here that is obviously wrong? Im not really used to coding so would be grateful of any advice.

Thanks again.


Edited by - tradefeedback on 18 May 2006 15:39:14

Not Tested, but try it.

$textfield_Recordset1 = "%";
if (isset($HTTP_POST_VARS['textfield']))
{
$textfield_Recordset1 = (get_magic_quotes_gpc()) ? $HTTP_POST_VARS['textfield'] : addslashes($HTTP_POST_VARS['textfield']);
}

mysql_select_db($database_TFBcon3, $TFBcon3);
$keywords = explode(" ", $textfield_Recordset1);
$query_Recordset1 ="SELECT * FROM test WHERE MATCH(bizName, occupation, county, details) against ('";
for ($i = 0; $i &lt; count($keywords); $i++)
{
$query_Recordset1.="+$keywords[$i] ";
}
$query_Recordset1.="' IN BOOLEAN MODE)";
$query_limit_Recordset1.= sprintf("%s LIMIT %d, %d", $query_Recordset1, $startRow_Recordset1, $maxRows_Recordset1);
$Recordset1 = mysql_query($query_limit_Recordset1, $TFBcon3) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);

Edited by - roders22 on 18 May 2006 15:53:52

This appears to work perfectly. Thanks for sharing your knowledge.