Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 6 of 6
  1. #1
    Junior Member Hobgoblin's Avatar
    Join Date
    Sep 2003
    Location
    San Diego, CA
    Posts
    12
    Member #
    3248
    Hi,

    Here's the issue I'm having... This site I'm working on has a search feature - they have an existing MySQL database full of thousands of items. Lots of these items have a title with an apostraphe. The problem is, not every user when searching is going to include that apostraphe - I know I rarley do, I usually type "its" instead of "it's".

    Now if the database record HAS the apostraphe, how would I go about retrieving that record from a search string where the user didn't put one in?

    Needless to say, the easy way would be to to str/strstr/eregi_replace functions on common words such as won't, it's and they're, but there are many using names or places that would make that a fruitless effort.

    What would be the best way to accomplish this?
    Michael Willey
    Southland Digital Web Design & Photography
    http://www.sldigi.com/

  2.  

  3. #2
    WDF Staff Wired's Avatar
    Join Date
    Apr 2003
    Posts
    7,656
    Member #
    1234
    Liked
    137 times
    Hrm... somehow have the search function just ignore them when it sees them in the database. That way the people who don't put them could still find what they're looking for. Of course you'd still want it to search without this ignore function enabled.
    The Rules
    Was another WDF member's post helpful? Click the like button below the post.

    Admin at houseofhelp.com

  4. #3
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    Yes, can you not use the replace function to remove the apostrophes?

    WHERE REPLACE(col,'\'','') LIKE REPLACE(string,'\'','')

    or similar?

    Syntax may not be 100% correct, as I don't know how mySQL deals with escaping -- I am assuming that \' means include rather than interpret as part of the command, though I may be wrong.

    Sean

  5. #4
    Junior Member Hobgoblin's Avatar
    Join Date
    Sep 2003
    Location
    San Diego, CA
    Posts
    12
    Member #
    3248
    Originally posted by seanmiller
    Yes, can you not use the replace function to remove the apostrophes?

    WHERE REPLACE(col,'\'','') LIKE REPLACE(string,'\'','')

    or similar?

    Syntax may not be 100% correct, as I don't know how mySQL deals with escaping -- I am assuming that \' means include rather than interpret as part of the command, though I may be wrong.

    Sean
    I can't use a replace method because I couldn't do any said method until after the SQL query has taken place - and at which time, it would have skipped the rows with an apostraphe.

    I haven't found much documentation on a REPLACE syntax - there should be somewhere in a query line to tell mySQL to ignore all quotes and apostraphes.

    Here is the quer line:

    PHP Code:
    "SELECT * FROM search WHERE keywords LIKE '%$pieces[0]%' OR keywords LIKE '%pieces[1]%' or keywords LIKE '%pieces[2]%' ORDER BY id" 
    Michael Willey
    Southland Digital Web Design & Photography
    http://www.sldigi.com/

  6. #5
    Senior Member nsr81's Avatar
    Join Date
    Oct 2002
    Posts
    1,132
    Member #
    250
    Liked
    15 times
    The best way to go about accomplishing it is to use addslashes and stripslahes functions of PHP. For example if you want to insert the word " It's ", the query would be this:
    PHP Code:
    $sql "INSERT INTO searches VALUES('".addslashes("it's")."')"
    this translates to:
    INSERT INTO searches VALUES('it\'s')
    That is i, t, backslash, apostrophe, and s. Stupid vb is stripping the slash I put in.

    Now, to search, you'll addslahes to user's input:
    PHP Code:
    $searchfor addslashes($_POST['term']);
    $sql "SELECT * FROM searches WHERE keywords LIKE '%$searchfor%'"
    of course, this method will require that you preserve your apostrophes. And that the searches are done using keywords with apostrophes in them.
    There and Back Again :Ogre:

  7. #6
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    Que??

    Rubbish... the REPLACE function is *within* the SQL query, who said it was a method?

    ie.

    SELECT a,b,d,e
    FROM e
    WHERE REPLACE(f,'\'') LIKE REPLACE('%'||var||'%','\'');

    Dunno about mySQL exact syntax (I program Oracle for a living) but I have checked and the replace function exists.


    Sean


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:03 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com