Searching Database Using Like

gilbertsavier · July 31st, 2009, 06:43 AM

Hi,
I am trying to query my database for specific words in variables.

I have one database titled pranks with a column called prankentry and tags

Another database holds an ID number and an entrytext column.

I pass the ID number through the URL to /pranks/index.php?id=26. On index.php i am running a query to try and find similar items in the pranksentry row.

Here's my code:
$id = $_GET['id']; //GET ID
$query = "SELECT entrytext FROM hmr WHERE id=$id"; //GET POST
$result = mysql_query($query);

while($row = mysql_fetch_array($result))
{
$text = $row['entrytext']; //CHANGE VARIABLE

}
$sql = "SELECT * FROM pranks WHERE pranksentry LIKE '%$text%' ORDER BY id DESC"; //GET PRANKS SIMILAR to TEXT
$result5 = mysql_query($sql);

while($row = mysql_fetch_array($result5))
{
$prank = $row['prankentry'];
echo $prank; //OUTPUT Prank
}
It's very strange. By setting $text = 'test' and putting a prank into my that table with the text, 'testing to do this testing to do that'. The code above displays that line.
However, if i set $text = "he talks too much, and never shuts up." and have a prank in my database with the text, 'shuts', it does not show the results i would like.

Old Pedant · July 31st, 2009, 03:08 PM

Ummm...if you really wanted to

Quote:

set $text = "he talks too much, and never shuts up." and have a prank in my database with the text, 'shuts'

and find a match, then you have your LIKE backwards.

YOu would have to do:

Code:

SELECT * FROM pranks WHERE '$text' LIKE CONCAT('%',pranksentry,'%') ORDER BY id 
DESC

The wildcards only work ONE WAY. Same as when you use a "ls" command to list a directory.

If you do

Code:

ls *foo*

you'll get files named "food" and "afoolish" and "xyfoo".

But if you do

Code:

ls *food*

you won't get "afoolyish" or "xyfoo".

Think of it this way: a LIKE b means "search for b inside of a" with the special provision that if b contains a % character then the % matches ANY characters in a. When you do

Code:

WHERE 'talks' LIKE '%he talks too much%'

then the %'s don't really matter, because obviously b is longer than a and so "search for b inside a" could not possibly work.