How to search the mysql database using php

impin

thjis is my search form...

Expand|Select|Wrap|Line Numbers

 
<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

Expand|Select|Wrap|Line Numbers

 
<?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:

Expand|Select|Wrap|Line Numbers

 
SELECT `stuff` FROM `myTable`

WHERE `myCol` REGEXP '[[:<:]](test|example)[[:>:]]';

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.

How to search the mysql database using php

Post your reply

Similar topics