MySQL searching (MATCH)
J_Maxwell
Aug 12, 2003
7:41 PM |
Hello!
How do I search for a value in a MySQL table? I have tried the match function, but that seems to yield weird results.
Just in case you're curious as to what the match function looked like:
SELECT * FROM members WHERE MATCH (username, name, zip) AGAINST ('$tmem');
Also, it seemed to have a problem with the column email. When I had the email column in there, it returned no results, but it did return results if I had the email column not there.
Also, this function doesn't seem to grab the user if I specified one letter (like if I wanted to find all of the records that had "J" in it). It only works if it is a whole word.
I had also thought about manually, in PHP using eregi, but I would think that would be server intensive if there are a lot of users to iterate through each row and each column.
Thanks,
J_Maxwell |
chapmanTrain
Aug 13, 2003
7:54 AM |
J,
The MATCH function requires FULLTEXT indexes on the columns in which you are searching. If you don't have the FULLTEXT indexes, I'm not sure what kind of results you'll get. If you are looking to find a piece of a word in a field, use the LIKE, or REGEXP comparisons. MySQL has a very good on-line manual that has a good SQL section:
[HYPERLINK@www.mysql.com]
Norm |
J_Maxwell
Aug 13, 2003
11:05 AM |
chapmanTrain,
Thank you so much for your reply. That is just what I needed.
LIKE was just what I needed!
Thanks,
Joseph Maxwell |
dave
Aug 13, 2003
11:51 AM |
Norm is right with all of this. I'll add in some thoughts:
FULLTEXT is a new feature of MySQL 4 and on the surface looks like exactly what we've all wanted for quite some time.
But, after using it for a while, I realized that the limitations of these fulltext searches are too great, rendering the thing completely unusable.
1) As Norm mentioned, you have to have a fulltext index turned on. Try indexing the body of posts in a forum system with over 10 gigs of content in the body text column. :( That's enough right there to not use this feature.
2) The match() against() syntax is attractive, but it limits you severely. You are then relying on MySQL's own system for matching, and I have found it to be lacking. It returns results that folks aren't expecting, and people complained to me when I implemented it in my searching some time ago.
3) Using like('%...%') is powerful because you can do your pre-processing on the search terms before sending it to mysql. This means you can split on spaces, look for -+ and so forth, and feed exact things into MySQL and get back exactly what you were expecting.
I'll be sticking with LIKE until MySQL comes out with a better fulltext search.
Dave |
Tony_in_OK
Sep 17, 2003
10:41 PM |
Depending on how much sql you know already, you might find these tutorial sites nice:
[HYPERLINK@sqlcourse.com]
[HYPERLINK@www.sqlcourse2.com]
[HYPERLINK@sqlzoo.net]
hope these help out. ;) |
|
Posting in this forum is limited to members of the group: SITEADMINS, SUBSCRIBERS, MEMBERS.]
|
| If you don't have an account yet, visit the registration page to sign up. If you already have an account, you may login here:
|
|
|
Welcome to the Open Forums!!
|
Welcome to the Open Forums!!
|
|
|