SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Denver
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    LIKE query using just text (no punctuation)

    I have a PHP/MySQL query like the below:

    Code:
    SELECT * FROM content, authors WHERE 
    author_id=authors.id AND content.title LIKE '%$title%'
    The variable $title is being passed from the URL (using mod rewrite) and a function is replacing all dashes with the percent sign. So if an article is titled how-to-use-widgets then the $title variable would search %how%to%use%widgets% in the MySQL table conent, column title.

    The problem I am running into is when the article title contains things like apostrophes, periods, ect. Is there any way to make the LIKE statement search just the text?

    Thanks.

  2. #2
    SitePoint Zealot newspire's Avatar
    Join Date
    Mar 2005
    Location
    Houston, TX
    Posts
    118
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In short no.

    However, maybe you could use regular expressions instead of like. Or you make another column in your table that contains a version of the title that is only text. Then search based on the new column.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tristankelley
    The problem I am running into is when the article title contains things like apostrophes, periods, ect.
    could you explain why this is a problem?

    if you search for LIKE '%how%to%use%widgets%' then the presence of special characters in the title should make no difference whatsoever
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Denver
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If I search for

    '%a%widgets%friend%'

    and the article title is

    a widget's friend

    MySQL says it can't find the article.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    then don't do that

    search for this instead --
    Code:
    like '%a%widget''s%friend%'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Denver
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ha.

    The problem is I am getting the search string from the URL (/article/a-widgets-friend) so the apostrophe isn't in the search string to begin with.

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, i understand

    would it make sense to ask where it went?

    must've been there at some point, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Denver
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are correct - it was there at somepoint.

    preg_replace stripped it out along with other special characters in a reg exp.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    well, i personally wouldn't do that, as it destroys the string that the user is searching on, but i understand you're doing it so that you can make a url out of it

    well, hmm, i never run out of ideas, so...

    you could have a second title column in the table, one to display and one to search in
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Denver
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The second column might not be a bad idea.

    What about FULLTEXT searching, would that help anything?

  11. #11
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    it might, or it might not

    (it depends on whether MATCH thinks widgets is a match for widget's)

    you may have to test it and see
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #12
    SitePoint Zealot
    Join Date
    Sep 2004
    Location
    Denver
    Posts
    111
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No luck with the fulltext searching.

    I think I'm going to go with the second title column route.

    Also, a little off topic but what is the benefit of using a FULLTEXT search if it pulls the same results as a LIKE query. (Or maybe I'm just not using it correctly)

  13. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,250
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    too bad, i thought perhaps it might actually do stemming

    MATCH ... AGAINST ('widget*') would work, except you'd have to know to truncate the 's off, wouldn't you

    the advantage of fulltext search is that it gives you a relevance score when you are doing multi-word searches
    Every correct word in the collection and in the query is weighted according to its significance in the query or collection. This way, a word that is present in many documents will have lower weight (and may even have a zero weight), because it has lower semantic value in this particular collection. Otherwise, if the word is rare, it will receive a higher weight. The weights of the words are then combined to compute the relevance of the row.
    the operators are more sophisticated, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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