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=InnoDB 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=InnoDB 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=InnoDB 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...
