Hello all ! ! !

I need a litle help on how to do some search on a MySQL Database.

I plan to create a Web Business Directory. That directory will be consists of three main tables. (Propably I will need more than three, but for more easy explain I say three for now)

The first one will be a table that will store inside the cities and the areas of my country as a tree, by using the lft, rgt model.

The second one will store inside business categories and sub categories as a tree too, by using again the lft, rgt model.

The third one table will contain the business info, such as company name, description, some google map locations, phones, and so on.

THE QUESTION NOW :

The question is how can I search all that informations after the project building ? ? ? I know how to search in a field by it seld, but how can I compine many fields based on the search creteria to extract the informations from the data.

I have this question because if the cliend will try to search something like that : " bars in street name ", or search for something like that "hotels in city name" how to get all that informations from the database ? ? ?

Thanks a lot ! ! !

When I mean the lft rgt model I mean a table like that

ID NAME right_count left_count

It would be easiest if the user enters the location in one field (perhaps a pull down), business category in another, other details in another field.

First get your lft rgt data for location using the location name ie if the user enters New York - Battery Park and the codes for this are lft=230 and rft=239. Build your where clause using this info ie where_clause=" where loc_code between 230 and 239".

Next do the same for business category and add to your where clause, let's say Bars which makes the where_clause becomes " where loc_code between 230 and 239 and cat_code between 40 and 49".

Next add the main search field if data has been added ie where_clause now = " where loc_code between 230 and 239 and loc_code between 40 and 49 and business_name like '%Harvey%' ". This will show all the business

If the location and category is stored in the business table then just initialise the query with something like the following which would pull all the bars in the Battery Park area that have Harvey in their name:

Code:

select * 
from  business 
where loc_code between 230 and 239 
     and cat_code between 40 and 49 
     and business_name like '%Harvey%'

Of course you might want to be careful of returning to many rows ie if someone asks to see all the bars in New York. Another method might be to total the rows returned into business categories or locations then allow the user to drill down.

Mike

First of all I like to thank you for your assistant.

I already know how to use the Tree model, and even better, I will use the CakePHP framework that make the work by It sesf.

My realy big problem is described here :

Lets assume that we have the bussiness table profile that It has the following columns

ID | Company_Name | Company_Phone | Company_Description | Company_Category | Company_Area

On the above columns the Company_Category and the Company_Area are foreign keys to the Category table tha hold the categories tree and the Campany_Area that hold the Areas in the tree model again.

Now lets say that the end user want to search into the data and enter on the search engine something like that :

"Hotels in Carebian" or "Sunset Hotel" or "Hotel with swimming pool". How the my search engine will decide wich columns to serch ? ? ?

In the first query must search on Categories and on Areas
The Second query must search on Company_Name and the last query must search on Category table and on the description field of the company profile.

An idea that I had is to split the query with some Reqular Exression and search with each word any table and then merge the data with some way, but I'm not sure if that is fine.

The Idea that you gave me to list all the categories and the areas in two select boxes is good I think, but what happens if in two years the categories are too much (ie 600 categories ? ? ?)

Thanks again ! ! !

I simply gave you a solution that I felt was easy to understand and easy to code.

You can take all the information from one input string but it's a lot more difficult to code, to avoid getting false matches and also slower. You could start by matching each word with each available category and award scores for each match then, taking the best scoring combinations of categories, match against the businesses that are in these categories.

I suggest you go with the different fields for the different parts of the search because it will be easier for you to implement. You don't have to have pull downs, you could have free text or you could stay with pull downs but get them to refresh themselves with the data at the next level - this would mean the user wouldn't have to trawl through 600 items but would have to work his way down the tree. A small benefit of having pulldowns is that it will avoid spelling mistakes.

It's all well and good having marvelous ideas in what to present to the user but you do have to keep in mind that you'll be the one writing the code.

Mike

Thanks again ! ! !

And now another and last question !

Is it a good idea to search one by one all the database fields to much keywords ? ? ? Or will slow down the system ? ? ?

Ofcource and I will use indexes for the most common searched but is that a good idea to search each column for muching data ? ? ? ?

Thanks a lot again ! ! !

Searching on a field that doesn't have an index will result in a table scan. Doing this multiple times on multiple tables will result in lots of scans. This will make things slow. It really depends on how much data you have.

This is quite a difficult type of search and I my recommendation would be to start simple and then try and add more functionality from there - make sure you monitor how your changes affect the search times etc. I suspect if you try and implement everything at once then you'll get stuck.

I typed your query of "Hotels in Carebian" into Google and it produced a whole list of hotels in the area along with reviews. It did this in 0.3 seconds. It even made allowances for your spelling of Caribbean I suppose you have to think how your system will compare to a typical search engine like Google. If your system will offer something it doesn't then fine.

Mike