How to search the mysql database using php

[impin]

thjis is my search form...

<form name="form" action="search.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>

this is my search.php

<?php

include('lock.php');
include("config.php");

  

  $var = @$_GET['q'] ;
  $trimmed = trim($var); 
  




// rows to return




if ($trimmed == "")
  {
  echo "<p>Please enter a search...</p>";
  exit;
  }


/*if (!isset($var))
  {
  echo "<p>We dont seem to have a search parameter!</p>";
  exit;
  }*/

$query = "select * from candidate where cname like \"%$trimmed%\" ||  skills like \"%$trimmed%\" ||   exp like \"%$trimmed%\" || indus like \"%$trimmed%\" || qual like \"%$trimmed%\" ORDER BY cid ";
   
$result=mysql_query($query);
$num=mysql_numrows($result);



if ($num == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";

 }


echo "<p>You searched for: &quot;" . $var . "&quot;</p>";
echo "Results";
print "<table width='100%' border='1'>";
print "<tr><th align='center'>Name</th><th align='center'>Email</th><th align='center'>Qualification</th><th align='center'>Skills</th><th align='center'>Resume</th></tr>";
$i=0;
while ($i < $num) {
$cname=mysql_result($result,$i,"cname");
$email=mysql_result($result,$i,"email");
$skills=stripslashes(mysql_result($result,$i,"skills"));
$qual=mysql_result($result,$i,"qual");
$id=mysql_result($result,$i,"cid");
$filename=mysql_result($result,$i,"res_title");
print "<tr>
<td>$cname</td>
<td>$email</td>
<td>$qual</td>
<td>$skills</td>
<td><img src='download.gif'>&nbsp;<a href='download.php?id=$filename'>$filename</a></td>
<td><a href='mailto:'>Forward</a></td>
<td><a href='updatestatus.php'>Update</a></td>
</tr>";
$i++;
}
print "</table>";
?>

i want search multiple values from the table. for example,

if i enter a search like " be java", then,

i have to search the db for 'be' key word and 'java' keyword seperately. then the result should be the candidates who having qualification 'be' and skills 'java' only to be displayed... how? help...

[code green]

You can use implode() or strtok() to split the words into array elements.
Then loop through the array creating a query with that part as the search string.

[Atli]

If you want to search for multiple values in a single field, a simple method is to use regular expressions. Note, this is not the most efficient method, but it does work.

For example, if I wanted to search for both "test" and "example" in a field I could do:
[code=sql]
SELECT `stuff` FROM `myTable`
WHERE `myCol` REGEXP '[[:<:]](test|example)[[:>:]]';
[/code]
This would return any field that had those two words anywhere within. The "[[:<:]]" and "[[:>:]]" parts will prevent it from matching the words within other words. If you don't want that, simply remove them.

(See 11.5.2. Regular Expressions for more info on how to construct more complex regular expressions)

If your search queries are coming in as space separated keywords, then all you would have to do is replace the space with a (|) and you put it into the query. The str_replace function could help you with that.

P.S.
Be sure to secure the input before using it though!

[impin]

i want to search multiple words in multiple fields in the table....

[Atli]

What I posted was an example. I showed you how you can use a regular expression to search a column. That should make it easy for you to build the query you need, based on what you already have.

I'm not going to write the whole thing for you.

[impin]

i dont want your code........... .. go

[InksEtc]

try "exploring" on php.net the php ... explode = " " function. This will seperate words that are placed together. This is what my PHP for Dummies says in Ch. 13explode ('sep", "string"): Creates an array of strings in wich each item i a substring of strings, separated by sep. For example, explode(" ",$string) creates an array in wich each wrd in $string is a separate value. This is similar to split in Perl.