search in the database

Hi!

I'm using a PHP code to do a search in the database.
I keep getting an error (undifined $wordcount) when I use more than one word separated by a space.
Does anyone knows why this happen?

Thanks in advanced
António



<?
// Search Tutorial Code
// Arutha.co.uk

// Standard Connection Data.
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "tutorials";
$dbac = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db ($db_name) or die ("Cannot connect to database");

if (!$_POST['search']){
// Breaking out of php to display the form.
?>
<form name="Search Tutorial" method="post" action="">
Search Words:<br>
<label>
<input name="swords" type="text" size="30" maxlength="30">
</label>
<br>
<label>
<input name="search" type="submit" id="search" value="Search">
</label>
</form>
<?
}else{
// Adding slashes and changing tags so that people trying to take advantage of the system can't
$searchwords = addslashes(htmlspecialchars($_POST['swords']));
// Checking the length of string to make sure its more than 3 characters long
if (strlen($searchwords) < 3){
echo "The word you have inputted is too short, please enter another one.";
}else{
$words = explode(' ',$searchwords);
$totalwords = count($words);
$i = 0;
$searchstring = "";
// Now we are looping to get the search string, however we could use a for loop.
// But i think while loops are much easier to teach with/edit
while ($i != $totalwords){
if ($i != 0 and $i != $wordcount){
$searchstring .= " and ";;
}
$searchstring .= "description LIKE '%$words[$i]%' or name LIKE '%$words[$i]%'";
// Incrementing the value
$i = $i + 1;
}
// Now we are gong to execute the query with the search string we have just created.
$query = mysql_query("SELECT DISTINCT * FROM Search where $searchstring");
// Want to check that we got something
if (mysql_num_rows($query) == 0){
echo "No results where found sorry ";
}else{
while ($fet = mysql_fetch_array($query)){
echo "<p>Name: {$fet['name']}<br />
Description: {$fet['description']}</p>";
}// End While
}// End Else
}// End Else
}// End Else


?>

 

Shouldn't

