Page 1 of 1
Getting php to read and match search items in any order
Posted: Wed Sep 28, 2005 6:12 am
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
Posted: Wed Sep 28, 2005 11:29 am
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.
Posted: Thu Sep 29, 2005 4:15 am
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
Posted: Thu Sep 29, 2005 10:13 am
by pickle
Posted: Thu Sep 29, 2005 10:32 am
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
DB version
Posted: Thu Sep 29, 2005 11:02 am
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