Job board select query problem

[ptaylor]

Hi,

Having trouble getting a select query to work for a job board I am building. I have a basic search and an advanced search - basic search works well, but the query for the advanced one is giving me problems.

 

The MySQL version is 4.1.2.

The advanced job search allows searching with the following fields:

- keywords (optional)

- industry (mandatory, up to five)

- locations (mandatory)

Where it gets complex is that I need to allow searches on locations in Canada, USA and the UK.

These are all select lists, as is the industry list, so there are no free text areas other than the keywords.

 

They can choose up to five locations in each country, using the select lists.

 

The table structures are as follows:

 

- employer_positions -  this is where the job details are stored

 

CREATE TABLE `employer_positions` (
  `pos_id` int(11) NOT NULL auto_increment,
  `contact_id` int(11) NOT NULL default '0',
  `pos_title` varchar(100) NOT NULL default '',
  `pos_description` text NOT NULL,
  `deadline` date NOT NULL default '0000-00-00',
  `compensation` varchar(50) NOT NULL default '',
  `contact_info` varchar(200) NOT NULL default '',
  `date_posted` date NOT NULL default '0000-00-00',
  `disable` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`pos_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=12 ;

 

- emp_position_industry - this stores the industries that correspond to the job in the above table

-- Table structure for table `emp_position_industry`
-- 

CREATE TABLE `emp_position_industry` (
  `emp_industry_id` int(11) NOT NULL auto_increment,
  `pos_id` int(11) NOT NULL default '0',
  `industry_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`emp_industry_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

 

 

- emp_position_location_can - this stores the locations in Canada for the job in the first table

-- Table structure for table `emp_position_location_can`
-- 

CREATE TABLE `emp_position_location_can` (
  `pos_loc_id` int(11) NOT NULL auto_increment,
  `pos_id` int(11) NOT NULL default '0',
  `location_id` int(11) NOT NULL default '0',
  `country_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`pos_loc_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=128 ;

 

- emp_position_location_usa - this stores the locations in USA for the job in the first table... there is a third table for the UK locations, but I won't post it here as it is the same layout.

 

So a job could be posted with up to 5 industries, and then up to 5 locations in each of the USA, Canada or the UK.

 

The select query for the advanced search I have is this:

 

$keys = addslashes(strip_tags(mysql_real_escape_string($_GET['qs'])));
$country = strip_tags(mysql_real_escape_string($_GET['country']));


$industry = array_map("mysql_real_escape_string",$_GET['industries']);
$indusaray = "'".implode(',',$industry)."'";
$theindusarray = trim($indusaray, "'");
$canada = array_map("mysql_escape_string",$_GET['clocations']);
$carray = "'".implode(',',$canada)."'";
$USA = array_map("mysql_real_escape_string",$_GET['uslocations']);
$usarray = "'".implode(',',$USA)."'";
$theuk = array_map("mysql_real_escape_string",$_GET['uklocations']);
$ukarray = "'".implode(',',$theuk)."'";
$thestring = $_SERVER['QUERY_STRING'];




if (!$keys){

$sql = mysql_query("SELECT p.pos_id, p.pos_title, p.date_posted, p.deadline FROM employer_positions AS p, emp_position_location_can AS l, emp_position_location_usa As u,                
                  emp_position_location_uk As k, emp_position_industry As i
			  WHERE (p.pos_id = l.pos_id Or p.pos_id = u.pos_id Or p.pos_id = k.pos_id)
			  AND i.industry_id in ($theindusarray)
			  AND (l.location_id in ($carray) Or u.location_id in ($usarray) Or k.location_id in ($ukarray))
			  GROUP BY p.pos_id") or die('Invalid query: ' . mysql_error());
     			 
			  $num_rows = mysql_num_rows($sql);


}
else if ($keys!=""){

$sql =  mysql_query("SELECT p.pos_id, p.pos_title, p.date_posted, p.deadline FROM employer_positions AS p, emp_position_location_can AS l, emp_position_location_usa As u,                
                  emp_position_location_uk As k, emp_position_industry As i
			  Where p.pos_title LIKE '%$keys%' OR p.pos_description LIKE '%$keys%'
			  And p.pos_id = l.pos_id AND (i.industry_id in ($indusaray)) 
			  AND (l.location_id in ($carray) Or u.location_id in ($usarray) Or k.location_id in ($ukarray))
			  GROUP BY p.pos_id") or die('Invalid query: ' . mysql_error());
     			 
			  $num_rows = mysql_num_rows($sql);

The $keys variable refers to the optional keywords for the search.

 

My problem is that I am getting inaccurate results from the search. A job will show up in the results that shouldn't, so something is picking it up within the parameters.

 

Is there a better way to write the query, so that it still covers all areas but is more accurate?

 

Many thanks in advance...

[ptaylor]

Sorry if there is too much info here...just trying to be thorough.

 

I am really stuck on this problem so any assistance would be greatfully received.

 

Thanks...

[toplay]

You forgot to put parenthesis around this:

 

Where p.pos_title LIKE '%$keys%' OR p.pos_description LIKE '%$keys%' AND

 

so change to this

 

Where (p.pos_title LIKE '%$keys%' OR p.pos_description LIKE '%$keys%') AND

 

 

Its not clear what you mean by "inaccurate results". So, it's best to show sample data, the query you're running, what's the data showing, and what you actually would like it to show.

 

 

[ptaylor]

Thanks for the reply...

 

By inaccurate results I mean when I do a specific search against the DB, (I only have a handful of sample job data in there at the moment) I am seeing a job show up in the results that shouldn't, based on the query.

For example, I search on:

Industry: Electronics

Location(s): Anchorage, Alaska

(that's the sample data I have in the DB)

 

When I execute the search, I see that job show up as well as another, which is for a totally different industry and location.

I think it's because I haven't seperated out all the "And" / "or" clauses in the where statement.

 

When I run it in the mysql interface I still get the same weird result, so it's not my php coding...

I see this result in both queries, the one with that includes the keywords and the other that doesn't.

 

Thanks...