[SOLVED] PHP Search engine error

[Hybride]

Normally I don't have problems with scripting, but for the life of me, I can't figure this out. Am writing a PHP/MySQL search engine script, and I have two problems with it so far.

1) It shows up on the index/front page without searching for anything (the keywords all show up). If I try to put an (if(isset($_POST['submit'])) code at the beginning of it, the whole script does not work.

2) Am trying to search two variables, but if I search one, it won't search the other.

 

Any help would be greatly appreciated; I even wouldn't care if it's not searching the two variables, I just don't want the keywords to show up on the index.

 

<div id="searchbox" class="searchbox">
<form name="form" action="<?php echo $PHP_SELF; ?>" method="get">
  <input type="text" name="q" />
  <input type="submit" name="submit" value="Search" />

<? 	require('./includes/dbconnect.php');

// Get the search variable from URL
	$var = @$_GET['q'] ;
	$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
	$limit=5; 

// Build SQL Query  
$query = "SELECT DISTINCT name,label FROM manual,automatic WHERE name || label LIKE '%$trimmed%' ORDER BY name DESC"; 
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

if ($numrows == 0){ //Start with the error first
  echo "<br /><b>Sorry,</b> your search: "" . $trimmed . "" returned 0 results.<br />";
} else { // next determine if 's' has been passed to script, if not use 0, start searching
  if (empty($s)) {
  	$s=0;
}  


// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query" . mysql_error());

// begin to show results set
	$a = $s + ($limit);
	if ($a > $numrows) { 
		$a = $numrows ; 
		}
	$b = $s + 1;
  	echo "<br /><b>Results: Showing results $b of $numrows.</b><br />";
	$count = 1 + $s ;
// now you can display the results returned
  while ($row= mysql_fetch_array($result, MYSQL_ASSOC)) {	
	  $title = $row["name"];
	  print  "   <a href='./data.php?name={$row['name']}' \>" . $title . "</a><br />" ;
 $count++; 
 }
 }
?> 
</form>
</div>

[hitman6003]

Change your query:

 

$query = "(SELECT DISTINCT name, label " .
			 "FROM manual WHERE name LIKE '%$trimmed%' OR label LIKE '%$trimmed%') " .
			 "  UNION  " .
			 "(SELECT DISTINCT name, label " .
			 "FROM automatic WHERE name LIKE '%$trimmed%' OR label LIKE '%$trimmed%') " .
			 "ORDER BY name DESC ";

 

If you want better results, use a fulltext index (assuming you are using myisam), then use a MATCH ... AGAINST() query.

[btherl]

1) It shows up on the index/front page without searching for anything (the keywords all show up). If I try to put an (if(isset($_POST['submit'])) code at the beginning of it, the whole script does not work.

 

Your form is using get, so you should check for $_GET['submit']

[Hybride]

I got both codes to work with slight mods (thank you to both!), but the last problem am having is that it only prints the "label" if searched, but not the "name" (ie: if I search the label name, it will echo if it's in the database; if am searching for the name, it won't echo/print anything if it is in the database. Basically: search label -> print yes/error; search name -> blank).

 

Any ideas? Here's the updated. Thanks again!

<? if(isset($_GET['submit'])) {
// Get the search variable from URL
	$var = @$_GET['q'] ;
	$trimmed = trim($var); //trim whitespace from the stored variable
// rows to return
	$limit=5; 

// Build SQL Query  
$query = "SELECT DISTINCT name,label FROM manual WHERE (name LIKE '%$trimmed%' OR label LIKE '%$trimmed%') ORDER BY name DESC ";
 $numresults=mysql_query($query);
 $numrows=mysql_num_rows($numresults);

if ($numrows == 0){ //Start with the error first
  echo "<br /><b>Sorry,</b> your search: "" . $trimmed . "" returned 0 results.<br />";
  // next determine if 's' has been passed to script, if not use 0, start searching
 }
 if (empty($s)) {
  	$s=0;
}  


// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query" . mysql_error());

// begin to show results set
	$a = $s + ($limit);
	if ($a > $numrows) { 
		$a = $numrows ; 
		}
	$b = $s + 1;
  	echo "<br /><b>Results: Showing results $b of $numrows.</b><br />";
	$count = 1 + $s ;
// now you can display the results returned
  while ($row= mysql_fetch_array($result, MYSQL_ASSOC)) {	
	  $title = $row["name"];
	  print  "   <a href='./data.php?name={$row['name']}' \>" . $title . "</a><br />" ;
 $count++; 
 }

}
?>

[btherl]

Is it anything to do with case?  You can use ILIKE for case-insensitive matching.

[Hybride]

I figured it out - it had nothing to do with case-sensitivity. I added a hidden input in the form ( <input type="hidden" name="submit" value="yes" /> ) and it worked.

 

Thanks for all your help!