Search function mySQL problem

[icb01co2]

Hi all,
I have a problem that i hope you can help me with. I am developing a site for a training company http://www.pitmanskills.com. The problem is that there is a course search within the site that isnt working to plan. The courses are held in a mySQL database with the following fields;

Title, Aims and Objectives, Prerequsites, Course Content etc.

Originally i used an SQL statement to select from by courses by the course title, this wasnt sufficient as when searching for 'databases' our MSAccess courses werent found. Next i used all fields to get the information. This again isnt right because selecting by all these fields means the most appropriate course appears miles down the list returned.

i.e. when searhing for "word" the "word 2000 expert" course appear below several other non-relevent courses with the word "word" in on of their fields.

How do i select all the results from the following statement :-


select * from pitman_courses
where `Course_Title` like \"%$trimmed%\"
OR `Target_Audience` like \"%$trimmed%\"
OR `Prerequisites` like \"%$trimmed%\"
OR `Aims_and_objectives` like \"%$trimmed%\"
OR `Course_Content` like \"%$trimmed%\"
order by Course_Title

But then put the results returned by;

select * from pitman_courses
where `Course_Title` like \"%$trimmed%\"

at the top?

Thanks, Chris.

[HardCoded]

What version of MySQL are you using?

[icb01co2]

im not sure ill check with the hosts, 5 mins.

[icb01co2]

I cant get hold of them, why is the added functionality that may be useful for my situation in later versions?

[ronniethedodger]

Might I suggest something simpler? Add a keywords field which will contain the key terms from each field. Arrange the terms in the order of importance. This would (of course) be a judgement call of a human who enters the course data, but it is cheap and easy way of "indexing" your data.

Your query would be against the keyword field, and depending upon the postition of the word in that field would give you the relevancy (or approximation of it).

I am not sure of the exact SQL statement, but it would involve selecting the strpos() INTO an array variable that holds the position of the search term in the keyword field, then doing a sort on that array.

[steve0]

Applicable solutions depend in part on the version of MySQL you are using.

Sub-queries may be an option if you are running one of the latest versions;

From a mysql prompt, type '\s' or 'SELECT version();'
to have it dump the relevant info to the screen.

[icb01co2]

Ive got it version 3.22 i think.

I dont really want to do the keywords field because we have hundreds of courses and would be an admin nightmare.

[steve0]

If I understand your question, you want the course titles with keywords to appear first...

This may be over simplistic, but why not just run 2 queries?

The first being
select * from pitman_courses where Course_Title like '%$trimmed%'
with the output stating ..
"The Following Courses matched your query:"

Then
select * from pitman_courses
where `Target_Audience` like \"%$trimmed%\"
OR `Prerequisites` like \"%$trimmed%\"
OR `Aims_and_objectives` like \"%$trimmed%\"
OR `Course_Content` like \"%$trimmed%\"
order by Course_Title

with the output stating ..
"<HR>The following courses may also be of interest:"

(You may want to have the second query omit any results where Course_Title like '%$trimmed%'
to avoid duplicates.)

[HardCoded]

Even simpler to use the IF() function:

select *,
IF(`Course_Title` like \"%$trimmed%\",0,1) AS in_title
from pitman_courses 
where `Course_Title` like \"%$trimmed%\" 
OR `Target_Audience` like \"%$trimmed%\" 
OR `Prerequisites` like \"%$trimmed%\" 
OR `Aims_and_objectives` like \"%$trimmed%\" 
OR `Course_Content` like \"%$trimmed%\" 
order by in_title, Course_Title

[icb01co2]

Hi HardCoded,
I take my hat off to you, that was exactly what i was looking for. You are an SQL master.

[icb01co2]

Hi,
Spoke too soon, now if i type in 'Databases' i dont get ' Access 2000', now the key word is in the course content field but not in the title. I still need to recieve data that doesnt have the keyword in the title. But i want the ones with the keyword in the title to be ranked highest, understand?

[HardCoded]

The new query should return the exact same results as the old one, just ordered differently. Double check your results. Did you cut n' paste exactly as I had it, or did you have to alter it?

[icb01co2]

Yup, my mistake, you actually are a star! :-) Thanks.