...

View Full Version : MySQL field search



PureMisery
07-30-2006, 01:13 AM
I need a way to search a field for a keyword.

The field can have values such as "1 shadow", "4 shadow", "shadow 4/5", "joined g2", "skilled g5", and other similar values.

I need something that would return all instances of a keyword.
Ex, search for "shadow" would return "1 shadow", "4 shadow", "shadow 4/5"

guelphdad
07-30-2006, 01:53 AM
Use LIKE.


select
foo,
bar,
qux
from yourtable
where yourfield
like '%shadow%'
Also do you mean your field contains all of those items at once or there's multiple rows with one each of those values in it?

PureMisery
07-30-2006, 03:49 AM
Use LIKE.


select
foo,
bar,
qux
from yourtable
where yourfield
like '%shadow%'
Also do you mean your field contains all of those items at once or there's multiple rows with one each of those values in it?
each item inside "" would be one row

I got it working now, I had tried using LIKE before I posted but didn't use %. Works great now.

Edit, now I'm trying to limit the results to only entries between two dates (unix time stamps).

SELECT * FROM `ow_aps` WHERE 'award_comment' LIKE '%shadow%' AND 'update_date' BETWEEN '1141298027' AND '1147370111'

That comes up with zero results, but there should be three. Any tips?

guelphdad
07-30-2006, 08:05 AM
can you show those three rows?

As for LIKE you always have to use at least one % or you won't get a match. If you want an exact match of course you would use =.

% after a term says the term must start with that term must be the first word or part of the first word. % before a term says it can appear anywhere in the sentence. THe drawback with %searchterm is that you can no longer use an index on that column, well you can have an index on the column, but it would be ignored for the purpose of the search and thus be slower as a result.

PureMisery
07-30-2006, 07:19 PM
The rows look something like this:

[ award_to ] [ update_date ] [ award_comment ] [ admin ]
[ Baggings ] [ 1141298028 ] [ 1 Shadow ] [ Webmaster ]
[ Baggings ] [ 1142175307 ] [ 1 shadow potion ] [ 1337Chicken ]
[ TripDanzig ] [ 1147370110 ] [ Shadow Potion ] [ Webmaster ]



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum