• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1042
  • Last Modified:

MySql/PHP search field for words

What would be a clever way to take user input (a few words) and search a field in a mysql database for these words?

I need a good example.


Thanks

Michel
0
Michel Plungjan
Asked:
Michel Plungjan
  • 11
  • 5
  • 3
  • +1
3 Solutions
 
nizsmoDeveloperCommented:
A common way to do this is:

first get the input search query:

$searchquery = $_REQUEST["query"];

then trim white spaces:
$searchquery = trim($searchquery);

then we can simply build the query from the searchquery:

$query = "select * from the_table where 1st_field like \"%$searchquery%\"  
  order by 1st_field"; // Edit "the_table and "1st_field to what you are wanting to search and which field

Then using mysql_query() pass in the query and the results should be returned!

Let me know how you get on.
0
 
RoonaanCommented:
mplungjan,

You preferable would use a fulltext indexed field and use the match agains sql syntax:

SELECT COUNT(*) FROM articles WHERE MATCH (title,body)AGAINST ('database')
or
SELECT id FROM tutorials WHERE MATCH (title,cat,text) AGAINST('*test*' IN BOOLEAN MODE);

More details on match against can be found at http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

Kind regards,

-r-
0
 
Michel PlungjanIT ExpertAuthor Commented:
Hehe, you are fast. I just read and wanted to paste
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html
here to ask if that is not overkill when I have perhaps 10 words in a field and the user types 1-2 words

HOWEVER There may also be a situation where I need a search of the words in a title against a field with words - I am not sure how to get a hit on

Title: the quick brown fox jumps over lazy dogs
as search argument against a mySql field that has

fox, quick, dog

for example
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Michel PlungjanIT ExpertAuthor Commented:
PS: I'll be back tomorrow
0
 
Michel PlungjanIT ExpertAuthor Commented:
So for a title like
"A title is a prefix or suffix added to a person's name to signify either veneration"

and a search like

select * from "mytable" where "keywords" like "%person%"

works fine...

Here is a search from the link

SELECT MATCH(keywords) AGAINST ('person veneration') as Relevance FROM mytable WHERE MATCH
(keywords) AGAINST('person+ veneration'+ IN
BOOLEAN MODE) HAVING Relevance > 0.2 ORDER
BY Relevance DESC

but it does not return anything

Can you perhaps explain?

I have fulltext indexed the keyword field
0
 
nplibCommented:
$searh = $_GET['search'];
$search = explode(" ", $search);

$query = "Select * from mytable where keywords\n";
foreach ($seach as $s) {
    $query .= "like '$s %' or like '% $s' or like '% $s %' and\n";
}
$query = substr($query, 0, -4);
$query .= ";";
0
 
RoonaanCommented:
mplugjan,

The cause of your problem might be a limited dataset. When a word is in more than 50% of your rows, mysql treats your word as a stopword.

I tried a setup myself containing two rows with the same title (actually the one you posted). The query returned nothing.
I then added four rows with rubbish and your query started to return rows.

Could you try increasing your dataset size?

Kind regards

-r-
0
 
Michel PlungjanIT ExpertAuthor Commented:
Ohhh - haha - now I get the warning on that page..

I only had two rows. So 50% is exactly right...

Perhaps I should not use this method then...

I do need to return rows - if none are returned it should be because the search was unsuccessful and not because of the size of the dataset. I do not mind getting all rows returned as long as the REAL stopwords are not in there (the, a, an)

It would not be good if the search is for a word that is in all rows and I then get nothing..

nplib's suggestion is understandable without a degree in statistical analysis, it looks like it will work even if only one of the keywords are in the record - however I do like the ranking given by the more complex query ... ohhhh decisions decisions

0
 
RoonaanCommented:
I am not terribly fond of the 50% limit. Didn't know of it until just now.

When you are going into fullfledged searching, a nice thing to look at for smaller datasets is the Zend_Search_Lucene in the zend's framework.
I tried to get it working, but it got into memory issues while indexing 50.000+ records.  

-r-
0
 
Michel PlungjanIT ExpertAuthor Commented:
I guess I need

  $query .= "like '$s %' or like '% $s' or like '% $s %' OR\n";
and not
  $query .= "like '$s %' or like '% $s' or like '% $s %' AND\n";

to get hits on ANY search word
0
 
Michel PlungjanIT ExpertAuthor Commented:
Thanks roonan. I know of Lucene. It is vastly overkill in this case.

Perhaps at a later stage...
0
 
nplibCommented:
changing the last AND to OR will make your search more wide spread, while leaving it as the AND will make it more specific. For Example,

---------------------------------------------------------------------
1     |       This row contains the word bob and jon
______________________________________________
2     |       This row contains the word Jason and bill
______________________________________________

If you leave it has AND and you do a search for "Bob Jon" you will return a result.
If you search for "Bob Bill" you will not return a result.
If you change it to OR and you do a Search for "Bob Jon" you will return a result.
If you search for "Bob Bill" you will return two results.

It depends on your needs.
0
 
Michel PlungjanIT ExpertAuthor Commented:
Yes - I know.....
By the way is LIKE case sensitive?

0
 
nplibCommented:
No.
0
 
Michel PlungjanIT ExpertAuthor Commented:
NPLib, I get errors.
Am I correct in guessing that the syntax should be

$query = "Select * from mytable where keywords like\n";
foreach ($seach as $s) {
    $query .= "'$s %' or '% $s' or '% $s %' or\n";
}

e.g. only one like?

Thanks
0
 
Michel PlungjanIT ExpertAuthor Commented:
Someone?

 Select * from `mytable` where
`keywords` like `theft %` or `% theft` or `% theft %` or `user %` or `%
user` or `% user %` ;
Statement gave error: [Unknown column 'theft %' in 'where clause']

Please advice, thanks
0
 
Michel PlungjanIT ExpertAuthor Commented:
Ok, no errors if I do

Select * from mytable where keywords like `theft %` or `% theft` or `% theft %` or `user %`
or `% user` or `% user %` ;

but also no results

I do get ONE result for

Select * from mytable where keywords like `%theft%`or `%user%` ;

even though the first row has theft and the second has user
0
 
nplibCommented:
Sorry, been away for the weekend.

I'm reading your response now.
0
 
nplibCommented:
sorry,

I made a mistake,

$search = $_GET['search'];
$search = explode(" ", $search);

$query = "Select * from mytable where\n";
foreach ($search as $s) {
    $query .= "keywords like '$s %' or keywords like '% $s' or keywords like '% $s %' and\n";
}
$query = substr($query, 0, -4);
$query .= ";";

if you want to make it more broad then change the last and to or, and chante substr($query, 0, -4); to substr($query, 0, -3);
0
 
Michel PlungjanIT ExpertAuthor Commented:
Thanks!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 11
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now