Searching Dilemma

[mr.wolf]

So I have a web application I'm working on, where I'd like to have a very flexible searching feature.

The data is stored in a MySQL database, and I have 2 columns I want to search on. ( Title, Description )

I basically want to have a Basic Search and an Advanced Search. The Basic Search is pretty easy, and just does a LIKE '%$keyword%' on all three fields. However, you get a lot of false positives.

So I tried implementing a FULLTEXT search on the Description, and using the following Contains These Words and Does not Contain These Words to get really specific.

However, FULLTEXT text doesn't do full wildcard searches, only %WORD searches. So if you leave the Contains These Words field blank, and only fill in the Does not Contain These Words field, you get 0 results.

Here's a picture of the form.

[img_thumb]http://i.imgur.com/RazO1.png[/img_thumb]

Here's the code that searchs for stuff

if ( !empty( $strTags ) && !empty( $strSearch ) )
	{
		$strSQL = "SELECT p.`ID`, p.`Title`, p.`Description`, p.`Created`, p.`Deadline`, p.`Offer`, p.`UserID`, u.`Username`,
					( SELECT COUNT(*) FROM `tags` WHERE `Tag` IN ($strParsedTags) AND `JobID` = p.`ID` ) as `Score`,
					MATCH(Title) AGAINST ('?' IN BOOLEAN MODE) as `TitleScore`,
					MATCH(Description) AGAINST ('?' IN BOOLEAN MODE) as `DescriptionScore`
					FROM `jobs` p
					LEFT JOIN `users` u ON u.`ID` = p.`UserID`
					LEFT JOIN `tags` t ON t.`JobID` = p.`ID`
					WHERE `Tag` IN ($strParsedTags)
					AND MATCH(Title,Description) AGAINST ('?' IN BOOLEAN MODE)
					AND p.`Status` = 'Open'
					AND p.`Offer` >= '?'
					AND p.`Status` = 'Open' " . ( $bDeadline ? "AND p.`Deadline` != '0000-00-00 00:00:00'" : '' ) . "
					GROUP BY p.`ID`
					ORDER BY ? LIMIT ?, ?";
	} elseif ( !empty( $strTags ) ) {
		$strSQL = "SELECT p.`ID`, p.`Title`, p.`Description`, p.`Created`, p.`Deadline`, p.`Offer`, p.`UserID`, u.`Username`,
					( SELECT COUNT(*) FROM `tags` WHERE `Tag` IN ($strParsedTags) AND `JobID` = p.`ID` ) as `Score`
					FROM `jobs` p
					LEFT JOIN `users` u ON u.`ID` = p.`UserID`
					LEFT JOIN `tags` t ON t.`JobID` = p.`ID`
					WHERE `Tag` IN ($strParsedTags)
					AND p.`Status` = 'Open'
					AND p.`Offer` >= '?'
					AND p.`Status` = 'Open' " . ( $bDeadline ? "AND p.`Deadline` != '0000-00-00 00:00:00'" : '' ) . "
					GROUP BY p.`ID`
					ORDER BY ? LIMIT ?, ?";
	} elseif ( !empty( $strSearch ) ) {
		$strSQL = "SELECT p.`ID`, p.`Title`, p.`Description`, p.`Created`, p.`Deadline`, p.`Offer`, p.`UserID`, u.`Username`,
					MATCH(Title) AGAINST ('?' IN BOOLEAN MODE) as `TitleScore`,
					MATCH(Description) AGAINST ('?' IN BOOLEAN MODE) as `DescriptionScore`
					FROM `jobs` p
					LEFT JOIN `users` u ON u.`ID` = p.`UserID`
					WHERE MATCH(Title,Description) AGAINST ('?' IN BOOLEAN MODE)
					AND p.`Status` = 'Open'
					AND p.`Offer` >= '?'
					AND p.`Status` = 'Open' " . ( $bDeadline ? "AND p.`Deadline` != '0000-00-00 00:00:00'" : '' ) . "
					ORDER BY ? LIMIT ?, ?";
	} else {
		$strSQL = "SELECT p.`ID`, p.`Title`, p.`Description`, p.`Created`, p.`Deadline`, p.`Offer`, p.`UserID`, u.`Username`
					FROM `jobs` p
					LEFT JOIN `users` u ON u.`ID` = p.`UserID`
					AND `Status` = 'Open'
					WHERE `Offer` >= '?'
					AND `Status` = 'Open' " . ( $bDeadline ? "AND `Deadline` != '0000-00-00 00:00:00'" : '' ) . "
					ORDER BY ? LIMIT ?, ?";
	}

Is there a better way to perform searches like this? I am fairly new to doing such broad searches on text-based fields, so any guidance would be appreciated.