...

View Full Version : PHP Search Exact Phrase? What if there are slashes or ' characters?



skcin7
03-08-2010, 08:49 PM
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.



$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);

Fou-Lu
03-08-2010, 08:58 PM
Add a print $sql; to the above code, and run it with George's. Post that result please.


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.

skcin7
03-08-2010, 09:30 PM
It just says:
Resource id #3

Fou-Lu
03-08-2010, 09:41 PM
It just says:
Resource id #3

Yes, I suppose it would hah. My bad, use this: print $SQL_QUERY;

skcin7
03-08-2010, 09:57 PM
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

Fou-Lu
03-08-2010, 10:05 PM
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:


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/showthread.php?p=711327#post711327

skcin7
03-08-2010, 10:15 PM
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. :)

Fou-Lu
03-08-2010, 10:22 PM
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:


$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.


Also, I need to go do some work, so I'll be back soon.

skcin7
03-08-2010, 10:31 PM
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.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum