[thelist] Complex Search using PHP/MySQL

[Robert Gormley]

These are evil to program. Unless, of course, I'm doing it the wrong
way. I've just had to implement this in Classic ASP/MSSQL, though my
forte is PHP. If you're having no luck by tomorrow, I'll send you the
Stored Procs and program logic I used.

Basically, I worked out what SQL was needed for the AND/OR portion of
the queries, and based on that either did subselects, or UNION JOINs.
You could do it with temp tables, too, if you are using an older MySQL
version.

Essentially I did it by concatenating the search strings. For each
search criteria I had two strings to add: 'WHERE blah = blah', and a
'INNER JOIN blah....'

Indexing kept the search speed reasonably constant.

Rob 

> -----Original Message-----
> From: thelist-bounces at lists.evolt.org 
> [mailto:thelist-bounces at lists.evolt.org] On Behalf Of Tim Massey
> Sent: Tuesday, 25 October 2005 1:17 AM
> To: thelist at lists.evolt.org
> Subject: [thelist] Complex Search using PHP/MySQL
> 
> This may be a bit off topic but then again it may be right 
> on? If its off topic I would appreciate direction on where to look.
> 
> The problem is this - we are developing an internal 
> recruitment database. The design has been normalised (within 
> reason) and I'm up to the part of developing the search logic.
> 
> The search form is a monster - it includes the following 
> fields, most are in the candidates table others are in:
> 
> * external tables.
> ** lookup & category/type tables.
> 
> First Name, Surname, Gender.
> County, Country.
> Home & Work Phone, Email, Mobile.
> Job Title*
> Availability*
> Notice Period**
> Employment Type**
> Contract Rate**
> F/T Salary Band**
> Job Title(s)**
> Skills**
> Industries**
> 
> And the crown jewel - Boolean search of CV content (text 
> field in external table). A candidate can have many CV's
> 
> My question is - how would you go about filtering the 
> results, using PHP or rely more on sql? 
> 
> I'm thinking to do an initial search on the fields within the 
> candidates table, generating an array of possible id's then 
> filtering from the lookup tables to get a reduced array of 
> id's then performing the Boolean search on the CV's for only 
> those id's in the array.