[php]Selecting from mysql when partial match
I'm working on a search function. And the script I have works perfectly. Except, it only return results when the search string is fully matched. So if I search for search string it will only return a result when there actually is a column in the table with the row search string.
Is there a way to let it also return a result when a partial match is found? So if I for example search for search string and there's a column with a row containing this is a search string it will still return it.
Here's my search script:
searchform.php:
PHP Code:
<? include("header.php"); ?>
<form action="search.php" method="get">
<input type="text" name="search"/>
<input type="submit" name="submit" value="search" />
<br/>
Search in:
<br/>
Subject<input type="radio" name="searchin" value="subject'" checked="checked">
Message<input type="radio" name="searchin" value="message'">
Both<input type="radio" name="searchin" value="both'">
</form>
<? include("footer.php"); ?>
search.php:
PHP Code:
<?php
include("header.php");
function replace($str) {
$pattern = array (
'/%24/',
'/%26/',
'/%2B/',
'/%2C/',
'/%2F/',
'/%3A/',
'/%3B/',
'/%3D/',
'/%3F/',
'/%40/',
'/%20/',
'/\+/'
);
$replace = array (
'\$',
'\&',
'\+',
',',
'/\/',
'\:',
'\;',
'\=',
'\?',
'\@',
' ',
' '
);
$str = preg_replace($pattern, $replace, $str);
return $str;
}
//Single out the search string.
$searchuri = $_SERVER['REQUEST_URI'];
$searchuri = str_replace($_SERVER['SCRIPT_NAME'], '',$searchuri);
// Search the url for the search term.
preg_match("/\=(.*)\&/", substr($searchuri,1), $smatches);
preg_match("/\=(.*)\&submit/", $smatches[0], $search);
// Search the url for where to search in.
preg_match("/searchin\=(.*)\%/", substr($searchuri,1), $searchin);
$search = stripslashes(replace($search[1]));
//Check if every field has been filled in.
if(empty($search) || empty($searchin[1]) || strlen($search) < 3) {
echo "
<div class='mainbody' align='left'>
<div class='maintext' align='left'>
Please enter a searchterm longer than 3 characters.
</div>
<div class='textend' style='margin-left:34px;'></div>
<div class='mainbottom'></div>
</div>
<div align='center'>
<a href='http://validator.w3.org/check?uri=referer'><img
src='http://www.w3.org/Icons/valid-xhtml10'
alt='Valid XHTML 1.0 Transitional' height='31' width='88' border='0' /></a>
<a href='http://jigsaw.w3.org/css-validator/'>
<img style='border:0;width:88px;height:31px'
src='http://jigsaw.w3.org/css-validator/images/vcss'
alt='Valid CSS!' />
</a>
</div>";
} else {
//Make search query.
if($searchin[1] == 'both') {
$searchquery = "SELECT message,subject FROM bcontent WHERE message = '$search' OR subject = '$search' LIMIT 20";
} else if($searchin[1] == 'subject') {
$searchquery = "SELECT message,subject FROM bcontent WHERE subject = '$search' LIMIT 20";
} else if($searchin[1] == 'message') {
$searchquery = "SELECT message,subject FROM bcontent WHERE message = '$search' LIMIT 20";
} else {
//Make sure no one fools around with the script.
echo "
<div class='mainbody' align='left'>
<div class='maintext' align='left'>
Invalid <i>searchin</i> specified.
</div>
<div class='textend' style='margin-left:34px;'></div>
<div class='mainbottom'></div>
</div>
<div align='center'>
<a href='http://validator.w3.org/check?uri=referer'><img
src='http://www.w3.org/Icons/valid-xhtml10'
alt='Valid XHTML 1.0 Transitional' height='31' width='88' border='0' /></a>
<a href='http://jigsaw.w3.org/css-validator/'>
<img style='border:0;width:88px;height:31px'
src='http://jigsaw.w3.org/css-validator/images/vcss'
alt='Valid CSS!' />
</a>
</div>";
}
$searchresult = mysql_query($searchquery) or die(mysql_error());
//If no rows were found, say so.
if(mysql_num_rows($searchresult) < 1) {
echo "
<div class='mainbody' align='left'>
<div class='maintext' align='left'>
No results were returned with the searchterm <i>$search</i>.
</div>
<div class='textend' style='margin-left:34px;'></div>
<div class='mainbottom'></div>
</div>
<div align='center'>
<a href='http://validator.w3.org/check?uri=referer'><img
src='http://www.w3.org/Icons/valid-xhtml10'
alt='Valid XHTML 1.0 Transitional' height='31' width='88' border='0' /></a>
<a href='http://jigsaw.w3.org/css-validator/'>
<img style='border:0;width:88px;height:31px'
src='http://jigsaw.w3.org/css-validator/images/vcss'
alt='Valid CSS!' />
</a>
</div>";
} else {
//Rest of script to display search query.
echo "
<div class='mainbody' align='left'>
<div class='maintext' align='left'>
Something was found!
</div>
<div class='textend' style='margin-left:34px;'></div>
<div class='mainbottom'></div>
</div>
<div align='center'>
<a href='http://validator.w3.org/check?uri=referer'><img
src='http://www.w3.org/Icons/valid-xhtml10'
alt='Valid XHTML 1.0 Transitional' height='31' width='88' border='0' /></a>
<a href='http://jigsaw.w3.org/css-validator/'>
<img style='border:0;width:88px;height:31px'
src='http://jigsaw.w3.org/css-validator/images/vcss'
alt='Valid CSS!' />
</a>
</div>";
}
}
?>
Re: [php]Selecting from mysql when partial match
SELECT * FROM table WHERE message LIKE '%text%'
Finds:
text
I'm text
I like text very much
ImTextBla
etc.
every string that contains "text"
Re: [php]Selecting from mysql when partial match
Uh "like" together with an unsecure language as php is kinda dangerous. Wasn
Re: [php]Selecting from mysql when partial match
LIKE is a SQL command, not PHP. Also, you can escape the input string with PHP, and you got rid of the injections.
Re: [php]Selecting from mysql when partial match
Ah thanks a lot guys. :smile:
Re: [php]Selecting from mysql when partial match
Quote:
Originally Posted by
Daevius
LIKE is a SQL command, not PHP. Also, you can escape the input string with PHP, and you got rid of the injections.
Aye, with mysql_real_escape_string.
MySQL also has a really nifty feature since 5.1 called MATCH AGAINST in NATURAL LANGUAGE MODE (well, match against is older but the natural language mode option has been added then). This is the way to do text searches. See the documentation on the subject.
For one thing, it's about a gazillion times faster than LIKE '%[match]%' and it has the option to use query expansion meaning if you search for banana and there's an item called apple, fruit and banana and in the banana item the text 'fruit' exists it can also find 'fruit' and 'apple' - which sounds useless in this case but you'll often want people to also find items related to what they are searching for.
Fai, a PHP/AJAX chatbot I wrote about a year ago also uses this with sometimes great results.
Slightly offtopic: why the hell are you fooling around with preg_match on the URL for? Any decent webserver will give you those items directly in the $_GET array - which isn't only faster, it's also far less code and much better readable.
