Hello All,
I have been trying to figure out why the below query doesn't quiet produce the
results we are looking for. I was hoping some one maybe able to shed some lite
on why QUERY #3 returns the wrong results. If addition information is needed,
please feel free to respond privately, off the list.
Appreciate any help! It's been driving me wack-o! ;)
======================================================================================
1) QUERY w/o KEYWORDS:
SELECT bi.bus_name,bi.address,bl.city,bl.state,
bl.zipcode,h.hr_descript,bi.phone,bi.comments,bi.unique_url,
CONCAT(bi.contact_fname," ",bi.contact_lname) AS name
FROM bus_info bi,bus_loc bl,hours h
WHERE bi.category = 'VEAD'
AND bi.info_id = bl.loc_id
AND h.hr_code = bi.hrs_open
GROUP BY bi.bus_name
4 results - Correct
=================
2) QUERY w/KEYWORDS
SELECT bi.bus_name,bi.address,bl.city,bl.state,
bl.zipcode,h.hr_descript,bi.phone,bi.comments,bi.unique_url,
CONCAT(bi.contact_fname," ",bi.contact_lname) AS name
FROM bus_info bi,bus_search bs,bus_loc bl,hours h
WHERE MATCH bs.keywords AGAINST ('book')
OR bi.category = 'VEAD' OR bs.cat_prefix = 'VE'
AND bs.bus_id = bi.info_id
AND bi.info_id = bl.loc_id AND h.hr_code = bi.hrs_open
GROUP BY bi.bus_name
5 results
Correct Keywords match agaisnt "book"
================
3) QUERY w/Keywords
SELECT bi.bus_name,bi.address,bl.city,bl.state,
bl.zipcode,h.hr_descript,bi.phone,bi.comments,bi.unique_url,
CONCAT(bi.contact_fname," ",bi.contact_lname) AS name
FROM bus_info bi,bus_search bs,bus_loc bl,hours h
WHERE MATCH bs.keywords AGAINST ('brake repair')
OR bi.category = 'VEAD' OR bs.cat_prefix = 'VE'
AND bs.bus_id = bi.info_id
AND bi.info_id = bl.loc_id AND h.hr_code = bi.hrs_open
GROUP BY bi.bus_name
5 results
Incorrect should only be 4 results "brake repair" is not in the column keywords
so it should not return the results.
================
In the table that it searches(bus_search) for the keywords, column name:
keywords varchar(225) with a FULLTEXT index on keywords
During a keyword search it also checks the column: cat_prefix
The data record it should be checking has the following info. this is the only
record in this table right now.
column value
*--------|--------*
bus_id | 9
keywords |"Secrets to the Best Car Deals negotiating prices book with tips for
buying"
cat_prefix | VE
Mike(mickalo)Blezien
========================================
Thunder Rain Internet Publishing
Providing Internet Solutions that work!
http://www.thunder-rain.com
Tel: 1(225) 686-2002
=========================================
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
