1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

SELECT WHERE LIKE Mysql Query

Discussion in 'MySQL' started by Weirfire, Mar 8, 2006.

  1. #1
    Using the following example table;

    tablea

    field1 - roger rabbit
    field2 - cartoon

    I want to extract all the rows from table a where field 1 contains the name roger. At the moment I have a query set up like this

    SELECT * FROM tablea WHERE "roger" LIKE '%field2%'

    but this returns nothing.

    Only when I set field 1 to just "roger" do I get it to return any rows.

    What am I doing wrong with my query? :)
     
    Weirfire, Mar 8, 2006 IP
  2. T0PS3O

    T0PS3O Feel Good PLC

    Messages:
    13,219
    Likes Received:
    777
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Show us the column headings and one or two rows of sample data. You're confusing me with the way you posted it.
     
    T0PS3O, Mar 8, 2006 IP
  3. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #3
    
    "SELECT * FROM tablea WHERE field2 LIKE '%roger%'"
    
    PHP:
    It looks like you just had your query backwards
     
    jestep, Mar 8, 2006 IP
  4. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #4
    But then is that not effectively saying

    SELECT * FROM tablea WHERE "roger rabbit" LIKE '%roger%' which would give no results because "roger rabbit" doesnt exist within the LIKE variable.

    I'll try and explain it more clearly for T0PS benefit :)


    I have a table which has several fields;

    e.g.

    cartoon_characters
    ----------------------------------------------------
    |name | age | description |
    ----------------------------------------------------
    |roger rabbit | 12 | a silly rabbit |
    ----------------------------------------------------
    |donald duck | 13 | a quacky duck |
    -----------------------------------------------------

    And basically I want all the cartoon characters that have the name "roger" somewhere in the table which is why I would like to use the LIKE option.

    So if I have a query;

    SELECT * FROM cartoon_characters WHERE "roger" LIKE '%name%'

    what am I doing wrong with the query?
     
    Weirfire, Mar 8, 2006 IP
  5. chengfu

    chengfu Well-Known Member

    Messages:
    113
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    108
    #5
    You should use it the way jestep suggested it. It should work then.
     
    chengfu, Mar 8, 2006 IP
  6. mad4

    mad4 Peon

    Messages:
    6,986
    Likes Received:
    493
    Best Answers:
    0
    Trophy Points:
    0
    #6
    SELECT * FROM cartoon_characters WHERE "name" LIKE '%roger%'
     
    mad4, Mar 8, 2006 IP
  7. dct

    dct Finder of cool gadgets

    Messages:
    3,132
    Likes Received:
    328
    Best Answers:
    0
    Trophy Points:
    230
    #7
    I think I know what your saying and you can't do it the way you're trying. You'll need to split your where clause a bit like
    
    SELECT 
         * 
    FROM 
        cartoon_characters 
    WHERE 
         "name" LIKE '%roger%' or "name" LIKE '%robert%'
    
    Code (sql):
    So in your PHP you'll build the where clause depending on how many words you need. You may also want to look at full text indexing for both performance and ease. I'm getting a strong sense of deja vu here :)
     
    dct, Mar 8, 2006 IP
  8. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #8
    ok I'll give that a go and see if it works. :confused:

    Thanks for your help guys :)
     
    Weirfire, Mar 8, 2006 IP
  9. Weirfire

    Weirfire Language Translation Company

    Messages:
    6,979
    Likes Received:
    365
    Best Answers:
    0
    Trophy Points:
    280
    #9
    Yeah I love that full text index with MySQL v5

    Unfortunately the server uses MySQL v4 and won't upgrade. :mad:


    Thanks for the help :)

     
    Weirfire, Mar 8, 2006 IP
  10. savani24

    savani24 Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #10
    ant to serach in table with "y "then i want to retrieve all the record which contain y what i do which query pls tell me
     
    savani24, Jul 18, 2012 IP
  11. zinist

    zinist Banned

    Messages:
    91
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    43
    #11
    Hi savani,
    You want to retrieve all names that contain "y"
    try this
    select * from Table1 where name like '%y%'
     
    zinist, Jun 29, 2015 IP