Query all fields

urbanmac

Hello all,

I'm doing a simple search for a staff system. I want the person searching to get results by entering or part entering first name, surname, or telephone extension and the results to be returned.

So far I have it workign but only on entering or part entering a surname, I cant seem to get the query right for search across all three fields.

my code is:

<?php

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

// rows to return - set to 99999 for testing
$limit=99999; 


// connect to MySQL
mysql_connect("localhost","DBADMIN","DBPASS");

// select database
mysql_select_db("staff") or die("Unable to select database");

// build SQL Query  

$query = "SELECT * FROM staffsearch WHERE surname LIKE \"%$trimmed%\" 
ORDER BY surname";

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

if ($numrows == 0)
  {

// if no results
echo "<p><li class='LSRow'>&nbsp;Sorry, your search:</li><li class='LSRow'>&nbsp;<strong>&quot;" . $trimmed . "&quot;</strong></li><li class='LSRow'>&nbsp;Returned 0 results</li></p>";
}

// determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

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



// display results
  while ($row= mysql_fetch_array($result)) {
  $first_name = $row["first_name"];
  $surname = $row["surname"];
  $tel = $row["tel"];


 echo "<li class='LSRow'><a href='http://intranet/staffsearch/search.lasso?=$surname'>$first_name $surname</a> Ext:$tel</li>" ;
  $count++ ;
  }

$currPage = (($s/$limit) + 1); ?>

thanks for any help

harmor

Change
$query = "SELECT * FROM staffsearch WHERE surname LIKE \"%$trimmed%\"

To
$query = "SELECT * FROM staffsearch WHERE surname LIKE \"%$trimmed%\" OR first_name LIKE \"%trimmed%\"

Just repeat the OR field_name part for the fields you want it to search

I'm not 100% positive it'll work since I never really did search scripts

urbanmac

I'd expect that to work as well, I tried with an OR but its still refusing to search on the first_name.

If I want to find Jon Smith, I type in Smith and all the Smith's appear (perfect) but if I type in Jon, all i get are Jones or other surnames with jon in I doubt get Jon Smith.