Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Dec 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL field search

    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"

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    Use LIKE.

    Code:
    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?

  • #3
    New Coder
    Join Date
    Dec 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by guelphdad
    Use LIKE.

    Code:
    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).
    PHP Code:
    SELECT FROM `ow_apsWHERE 'award_comment' LIKE '%shadow%' AND 'update_date' BETWEEN '1141298027' AND '1147370111' 
    That comes up with zero results, but there should be three. Any tips?
    Last edited by PureMisery; 07-30-2006 at 04:23 AM.

  • #4
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.
    Last edited by guelphdad; 07-30-2006 at 08:09 AM.

  • #5
    New Coder
    Join Date
    Dec 2005
    Posts
    27
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 ]


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •