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 9 of 9
  1. #1
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts

    PHP Search Exact Phrase? What if there are slashes or ' characters?

    Hello all. How can I make a PHP search an item for the exact phrase in the database? The way I currently have it works fine, but the problem is when trying to search for entries with special characters in them, such as ' or / or \. For example, if I search for "George" it will find the item in the database called "George" and return it. However, if I search for "George's" it will not find that item in the database even if it exists. What is the best way to correct this? Thank you.

    Code:
      $term = mysql_real_escape_string($_GET['term']);
    
      $SQL_QUERY = "
      SELECT GAMEDB_Games.GameID, GAMEDB_Games.PublisherID, GAMEDB_Games.GameTitle, GAMEDB_Games.ReleaseDate, GAMEDB_Games.RarityC, GAMEDB_Games.RarityI, GAMEDB_Games.RarityB, GAMEDB_Games.ScanCAvailable, GAMEDB_Games.ScanIAvailable, GAMEDB_Games.ScanBAvailable, GAMEDB_Games.RomDownloadAvailable, GAMEDB_Games.RomDownloadID, 
        GAMEDB_Platform.Abbreviation, 
        GAMEDB_Publisher.PublisherName 
      FROM GAMEDB_Games, GAMEDB_Platform, GAMEDB_Publisher 
      WHERE GAMEDB_Games.SearchKeywords = '$term' 
        AND GAMEDB_Games.PlatformID = GAMEDB_Platform.PlatformID 
        AND GAMEDB_Games.PublisherID = GAMEDB_Publisher.PublisherID ";
    
      $sql = mysql_query($SQL_QUERY);

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Add a print $sql; to the above code, and run it with George's. Post that result please.

    Edit:
    For anyone asking why (or if I get busy here at work and can't answer for a bit), I presume a magic_quotes_gpc issue at either insertion or search time.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    skcin7 (03-08-2010)

  • #3
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    It just says:
    Resource id #3

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Quote Originally Posted by skcin7 View Post
    It just says:
    Resource id #3
    Yes, I suppose it would hah. My bad, use this: print $SQL_QUERY;
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    skcin7 (03-08-2010)

  • #5
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    Haha I don't know what that means... this is what print $SQL_QUERY; returns when I search for "Nester's Funky Bowling" (this is an actual item that is currently in the database).

    SELECT GAMEDB_Games.GameID, GAMEDB_Games.PublisherID, GAMEDB_Games.GameTitle, GAMEDB_Games.ReleaseDate, GAMEDB_Games.RarityC, GAMEDB_Games.RarityI, GAMEDB_Games.RarityB, GAMEDB_Games.ScanCAvailable, GAMEDB_Games.ScanIAvailable, GAMEDB_Games.ScanBAvailable, GAMEDB_Games.RomDownloadAvailable, GAMEDB_Games.RomDownloadID, GAMEDB_Platform.Abbreviation, GAMEDB_Publisher.PublisherName FROM GAMEDB_Games, GAMEDB_Platform, GAMEDB_Publisher WHERE GAMEDB_Games.SearchKeywords = 'Nester\\\'s Funky Bowling' AND GAMEDB_Games.PlatformID = GAMEDB_Platform.PlatformID AND GAMEDB_Games.PublisherID = GAMEDB_Publisher.PublisherID

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Great, thats what I wanted right here: 'Nester\\\'s Funky Bowling'
    As for the little chuckle, once php executes mysql_query, its result will either be false or a resource id to point an an external resource. No matter what the query was, if its successful you'll always get a Resource id #xx, which is why I had a laugh.

    Fix you're problem with this:
    PHP Code:
    if (function_exists('get_magic_quotes_gpc') && get_magic_quotes_gpc())
    {
        
    $_GET['term'] = stripslashes($_GET['term']);
    }

    $term mysql_real_escape_string($_GET['term']);

      
    $SQL_QUERY "
      SELECT GAMEDB_Games.GameID, GAMEDB_Games.PublisherID, GAMEDB_Games.GameTitle, GAMEDB_Games.ReleaseDate, GAMEDB_Games.RarityC, GAMEDB_Games.RarityI, GAMEDB_Games.RarityB, GAMEDB_Games.ScanCAvailable, GAMEDB_Games.ScanIAvailable, GAMEDB_Games.ScanBAvailable, GAMEDB_Games.RomDownloadAvailable, GAMEDB_Games.RomDownloadID, 
        GAMEDB_Platform.Abbreviation, 
        GAMEDB_Publisher.PublisherName 
      FROM GAMEDB_Games, GAMEDB_Platform, GAMEDB_Publisher 
      WHERE GAMEDB_Games.SearchKeywords = '$term' 
        AND GAMEDB_Games.PlatformID = GAMEDB_Platform.PlatformID 
        AND GAMEDB_Games.PublisherID = GAMEDB_Publisher.PublisherID "
    ;

      
    $sql mysql_query($SQL_QUERY); 
    Try that, post result.

    Also, take a read at a quick tutorial I threw together awhile back explaining what is happening and why here: http://www.codingforums.com/showthre...327#post711327
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    skcin7 (03-08-2010)

  • #7
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    Ok i tried that and now print $SQL_QUERY returns this:

    SELECT GAMEDB_Games.GameID, GAMEDB_Games.PublisherID, GAMEDB_Games.GameTitle, GAMEDB_Games.ReleaseDate, GAMEDB_Games.RarityC, GAMEDB_Games.RarityI, GAMEDB_Games.RarityB, GAMEDB_Games.ScanCAvailable, GAMEDB_Games.ScanIAvailable, GAMEDB_Games.ScanBAvailable, GAMEDB_Games.RomDownloadAvailable, GAMEDB_Games.RomDownloadID, GAMEDB_Platform.Abbreviation, GAMEDB_Publisher.PublisherName FROM GAMEDB_Games, GAMEDB_Platform, GAMEDB_Publisher WHERE GAMEDB_Games.SearchKeywords = 'Nester\'s Funky Bowling' AND GAMEDB_Games.PlatformID = GAMEDB_Platform.PlatformID AND GAMEDB_Games.PublisherID = GAMEDB_Publisher.PublisherID

    It is close but still doesn't return the item "Nester's Funky Bowling" which is in the database. Should I be storing that item in the database with a slash and then just use stripslashes when I am going to display it on the page? Is that the best way to do this do you think?

    Also thanks for posting that link to your tutorial... I will start to read up on it now and continue it later when I have time.

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,994
    Thanks
    4
    Thanked 2,662 Times in 2,631 Posts
    Nope, what you see above is correct.
    The above query will not actually include the \' inside of you're query, rather it interprets it as ' without allowing it to step outside of you're query code. This is because as a string, you need to interpret the GAMEDB_Games.SearchKeywords = 'STRINGDATAHERE' with surrounding quotations.

    If its not working, I'd suspect that damage has occured on that table. Run this:
    PHP Code:
    $sQry "SELECT * FROM GAMEDB_Games WHERE SearchKeywords LIKE 'Nester%'";
    $qry mysql_query($sQry) or die(mysql_error());
    while (
    $row mysql_fetch_assoc($qry))
    {
        
    printf("Keywords: %s\n"$row['SearchKeywords']);

    run that, post the results. I have a feeling we'll see something like: Nester\\\\\'s Funky Bowling.

    Edit:
    Also, I need to go do some work, so I'll be back soon.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 
    Been gone for a few months, and haven't programmed in that long of a time. Meh, I'll wing it ;)

  • Users who have thanked Fou-Lu for this post:

    skcin7 (03-08-2010)

  • #9
    Regular Coder
    Join Date
    Jul 2009
    Posts
    186
    Thanks
    72
    Thanked 2 Times in 2 Posts
    AHA! It works dude. Thanks. For the record, what you asked me to type in the previous post displays this:

    Keywords: Nester's Funky Bowling Nesters

    I had created a separate field called "SearchKeywords" which I created to add all the separate stupid things a user could possibly search for. I just changed the query to this: WHERE GAMEDB_Games.GameTitle = 'Nester\'s Funky Bowling' and it works well now. Thank you sir.

    I am sure I will have more questions lol. My next problem to tackle is making an advanced search.


  •  

    Posting Permissions

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