Wrox Programmer Forums
Go Back   Wrox Programmer Forums > PHP/MySQL > MySQL
MySQL General discussion about the MySQL database.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old July 31st, 2009, 06:43 AM
Authorized User
Join Date: Jun 2009
Posts: 12
Thanks: 0
Thanked 1 Time in 1 Post
Default Searching Database Using Like

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.
Thanks & regards
[url "http://www.mioot.com"]Live Chat Software[/url] By miOOt
Old July 31st, 2009, 03:08 PM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Ummm...if you really wanted to
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:
SELECT * FROM pranks WHERE '$text' LIKE CONCAT('%',pranksentry,'%') ORDER BY id 
The wildcards only work ONE WAY. Same as when you use a "ls" command to list a directory.

If you do
ls *foo*
you'll get files named "food" and "afoolish" and "xyfoo".

But if you do
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
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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
searching a database mark jonas MySQL 13 January 19th, 2007 10:01 AM
Searching through a database d5t ASP.NET 1.0 and 1.1 Basics 6 October 27th, 2006 07:10 AM
help with Database searching europhreak Classic ASP Basics 1 February 8th, 2006 07:30 AM
Searching the database...HELP! Zedman BOOK: Beginning ASP 3.0 6 September 30th, 2004 09:52 AM
searching a database shabazzk Classic ASP Databases 2 March 10th, 2004 06:56 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.