Getting php to read mysql field in any order

elfranko

Hi all - I've built a reasonable search engine for our new web site using php and mysql which does the job - it searches on the titles of courses our company offers using CourseTitle LIKE "%' . $keywordsearch . '%"'; as the search term in the query. Now I am expanding the search so that it does a match on keywords in the query - WHERE Keywords LIKE '%" . $keywordsearch . "%'"; using a new Keyword field added to the db table with keywords associated with each course.

This also works, on single word searches. If I search say on 'solaris unix' - this also works and finds the right course. But if I search on 'unix solaris' which is different to the order of the keywords in the db field, I get a No Match. The keywords are only seperated by spaces in the field. I thought LIKE tries to match any of the data in the var against the field in the query. But in this case it seems it will only match if the search keywords are in the same order as the keywords in the db field. I've set the field to Fulltext without effect unfortunately.

Any suggestions or ideas would be great thanks!

frank

dalecosp

Hrmm, what about "or"?

$searchterms="Slowlaris Gentoo Unbuntu FreeBSD";
$searcharray=explode(" ",$searchterms);
$ct=count($searcharray);
$n=0;
$sql="select course_title from mytable where ";

while ($n<$ct) {
   $sql.="course_title LIKE '".$searcharray[$n]."' ";
   if ($n>0&&$n<($ct-1)) {
      $sql.="or ";
   }
   $n++;
}

elfranko

Hi - cheers for that; yes I was looking for a way to do this but had no idea where to start being a php noob 🙂 Anyhow I have inserted your code, as below, with some debugging to make sure all is well.

$searcharray=explode(" ",$keywordsearch); 
$ct=count($searcharray); 
//set up unix query
 $n=0; 
$query_unix="SELECT * FROM coursesUnixLinux WHERE "; 
	while ($n<$ct) { 
		$query_unix.="Keywords LIKE '".$searcharray[$n]."' "; 
		echo "n = ".$n ."<br>";
		echo "ct = ".$ct ."<br>";
			if ($n>0&&$n<($ct-1)) { 
				$query_unix.="OR "; 
			} 
			$n++; 
	                }
		echo $query_unix;

The code is erroring; this is the message:

post filter keywordsearch = unix networking
n = 0
ct = 2
n = 1
ct = 2
SELECT * FROM coursesUnixLinux WHERE Keywords LIKE 'unix' Keywords LIKE 'networking' You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Keywords LIKE 'networking'' at line 1

The reason from what I can understand is that the IF statement works out if n is greater than 0 and less than the count of the items in the array minus 1 so that an OR isn't added onto the end of the query. As n = 0 during the first loop of the WHILE, the IF is false and no OR is added hence the error. If I search on 3 items, I get this:

post filter keywordsearch = unix solaris networking
n = 0
ct = 3
n = 1
ct = 3
n = 2
ct = 3
SELECT * FROM coursesUnixLinux WHERE Keywords LIKE 'unix' Keywords LIKE 'solaris' OR Keywords LIKE 'networking' You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Keywords LIKE 'solaris' OR Keywords LIKE 'networking'' at line

which is cool because n reaches 1 and in the second iteration of the WHILE the IF is true and an OR is added.

Therefore as a solution, would moving the n++ before the IF provide a fix?

many thanks for your help in this

frank

dalecosp

Well, one thing's pretty obvious - I left out the SQL wildcard ....

// make it read:
$query_unix.="Keywords LIKE '%".$searcharray[$n]."%' ";

Therefore as a solution, would moving the n++ before the IF provide a fix?

Actually, I probably meant to simply test that there was more than one item in the array, so:

if ($ct>0&&($n<($ct-1))) {

Sorry about that; been a tad distracted lately.