Search multiple fields with Mysql in php

[a1suria]

hello guys.. can anyone please help me

i have created a Simple search for my database..the search form contains a drop down menu of the field names and user select the field name and input the keyword and then Click on search to retrieve matching records from that particular field..

Code here

form.php
<form action="results.php" method="post"> 
Search within:<br /> 
<select name="searchtype"> 
<option value="question">Question</option> 
<option value="answers">Answers</option> 
<option value="elements">Elements</option> 
</select> 
<br /> 
Enter Search Term:<br /> 
<input name="searchterm" type=”"text" size="40"/> 
<br /> 
<input type="submit" name="submit" value="Search"/> 
</form> 





result.php


<?php 
// create short variable names 
$searchtype=$_POST['searchtype']; 
$searchterm=trim($_POST['searchterm']); 

if (!$searchtype || !$searchterm) { 
echo 'You have not entered search details.  Please go back and try again.'; 
exit; 
} 
if (!get_magic_quotes_gpc()){ 
$searchtype = addslashes($searchtype); 
$searchterm = addslashes($searchterm); 
} 
@ $db = new mysqli('localhost', 'root', '', ''); 

if (mysqli_connect_errno()) { 
echo 'Error: Could not connect to database.  Please try again later.'; 
exit; 



} 
$query = "select * from table where ".$searchtype." like '%".$searchterm."%'"; 
$result = $db->query($query); 
$num_results = $result->num_rows; 

echo "<p>Number of records found: ".$num_results."</p>"; 

for ($i=0; $i <$num_results; $i++) { 
$row = $result->fetch_assoc(); 




echo "<p><strong><br>".($i+1).". First Name: </strong>"; 
echo htmlspecialchars(stripslashes($row['fname'])); 
echo "<strong><br />Last Name: </strong>"; 
echo stripslashes($row['lname']); 

echo "<strong>Question: </strong>"; 
echo stripslashes($row['question']);
echo "<strong> <br />Answer: </strong>"; 
echo stripslashes($row['answers']);
//echo "</p>";

echo "<strong> <br />Elements:</strong> "; 
echo stripslashes($row['elements']);



$db->close(); 
?>

i want to have a multiple search function...
Example \
FieldName = "Question"
User input = ? e.g. what is it?

FieldName = "Answers"
User input = ? e.g. nothing

FieldName = "Elements"
User input = ? e.g. animal

SEARCH BUTTON

Once user clicked on search button.... it should retrieve and echo all the Records(rows) which contains all the keywords in a single row on the top of the page and then records which matches Two fields and One field.

Result should look like:

[B]"2 records found with 100% match"[/B]
  [B]  id    Question     Answers   Elements  [/B]
       1    what is it?   nothing    animal
       33    what is it?   nothing    animal


 [B]"2 records match 60%(2/3)"[/B]
  [B]  id    Question       Answers    Elements  [/B]
       25    what is it?    somthing   animal
       60    where is it?   nothing    animal


 [B]"1 record match 30%(1/3)"[/B]
  [B]  id    Question       Answers      Elements  [/B]
       30    how is it?     idontknow    animal
       80    where is it?   idontknow    animal

Please ask me if i dont make sense..
Thank you

[madCoder]

MySQL does offer FULL-TEXT search capabilities. What you describe sounds similar to that. It lets you search on strings, including multiple columns and returns a 'relevance' with each row.

Hope that helps.

[a1suria]

i know.. sir but how do i do the sql query to out put that result