Multiple search words problem

[matissen]

I want to search using multiple search words, and criterias. The criterias are: "contains, starts with and matches". I have problem with both multiple words, and criterias.

When using "start with" and the searchwords in the table are "blue, happy, summer" and someone searched for "summ" nothing comes up, but a search on "blu" shows that image (because it searches the whole string)

This if my form:

<td><b>Bildsökning</b>

<table bgcolor=#FFFFFF border=0 width=100%>
    	<tr>
     <td><form action="go.php" method="get" name="searchimageform">
    <table border="0" align="left" cellpadding="2" cellspacing="0">

      <tr class="table_leftnav">
  		<td><font face=verdana color=#B8622D>Sökord:</font><div align="left">
            <input name="searchForm" type="text" id="searchForm"></div>
	<br>
	<font face=verdana color=#B8622D>Kriterier:</font><br>
	<select name="kriterier">
	<option value="1" selected>Inneh&aring;ller</option>
	<option value="2">B&ouml;rjar med</option>
	<option value="3">Matchar exakt</option>
	</select>
          </td>
      </tr>
      <tr> 
        <td><br><div align="left"> 
            <input type="submit" name="Submit" value="S&ouml;k bild">
          </div></td>
      </tr>
    </table>
    <input name="navid" type="hidden" value="search">
    <input name="navmainname" type="hidden" value="Search">
    <input name="display" type="hidden" value="gallery">
    <input name="nav" type="hidden" value="thumbs">
  </form>

</td>

This is the sql:

elseif($navid=='search' && $kriterier=="1")
	{
		$sql_text = ("SELECT * FROM mpgallery_photos WHERE photoSearch LIKE UCASE('%$searchForm%') AND photoVerified='yes' OR photoSearch LIKE LCASE('%$searchForm%') AND photoVerified='yes'");
	}
	elseif($navid=='search' && $kriterier=="2")
	{
			$sql_text = ("SELECT * FROM mpgallery_photos WHERE photoSearch LIKE UCASE('$searchForm%') AND photoVerified='yes' OR photoSearch LIKE LCASE('$searchForm%') AND photoVerified='yes'");
	}
	elseif($navid=='search' && $kriterier=="3")
	{
		$sql_text = ("SELECT * FROM mpgallery_photos WHERE photoSearch LIKE UCASE('$searchForm') AND photoVerified='yes' OR photoSearch LIKE LCASE('$searchForm') AND photoVerified='yes'");
	}

I know I probably have to use fulltext and sort my seach words into an array using explode, but could someone please show me how this is done?

[paulnaj]

Hi matissen,

I haven't checked this so it might not work but ...

$sql = "
	SELECT * 
	FROM mpgallery_photos 
	WHERE LCASE(photoSearch) LIKE '%".strtolower($searchForm)."%' 
	AND photoVerified = 'yes'
";

... looks good to me. The trick is to lowercase both needle and haystack!

Paul 😉

[matissen]

the above code for criteria "1" works just fine.
Criteria 2 and 3 however get problematic sine it searches "blue, summer, happy" and not "blue" "summer" "happy" as separate words.

I alse want to be able to search using mutiple words, and search a table consiting of several searchwords. Someone gave me the tip to do a fulltext search in BOOLEAN mode, but I have never done this and do not know how to code it.

I think you misunderstood my problem.

Anyone else?