...

View Full Version : Does DB have to be full text for this kind of search query?



Majd-GFX
10-24-2004, 04:35 PM
hi, im making a search script, the code i'm using is:



<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" media="screen" title="Stylesheet" href="styles.css">
</head>

<body>
<!--START OF DIV CODE-->

<div id="container">

<div id="header">banner</div>

<div id="menu"><?php include('menu.html'); ?></div>

<div id="content-container">

<div id="content">

<!-- ALL CONTENT -->

<?



echo"

<table border=\"1\" cellpadding=\"0\" cellspacing=\"0\" style=\"border-collapse: collapse\" bordercolor=\"#111111\" width=\"100%\" id=\"AutoNumber1\">

<tr>

<td class=\"headers\" width=\"16%\" align=\"center\">File #</td>

<td class=\"headers\" width=\"16%\" align=\"center\">Type Of Business</td>

<td class=\"headers\" width=\"17%\" align=\"center\">Location</td>

<td class=\"headers\" width=\"17%\" align=\"center\">Gross Revenue</td>

<td class=\"headers\" width=\"17%\" align=\"center\">Business Price

</font></td>

<td width=\"17%\" align=\"center\">Terms</td>

</tr>";


//CONNECT TO MYSQL AND DATABASE
$conn=@mysql_connect("localhost", "USERNAME", "PASSWORD") or die ("Err:Conn");
mysql_select_db("majdkgf_capital1") or die("Err:Db");

//CONVERT INFORMATION INTO VARIABLES
$category=$_POST['category'];
$keyword=$_POST['keyword'];

//FORM THE QUERY
$sql="select * from businesses_for_sale where '$category' LIKE '%$keyword%'";
$result = mysql_query($sql,$conn);

//TEST IF THERE IS AN ERROR WITH QUERY
if(!$result) die("query ".$query." failed with error ".mysql_error());


$number=mysql_num_rows(mysql_query("$sql"));
echo "Search has returned $number results ";
echo "In the category: $category";

while ($row = mysql_fetch_array($result))
{
$filenu=$row['filenu'];
$Business=$row['Business'];
$Location=$row['Location'];
$Gross_Sales=$row['Gross_Sales'];
$Business_Price=$row['Business_Price'];
$Terms=$row['Terms'];
echo ("
<tr>
<td class=\"result\" width=\"16%\" align=\"center\"><a href=\"http://www.majd-gfx.com/capital-one/show_full_business.php?full_business_file_number=$filenu\">$filenu</a></td>
<td class=\"result\" width=\"16%\" align=\"center\">$Business</td>
<td class=\"result\" width=\"17%\" align=\"center\">$Location</td>
<td class=\"result\" width=\"17%\" align=\"center\">$Gross_Sales</td>
<td class=\"result\" width=\"17%\" align=\"center\">$Business_Price</td>
<td class=\"result\" width=\"17%\" align=\"center\">$Terms</td>
</tr>
");
}

echo"</table> "; ?>

<!-- END OF CONTENT -->
</div>
</div>
</div>
<!--END OF DIV CODE-->
</body>
</html>


now my question is, on my db, do all the fields have to be full text?

if not, this query is not showing any results even though im searching for exact match.....

fci
10-25-2004, 06:11 AM
I think you would you want to change this:

$sql="select * from businesses_for_sale where '$category' LIKE '%$keyword%'";
to this:

$sql="select * from businesses_for_sale where `$category` LIKE '%$keyword%'";
//note: backticks `

I would recommend using mysql_real_escape_string (http://www.php.net/mysql_real_escape_string) on the posted($_POST) variables.. and since you're doing a LIKE search you will also need to escape out of the _ and %.
This is how you can escape out of the LIKE search string, I posted it on php.net but it never went on there.

preg_replace('/(%|_)/sim','\\\\$1',$str);

raf
10-25-2004, 09:34 AM
good advice. (i also made a few posts to php.net but non of them were added)
personally, i'd use


$sql="select var1, var2, var3 from businesses_for_sale where `" . $category . "` LIKE '%" . $keyword . "%'";

where var1 etc are the actual columnnames that you need
+ instead of a replace on the wildcards, i'd simply do a regex and return an error if the input contained wildcards. If your tables get bigger, then these searches can realy slow your db down.

about the fulltext: fulltextsearches and keywordsearches are something completely different. You have a keywordsearch so you don't need fulltext-indexes on none of these columns.

Majd-GFX
10-26-2004, 02:40 AM
im REALLY sorry but im afraid i don't understand what you guys are talkin about, maybe a reference or smthn that would explain it a bit simpler? thank you

EDIT: when i replace the ' with `, i get a mysql error, but when it's ' i just get 0 results....

raf
10-26-2004, 07:57 AM
im REALLY sorry but im afraid i don't understand what you guys are talkin about, maybe a reference or smthn that would explain it a bit simpler? thank you
What exactly don't you understand?


EDIT: when i replace the ' with `, i get a mysql error, but when it's ' i just get 0 results....
I simply don't believe that.

Majd-GFX
10-26-2004, 09:09 PM
ok this is what i get when i put ` instead of ' in the query

query failed with error Unknown column '%Auto Body Shop%' in 'where clause'

now why is it taking $keyword as $category?

NOTE: when i switch the two variables so that $keyword is before $category, i get same error statement.

However, when they are ' and not `, i get (and i echoed it this way)

Search has returned 0 results In the category: Business with the keyword: Auto Body Shop
select * from businesses_for_sale where 'Business' LIKE '%Auto Body Shop%'

NOTE: second part is the query used.


thank you

raf
10-26-2004, 10:04 PM
post the exact code that you use. the query i posted will most certainly work. surrounding a column or tablename with backtick will never cause an error.

I quickly created a table businesses_for_sale with column 'Business' and added a few records. One with value 'Auto Body Shop'

Guess what:
select * from businesses_for_sale where `Business` LIKE '%Auto Body Shop%'
==> returns 1 record
select * from businesses_for_sale where Business LIKE '%Auto Body Shop%'
==> returns 1 record
select * from businesses_for_sale where 'Business' LIKE '%Auto Body Shop%'
==> returns 0 records.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum