By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Search DB by more than one word

P: n/a
Hello everyone,
Right now I have a simple PHP page setup that takes whatever was
entered into txtsearch on the previous page, it searches my DB by that
word and brings back the whole record based on the word.

So if I enter: ALPHA
it will bring back this record: the alpha dog was there

HOWEVER if I enter: ALPHA DOG or alpha dog
(so if I enter more than one word) it does not find any records. How
can I fix this so that if I enter ALPHA DOG it will find the record:
the alpha dog was there

Here is my code:
==============
<?php
$host="localhost";
$user="root";
$pass="";
$db="productiondb";
$con = mysql_connect($host, $user, $pass);

$Search = $_POST["txtsearch"];

if (!$con)
{
die('Unable to connect: ' . mysql_error());
}
mysql_select_db($db, $con) or die('Unable to connect: ' .
mysql_error());
$sql = "SELECT * FROM IT WHERE itcomments LIKE '$Search%' ";
$rs = mysql_query($sql,$con);

while($row=mysql_fetch_object($rs)){

print "<div class=\"message\">";
print " <h3 class=\"red\">" . $row->itdate . "</h3>";
print " <h5 class=\"red\">" . $row->itname . "</h5>";
print " <h4 class=\"red\">" . $row->itcomments ."</h4>";
print "############### END OF RECORD ###############";
print "</div>";
}
?>

thanks for any help
Jan 7 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
pa*****@excite.com wrote:
Hello everyone,
Right now I have a simple PHP page setup that takes whatever was
entered into txtsearch on the previous page, it searches my DB by that
word and brings back the whole record based on the word.

So if I enter: ALPHA
it will bring back this record: the alpha dog was there

HOWEVER if I enter: ALPHA DOG or alpha dog
(so if I enter more than one word) it does not find any records. How
can I fix this so that if I enter ALPHA DOG it will find the record:
the alpha dog was there

Here is my code:
==============
<?php
$host="localhost";
$user="root";
$pass="";
$db="productiondb";
$con = mysql_connect($host, $user, $pass);

$Search = $_POST["txtsearch"];

if (!$con)
{
die('Unable to connect: ' . mysql_error());
}
mysql_select_db($db, $con) or die('Unable to connect: ' .
mysql_error());
$sql = "SELECT * FROM IT WHERE itcomments LIKE '$Search%' ";
$rs = mysql_query($sql,$con);

while($row=mysql_fetch_object($rs)){

print "<div class=\"message\">";
print " <h3 class=\"red\">" . $row->itdate . "</h3>";
print " <h5 class=\"red\">" . $row->itname . "</h5>";
print " <h4 class=\"red\">" . $row->itcomments ."</h4>";
print "############### END OF RECORD ###############";
print "</div>";
}
?>

thanks for any help
I'm amazed!
Looking at your code I would not expect searching on "alpha" to bring back
"the alpha dog was there", since this does not begin with the word "alpha".
Jan 7 '08 #2

P: n/a
On Jan 7, 12:33 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
paul...@excite.com wrote:
Hello everyone,
Right now I have a simple PHP page setup that takes whatever was
entered into txtsearch on the previous page, it searches my DB by that
word and brings back the whole record based on the word.
So if I enter: ALPHA
it will bring back this record: the alpha dog was there
HOWEVER if I enter: ALPHA DOG or alpha dog
(so if I enter more than one word) it does not find any records. How
can I fix this so that if I enter ALPHA DOG it will find the record:
the alpha dog was there
Here is my code:
==============
<?php
$host="localhost";
$user="root";
$pass="";
$db="productiondb";
$con = mysql_connect($host, $user, $pass);
$Search = $_POST["txtsearch"];
if (!$con)
{
die('Unable to connect: ' . mysql_error());
}
mysql_select_db($db, $con) or die('Unable to connect: ' .
mysql_error());
$sql = "SELECT * FROM IT WHERE itcomments LIKE '$Search%' ";
$rs = mysql_query($sql,$con);
while($row=mysql_fetch_object($rs)){
print "<div class=\"message\">";
print " <h3 class=\"red\">" . $row->itdate . "</h3>";
print " <h5 class=\"red\">" . $row->itname . "</h5>";
print " <h4 class=\"red\">" . $row->itcomments ."</h4>";
print "############### END OF RECORD ###############";
print "</div>";
}
?>
thanks for any help

I'm amazed!
Looking at your code I would not expect searching on "alpha" to bring back
"the alpha dog was there", since this does not begin with the word "alpha".
Hmm, you are right, I didnt notice that, that was just an example,
not an actual query......hmm, yea if it does not begin with whatever I
search for I do not get a result?...
Jan 7 '08 #3

P: n/a
On Jan 7, 12:39 pm, paul...@excite.com wrote:
On Jan 7, 12:33 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
paul...@excite.com wrote:
Hello everyone,
Right now I have a simple PHP page setup that takes whatever was
entered into txtsearch on the previous page, it searches my DB by that
word and brings back the whole record based on the word.
So if I enter: ALPHA
it will bring back this record: the alpha dog was there
HOWEVER if I enter: ALPHA DOG or alpha dog
(so if I enter more than one word) it does not find any records. How
can I fix this so that if I enter ALPHA DOG it will find the record:
the alpha dog was there
Here is my code:
==============
<?php
$host="localhost";
$user="root";
$pass="";
$db="productiondb";
$con = mysql_connect($host, $user, $pass);
$Search = $_POST["txtsearch"];
if (!$con)
{
die('Unable to connect: ' . mysql_error());
}
mysql_select_db($db, $con) or die('Unable to connect: ' .
mysql_error());
$sql = "SELECT * FROM IT WHERE itcomments LIKE '$Search%' ";
$rs = mysql_query($sql,$con);
while($row=mysql_fetch_object($rs)){
print "<div class=\"message\">";
print " <h3 class=\"red\">" . $row->itdate . "</h3>";
print " <h5 class=\"red\">" . $row->itname . "</h5>";
print " <h4 class=\"red\">" . $row->itcomments ."</h4>";
print "############### END OF RECORD ###############";
print "</div>";
}
>?>
thanks for any help
I'm amazed!
Looking at your code I would not expect searching on "alpha" to bring back
"the alpha dog was there", since this does not begin with the word "alpha".

Hmm, you are right, I didnt notice that, that was just an example,
not an actual query......hmm, yea if it does not begin with whatever I
search for I do not get a result?...
I think I fixed this by going from this:
$sql = "SELECT * FROM EDITORIAL WHERE editorialcomments LIKE '$Search
%' ";

to this:
$sql = "SELECT * FROM EDITORIAL WHERE editorialcomments LIKE '%$Search
%' ";

And I think this fixed my initial problem, question
Jan 7 '08 #4

P: n/a
pa*****@excite.com wrote:
On Jan 7, 12:39 pm, paul...@excite.com wrote:
>On Jan 7, 12:33 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
>>paul...@excite.com wrote:
<...>
I think I fixed this by going from this:
$sql = "SELECT * FROM EDITORIAL WHERE editorialcomments LIKE '$Search
%' ";

to this:
$sql = "SELECT * FROM EDITORIAL WHERE editorialcomments LIKE '%$Search
%' ";

And I think this fixed my initial problem, question
Yep. You got it. I would only add that, for performance reasons, I'd
recommend that the editorialcomments column is being indexed. These
"fuzzy" searches, that use the % in the clause tend to be much slower.
As well as the fact that text (varchar()) columns are slower even if an
explicit clause were used.

--

Gene Kelley
Senior Open Source Software Engineer
Advanced Design Solutions Team
Network Solutions (MonsterCommerce)
Swansea, Illinois, USA
Eugene.Kelley_AT_networksolutions_DOT_com
Jan 12 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.