Basically, I want one search box that will take the search string and cross reference each individual word with the cell data in the database and display the results.

I've got it working to a point (if I type Managing Director, it will find all Managing Director roles), however, what I'd like to happen is that by entering text into 1 form field, it will search all words against all the cells & return appropriate results.

For example; if I search, Managing Director London; I'd like it to return all MD roles in London. Now, the database is configured in such a way that the search will need to match Managing Director against 'job_title' & London against 'location'. Unfortunately, the code I'm using (see below) uses the whole string to compare against the database, so, it's searching the cells for 'Managing Director London', clearly this won't return any values for the reason above.

I don't want it to know whether it means 'managing' 'director london' or 'managing director' 'london', I want it to search each row with 'managing' and 'director' and 'london' and if those 3 appear anywhere in the row, it will be output to the results page.

This is the code I’m currently using;

<?php

$connection = mysql_connect("localhost","user","pass");
mysql_select_db("colvertgroup_co_uk_job",$connecti on) ;

$fields='`job_title` , `job_ref` , `job_desc` , `job_location` , `salary` , `job_type` , `key_words`';
$word = false;
if(!empty($_POST['search']))
{
$word=mysql_real_escape_string($_POST['search']);
$sql="SELECT $fields
FROM `tjob` WHERE concat($fields) LIKE '%$word%'";


if($word!=false)
{

$result=mysql_query($sql);
while ($row = mysql_fetch_row($result))
{
for ($i=0; $i<mysql_num_fields($result); $i++)
echo $row[$i] . " ";
echo "<br /><br>";
}
}
}
?>

Any Ideas?

You could split the search phrase into each word and put it into an array. Then build a mysql query string using a foreach loop....

PHP Code:

$wordAry = explode(' ',$word);
$qStr = '';
foreach ($wordAry as $k=>$value){
    $qStr = ($key == 0) ? ' concat($fields) LIKE "%'.$value.'%"' : ' AND concat($fields) LIKE "%'.$value.'%"';
} 


Then place $qStr in your query string.