Search and query techniques

[Anon]

Hi All,

I'm working on this "job board system" and having problems with searching database (jobs). As you may know, job search facility is very popular on job sites, majority visitors use it. it means, is very important to get things right with this search script.

People use search facility differently. they type different keywords in different syntax to get results they want.

Search examples:

php developer
"php developer"
'php developer'
php+developer
php, developer
php, developer, London
....

and so on

I'm puzzled now how to make script that would be user friendly.

At the moment my sql (mysql) query looks like this:

$query = "SELECT * FROM jobs WHERE id_internal LIKE '%".$q."%' OR referance LIKE '%".$q."%' OR location LIKE '%".$q."%' OR position LIKE '%".$q."%' OR jobdesc LIKE '%".$q."%' OR position LIKE '%".$q."%' OR skills LIKE '%".$q."%' ORDER BY date DESC, priority DESC, id_internal DESC";

I simply add slashes to keywords ($q= addslashes($q)😉, as I added slashes when adding jobs into database and the search many columns in the table and hope one of them will have the keyword.

What must be done so search will give results according to keyword syntax?

Also I'm not sure about serialize() function, how is going to effect data and is there going to be difference in the searches, and if I need to use it.
http://www.php.net/manual/en/function.serialize.php

Thank you for suggestions.

Max

[crimaniak]

$query = "SELECT * FROM jobs WHERE id_internal LIKE '%".$q."%' OR referance LIKE '%".$q."%' OR location LIKE '%".$q."%' OR position LIKE '%".$q."%' OR jobdesc LIKE '%".$q."%' OR position LIKE '%".$q."%' OR skills LIKE '%".$q."%' ORDER BY date DESC, priority DESC, id_internal DESC";

It's not so easy. First, foreach(filled field) add_condition_to_WHERE(for_this_field,using_AND);

Second, nobody wants to give job to php developer. They want to have application developed. Nobody wants to buy MP3 player. They just want to listen music long time. And so on. It's very important to make usable search, think about.