if ($i != 0 and $i != $wordcount){

Code (markup):

be

if ($i != 0 and $i != $totalwords){

Code (markup):

 

why not use "MATCH" instead of looping for every words... let's try to rewrite your code. But first of all you need to make FULL TEXT the field you want to include in the search. This code will act like Search engine do, first search for the exact word, then split the keyword string if more than 1 word and search each word.

<?
// Search Tutorial Code
// Arutha.co.uk

// Standard Connection Data.
$db_host = "localhost";
$db_user = "root";
$db_pass = "";
$db_name = "tutorials";
$dbac = mysql_connect($db_host,$db_user,$db_pass);
mysql_select_db ($db_name) or die ("Cannot connect to database");

if (!$_POST['search']){
// Breaking out of php to display the form.
?>
<form name="Search Tutorial" method="post" action="">
Search Words:<br>
<label>
<input name="swords" type="text" size="30" maxlength="30">
</label>
<br>
<label>
<input name="search" type="submit" id="search" value="Search">
</label>
</form>
<?
}else{
// Adding slashes and changing tags so that people trying to take advantage of the system can't
$searchwords = addslashes(htmlspecialchars($_POST['swords']));
// Checking the length of string to make sure its more than 3 characters long
if (strlen($searchwords) < 3){
echo "The word you have inputted is too short, please enter another one.";
}else{
$sql = $sql="SELECT * FROM Search WHERE MATCH (description,name) AGAINST ('%$searchwords%')";
$rs=mysql_query($sql) or die(mysql_error());

if(mysql_numrows($rs) > 0){
echo mysql_numrows($rs). " entries found for this search";

do {
echo "<p>Name: $rows['name'] <br />
Description: $rows['description'] </p>"; 
}while($rows=mysql_fetch_array($rs));
}else{
echo "No match found ";
}
}
}
?>

Code (markup):

 

Yeah $wordcount isn't defined any where?

 

FULL TEXT SEARCH is the easiest one. but not good enough for small database.

 

Thank you all for the fast replies!
Since my database is small, I will not use FULL TEXT SEARCH.
I try the envoy solution, and it works fine now!

But I'm having dificulty in display the resultes in pages. I have divided results by 20 products per page.It makes the correct division and display the number of pages, but when I try to acess the other pages it give the following error:

Notice: Undefined index: swords in c:\programas\easyphp1-8\www\medical\pesquisar.php on line 206
A palavra inserida é pequena insira outra.

Notice: Undefined variable: searchstring in c:\programas\easyphp1-8\www\medical\pesquisar.php on line 285
Error, query failed

I think that it doesn't take the swords for the other pages, but I can't seen how can I do that.

António

----------------------------------------------------------
<?php

mysql_select_db($database_medical, $medical);

$rowsPerPage = 20;

$pageNum = 1;

if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

$offset = ($pageNum - 1) * $rowsPerPage;

$searchwords = addslashes(htmlspecialchars($_POST['swords']));

// Checking the length of string to make sure its more than 3 characters long
if (strlen($searchwords) < 3){
echo "A palavra inserida é pequena insira outra.";
}else{
$words = explode(' ',$searchwords);
$totalwords = count($words);
$i = 0;
$searchstring = "";
// Now we are looping to get the search string, however we could use a for loop.
// But i think while loops are much easier to teach with/edit
while ($i != $totalwords){
if ($i != 0 and $i != $totalwords){
$searchstring .= " and ";
}
$searchstring .= "produto LIKE '%$words[$i]%'";
// Incrementing the value
$i = $i + 1;
}
// Now we are gong to execute the query with the search string we have just created.
$query = mysql_query("SELECT DISTINCT * FROM produto where $searchstring LIMIT $offset, $rowsPerPage");

$num=mysql_num_rows($query);
//$num2=mysql_num_rows($query2);
if ($num == 0){
echo "Nenhum resultado encontrado";
}else{
print "<table width=\"100%\"><tr>";
$contador=0;
while ($campos = mysql_fetch_array($query)){
$id_cat=$campos['id_titulo'];
$tumbs=$campos['image_home'];
$result1 = mysql_query("select * from images_home where id = $tumbs");
$campos1 = mysql_fetch_array($result1);
$result2 = mysql_query("select * from titulos where id = $id_cat");
$campos2 = mysql_fetch_array($result2);
if ($contador==2) print "<tr>";
print "<td>";
print"<a href='detalhe_prod.php?ler=$campos[id]&cod_cat=$campos2[id]'>
<img src='small/$campos1[image].jpg' border=1 width=60></a><br><br>";
print "</td>";



print "<td>";
if($campos['promocao']==1){
print"<img src='logos/promo.gif'><br>";
}
print "<strong>$campos[produto]</strong>";
print "<br>";
$lower=mb_strtolower($campos['sub_nome']);

if($lower <> ""){
print"<font color='#CC0000'>$lower</font><br>";
}

if($campos['novidades']==1){
print"<img src='logos/simbolonovo.jpg'>";
}

print "</td>";
print "<td>";
if ($contador==2) { print "</tr>"; $contador=0; }
++$contador;

}// End While
print "</td>";
print "</tr></table>";
}// End Else
}// End Else


print"</td>";
print"</tr>";
print "<tr>";
print "<td height='19' colspan='2' align='center' valign='top' class='style4'><br />";


$query = "SELECT COUNT(*) AS numrows FROM produto where $searchstring";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];

$maxPage = ceil($numrows/$rowsPerPage);

$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"$self?page=$page \">$page</a> ";
}
}


if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"$self?page=$page\">[Anterior]</a> ";

$first = " <a href=\"$self?page=1\">[Página inicial]</a> ";
}
else
{
$prev = '&nbsp;'; // we're on page one, don't print previous link
$first = '&nbsp;'; // nor the first page link
}

if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Seguinte]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Página final]</a> ";
}
else
{
$next = '&nbsp;'; // we're on the last page, don't print next link
$last = '&nbsp;'; // nor the last page link
}

// print the navigation link
echo $first . $prev . $nav . $next . $last;

 

I may have to disagree with this comment. As long as you have 3 entries, the FULLTEXT search can very well be used and properly.