Getting php to read and match search items in any order

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Getting php to read and match search items in any order

Post by sleazyfrank »

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. This works quite well. 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.

Any suggestions or ideas would be great thanks!

frank
User avatar
Skara
Forum Regular
Posts: 703
Joined: Sat Mar 12, 2005 7:13 pm
Location: US

Post by Skara »

Code: Select all

WHERE MATCH (row1[,row2[,row3[,...]]]) AGAINST ('text to search for')
The row/s you want to search have to be in one fulltext field.
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post by sleazyfrank »

Hi - thanks for the reply but sorry am a little lost - only a php noob - I've set the field with the keywords in to Fulltext, but can you please explain further re your code? thanks!

frank
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

Post by sleazyfrank »

Hi - with a little help I have got to this:

Code: Select all

$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)) {  
                echo "IF is true<br>";
                $query_unix.="OR ";  
            }  
            $n++;  
                    } 
        echo $query_unix;
Which almost works!

Except I get this error:

debug mode
post filter keywordsearch = unix networking
n = 0
ct = 2
n = 1
ct = 2
SELECT * FROM coursesUnixLinux WHERE Keywords LIKE 'microsoft' 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
Which is basically saying the IF failed and therefore an OR was not put into the query. With an OR it works fine.

With a three word search I get this:

debug mode
post filter keywordsearch = solaris unix networking
n = 0
ct = 3
n = 1
ct = 3
IF is true
n = 2
ct = 3
SELECT * FROM coursesUnixLinux WHERE Keywords LIKE 'solaris' Keywords LIKE 'unix' 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 'unix' OR Keywords LIKE 'networking'' at line 1


Any suggestions?

cheers

frank
sleazyfrank
Forum Commoner
Posts: 40
Joined: Fri Aug 19, 2005 3:59 am
Location: Horsham, West Sussex

DB version

Post by sleazyfrank »

Hi - plus.net - our hoster - is running MySQL 4.0.18 - is this an issue with the OR statement?

The stupid thing is that this code:

Code: Select all

$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)) { 
							   		echo "IF is true<br>";
								  $query_unix.="OR "; 
							   } 
							   $n++; 
							}
now produces this result (After an edit to the IF)
debug mode
post filter keywordsearch = unix networking
n = 0
ct = 2
IF is true
n = 1
ct = 2
SELECT * FROM coursesUnixLinux WHERE Keywords LIKE 'unix' 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 'networking'' at line 1
The weird this is is that I ran the query through the plus.net phpadmin query tester and it reports it's fine. :(

weird.

frank
Post Reply