SQL hell

[dancahill]

I'm trying to do a search on a mySQL DB of Job vacancies which match user inputted key words in the job_description column.

The vacancy table has following structure:

CREATE TABLE vacancy (
vacancyID int(5) DEFAULT '0' NOT NULL auto_increment,
companyID int(6) NOT NULL,
job_title varchar(30) NOT NULL,
job_description text,
locationID int(3) NOT NULL,
contract_typeID int(2),
vacancy_salaryID int(3),
date_posted date,
vacancy_status varchar(10),
PRIMARY KEY (vacancyID)
);

locationID matches an ID in a location table;

contract_typeID matches an ID in a contract table;

vacancy_salaryID matches an ID in a salary table;

2 out of 4 trial entries 'job_description' field contain the word 'dog'

NOW if I make this query:

SELECT DISTINCT vacancy.vacancyID, job_title, job_description, location.location, contract.contract_type, salary.salary
FROM vacancy, client, location, contract, salary
WHERE vacancy.locationID = location.locationID
AND vacancy.contract_typeID = contract.contract_typeID
AND vacancy.vacancy_salaryID = salary.salaryID
AND job_description LIKE '%dog%';

It works fine & displays the details

BUT if I want to put in 2 search words i.e......................

SELECT DISTINCT vacancy.vacancyID, job_title, job_description, location.location, contract.contract_type, salary.salary
FROM vacancy, client, location, contract, salary
WHERE vacancy.locationID = location.locationID
AND vacancy.contract_typeID = contract.contract_typeID
AND vacancy.vacancy_salaryID = salary.salaryID
AND job_description LIKE '%negotiation%'
OR job_description LIKE '%dogs%';

Which should pull up 3 results then it goes crazy & pulls out 1633 records which are basically compiled of the first correct record & then the next 2 records (which are the ones I want to display) but with as many permutations of contract_type, Salary & location as possible - hence the additional 1630 records!!

I can stop it by doing a 'Group By' but this just displays the 1st permutation and not the 100% correct record.

Sorry to be a bit lengthy but hope this is understandable & appreciate any assistance.

Dan.

[MrAlaska]

My brain hurts today, so I might not be reading this clearly, but I think the last OR is bypassing all AND's. To me, it looks like it should be in paren's:

AND
(job_description LIKE '%negotiation%'
OR job_description LIKE '%dogs%')

[ajmoore]

What you need to do is enclose the section of the query that contains the search strings in ()'s...

ie:
SELECT DISTINCT vacancy.vacancyID, job_title, job_description, location.location, contract.contract_type, salary.salary
FROM vacancy, client, location, contract, salary
WHERE vacancy.locationID = location.locationID
AND vacancy.contract_typeID = contract.contract_typeID
AND vacancy.vacancy_salaryID = salary.salaryID
AND (job_description LIKE '%negotiation%'
OR job_description LIKE '%dogs%');

otherwise that OR statement is applying to all of your WHERE statements....

Let me know if that doesn't take care of it...

[dancahill]

Brain hurting or not it's a better one than mine. That's been bugging me for a couple of hours.

A big thank you Mr. A

[MrAlaska]

No probem!

That one kind of jumped off the page because it has bit me before. Getting stuck sucks. I hate it when I run out of options after rebooting the server a couple times, figure out a dozen ways to blame it on Microsoft, then someone walking by spots a missing semi-colon in my code.

[chriskl]

One thing - don't use LIKE for your search. It will be hella slow!

Use FULLTEXT index instead:

http://www.mysql.com/doc/en/Fulltext_Search.html

It's thousands of times faster...

Chris

[dancahill]

Point taken but my version of mySQL (for win) doesn't support FULLTEXT.

Is there any faster way of doing it given I can't use FULLTEXT?