Hello All,
MySQL version 3.23.33
I was hoping someone maybe able to offer some suggestion on a search query I've
been working for a day or so, but seems to be getting no where fast!
I've attached the tables and test inserts I'm working with. This is for a
business referral search where people submit local business they've used and
submit them. Then other people that visit the website can do a search for these
business.
The search criteria is either they enter a 1)city or 2)state or 3)zipcode and
must select a category. Only 1 of the 3 can be used along with the category, not
all 3
Lets say I am going to do a search. I have select a State(CA) and selected a
category: COI (Computer>Internet) and enter the keywords: "Web Hosting"
Now it will search for all states(CA) matching the COI(Computer>Internet)
category and ALL CO base categories(CO is the base category of all the CO.. sub
categories) with match against the keywords "Web Hosting". So if my business is
listed under the COP(Computer>Programming) category, my company would be
included in the search results(if we have the correct keywords in the bus_search
table) as we are in the base category CO
Now If I do a search and select a state(CA) and category COI (Computer>Internet)
with NO keywords, it will search all states that are in the COI category only,
no problem there. The problem I'm having is building the search query, when
keywords are present. The test query below doesn't produce the results we're
looking for. I was hoping, some one would take a moment or two, review the
attached table/insert data and can suggest a query that will produce the results
needed with keywords.
Test Query:
SELECT bi.bus_name,CONCAT(bi.contact_fname," ",bi.contact_lname) AS Name
FROM bus_info bi,bus_search bs
WHERE (MATCH bs.keywords AGAINST ("Web Hosting")
OR bs.cat_prefix = 'CO')
AND bl.state = 'CA'
AND bs.bus_id = bi.info_id
AND bl.loc_id = bi.info_id
GROUP by bi.bus_name ASC
I truly appreciate any assistance anyone maybe to offer! I need to get this done
before the end of the week! :)
TIA.
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
