Multiple search query??

[bolty2uk]

hi all,

 

just a quick post to see if anyone one out there can help me or maybe suggest ways of attempting what i need done..

 

If i explain what i have first then what i would like to happen it will give you an idea of what i need advice on..

 

-------------------------------------------------------------------------

WHAT I HAVE

 

I have mysql which i use phpmyadmin to edit/maintain..

I have two tables (listings and catagories)

 

in 'catagories' there are two fields ID and CATS...

1 doctors

2 dentists

3 schools

ert etc...

 

in 'listings' there are three fields NAME, ADDRESS and ID

joe norfolk 2

dave suffolk 1

sid essex 3

etc etc...

 

These tables are linked via the id fields, for example 'joe in norfolk will be a dentist' and 'sid in essex will be a school'

 

 

 

--------------------------------------------------------------------------

WHAT I WANT TO TRY

 

Ok now what i want to try to do is create a search query that will be able to pull info from the above tables, problem is i think it will need a query inside a query so to speak..

 

the search i would like to try is by category, so if a user searches for dentists in norfolk it will bring back the info from the listings table (joes row)

 

now i could achieve this easily by using checkboxes, dropdowns etc with the value of the ID number but there are just too many categories now.

 

Anyway, if anyone has any advice or suggestions it would be much appreciated..

 

Many Thanks for any replies...

[aschk]

What you might want to do is consider sub-categorising the "jobs" (as that's what they appear to be to me).

For example you might want to break down "dentist" into "orthodontist", "drillmaster", "general practitioner" or something (my dentistry knowledge is rather sparse).

 

- dentist
  - orthodontist
  - drillmaster
  - general practitioner
- doctor
  - surgeon
  - paramedic
- teacher (not school )
  - maths teacher
  - p.e. teacher

 

For this you will probably need a tree heirarchy where each level becomes more specific. Then you can select nodes from the tree at a certain depth and present this as selectable options. It will help to reduce the options at each level. See http://dev.mysql.com/tech-resources/articles/hierarchical-data.html for an idea on how to represent this in your database.

[bolty2uk]

Hi there..

 

thanks for the reply, yes the catagories already have parent categories so not really the issue..

 

what we need is if a user types the word 'dentist' onto a search form it will query the 'CATEGORY' table to find the word dentist...

when found dentist use the id assigned to denstist and with this id pull results with this same id from the LISTINGS table..

 

basically bring up all row which are in the dentist category..

 

I could do this if i added another column to the LISTINGS table called category and had the category as a word but for other reasons i need the two seperate tables..

 

its a bit of a nightmare, im just wondering if there is an easier way at all..??

[aschk]

Well you do indeed need a way to identify whether joe is a dentist or doctor (or...). So you WILL need a column in the 'listings' table that specified this information (i.e. column: 'category', value: 2, for dentist). Just add another column, however you might have complications when you decide that joe is both a dentist and a doctor, in which case you'll need a tertiary table for this mapping.

However for the time being, assuming that you have a column called 'category' that has the integer value of the particular category in question you can use the following SQL:

SELECT l.name
FROM listings l
JOIN categories c ON c.id = l.category
WHERE c.cats = 'dentists';