MySQL search using PHP3

Anon

Hi FOlks,

I know the search question has been covered many times but here is just a varience on it.

I'm developing a website for an IT recruitment agency
and would like some info on the following.

I have a html textfield for searching for jobs and the text field accepts input from the user regarding their skills set.

This can be just one word (eg. HTML) or several (eg. JAVA C++ PHP ASP).

I need to use these words to search a field, 'description', in a MySQL database. 'Description' holds information on a vacancy including the skills required.

The way i thought to do this was to 'explode' the returned string from the form, and assign them to variables and use a query such as:

SELECT * FROM tablename WHERE description LIKE
'%$desc1%' AND description LIKE '%$desc2%' AND
description LIKE '%$desc3%' ..... and so on......

However i do not know the number of skills that the user may enter and also what if the user enters a string like 'visual basic' where it should be read as one variable but may get split into two because of the
space inbetween.

also i need the search to return results firstly using
'desc1' AND 'desc2' AND 'desc3' syntax, then once that is exhusted, i need it to return results where only one or two of the words are mentioned.

There are several points here that i have mentioned and I hope some one is able to follow what i have said, and also possibly provide a suitable solution.

A point to bear in mind is that the database is unlikely to hold more than 1000 entries at any time and typically will hold 150-300 entries. Therefore, not being particularly big, i don't think i want to set up a seperate table that hold key words etc (unless this is very simple to setup and integrate).

My main concern is how do i manage a variable number of keywords, and also how do i manage a string such as 'visual basic', which should be seen by PHP and MySQL as one variable, rather than two seperate variables (eg 'visual' and 'basic').

Many thanks in advance,

Vikesh.

Anon

MySQL has a "REGEXP" function that would allow you to do regular expression.
For example:
SELECT * FROM tblname WHERE skills REGEXP 'Visual Basic|PHP4|HTML';

-- this would match one or more skills.

Anon

Thanks for your reply:

Could you possibly explain how REGEXP works, and the benifits in using REGEXP over SELECT FROM.....

Thanks.

Anon

Here's one way I did something like this for a job search site (jobs.thetaxi.org). It searches for location, category, and through a title and description field for records. I tried to comment it as best as I could. It basically creates a bunch of little strings that are applied after a LIKE statement to make a huge query. Works fine on small databases, but is probably inefficient for anything large. Let me know if you want me to clarify anything...

It starts by adding the location and category to the criteria. Then it checks to see if the words are enclosed in quotes, "Visual Basic", and if so, it'll search for both of those words next to each other. If there are no quotes, it'll break up the words and add an element to the criteria looking for each word. These are either seperated by && or ||, depending on the value of a radio button, "find all words" or "find any word". Then it assembles everything into a huge query.

To see it work, visit jobs.thetaxi.org/search.php3

$element = 0;

// create element in criteria array of location = ##
if ($location > 1) { $criteria[$element++] = " location='$location' "; }
// create element in criteria array of category = ##
if ($category > 1) { $criteria[$element++] = " category='$category' "; }

//check to see if keywords are enclosed in quotes
if (substr($keywords,0,1) == "\"" && substr($keywords,-1) == "\"")
{
//strip off quotes
$keywords = substr($keywords,1,-1);
$kw_criteria[0] = " title LIKE '%$keywords%' ";
$kw_criteria[1] = " description LIKE '%$keywords%' ";
//end up with statment looking for title OR description
//containing 'keywords', enclosed by ()
$criteria[$element++] = " ( " . implode(" || ",$kw_criteria) . " ) ";
}
else
{
//check to see if anything was put in keywords field
if (strlen($keywords) > 0)
{
//seperate all keywords in array.
$words_array = explode(" ",$keywords);
//count how many words there are
$num_words = count($words_array);
$kw_element = 0;
for($count=0;$count<$num_words;$count++)
{
//for each word, create criteria looking for title OR description
//matching the word, enclosed within ()
$kw_criteria[$kw_element] = " ( title LIKE '%" . $words_array[$count] . "%' ";
$kw_criteria[$kw_element] .= " || ";
$kw_criteria[$kw_element++] .= " description LIKE '%" . $words_array[$count] . "%' ) ";
}
//join each title || description pair for each word into one string
//seperated by $match, which is either && or ||
//&& = match all words
//|| = match any word
$criteria[$element++] = " ( " . implode(" $match ",$kw_criteria) . " ) ";
}
}
//finally, join all of the criteria into the final criteria statement,
//seperated by && and enclosed by ()
//the && at the beginning is needed because the SQL statement always does
//a search for expired dates && final_criteria when searching for jobs
if (count($criteria) > 0) { $final_criteria = " && ( " . implode(" && ",$criteria) . " ) "; }

if (isset($search_jobs) || $list=="jobs")
{
//construct final query. end_date > NOW()-1 gives us dates greater
//than yesterday,so jobs expiring today aren't left out.
$query = "SELECT id,date_format(end_date,'b d, Y'),name,email,title,company FROM jobs WHERE (end_date>NOW()-1) $final_criteria ORDER BY end_date ASC";

}

Anon

Much to much to go into in a forum:

For an explanation of the REGEXP string comparison function in mySQL see the mySQL doc on page 178: (see below)

For an explanation of regular expressions:
http://www.perl.com/pub/doc/manual/html/pod/perlre.html

expr REGEXP pat
Performs a pattern match of a string expression expr against a pattern pat.
The pattern can be an extended regular expression. See Appendix I [Regexp],
page 580. Returns 1 if expr matches pat, otherwise returns 0. RLIKE is a
synonym for REGEXP, provided for mSQL compatibility. Note: Because MySQL
uses the C escape syntax in strings (e.g., \n'), you must double any\' that
you use in your REGEXP strings. As of MySQL 3.23.4, REGEXP is case insensitive
for normal (not binary) strings.
mysql> select 'Monty!' REGEXP 'm%y%%';
-> 0
mysql> select 'Monty!' REGEXP '.';
-> 1
mysql> select 'new\nline' REGEXP 'new\.\*line';
-> 1
mysql> select "a" REGEXP "A", "a" REGEXP BINARY "A";
-> 1 0
REGEXP and RLIKE use the current character set (ISO-8859-1 Latin1 by default)
when deciding the type of a character.