First I should say I'm using MySQL 3.23.x because that's what's
currently available on our host's server. An upgrade to 5.x is
promised any time now, but I'm not holding my breath! So, with that
in mind...
I'm trying to do quite a sophisticated search across several tables
and am running into trouble, maybe because of trying to use 'or',
maybe because of records not present, I dunno. Whatever it is,
there's obviously a flaw in my logic that I can't find.
Simplifying it to the relevant bits, I'm searching six tables of data
relating to people. For the present purpose I'll call the tables
T1..T6. T1 contains a record for each person, including lots of basic
information and an ID field that the other tables refer to. Tables T2
to T6 each have three fields: that same id, a field for a two-letter
language code (en, fr, de, etc.) and a lump of text. The important
thing is that in T2 to T6, any one person might have several rows if
they've provided information in several languages - or none at all
(relatively few have any data in T6, for example).
I want to be able to enter a bit of text in the search form and find
anyone in the db with the search term anywhere in T2..T6, in any
language.
My first instinct was this:
select
[fields I want to display from t1]
from
t1, t2, t3, t4, t5, t6
where
(t2.text like '%search_term%' and t2.id = t1.id)
or
(t3.text like '%search_term%' and t3.id = t1.id)
or
(t4.text like '%search_term%' and t4.id = t1.id)
or
(t5.text like '%search_term%' and t5.id = t1.id)
or
(t6.text like '%search_term%' and t6.id = t1.id)
However, that sent the system into what was obviously some huge loop
which, if I waited long enough, would have produced thousands upon
thousands of results. It also sent my UPS into overload, so I
interrupted it! The logic looks right to me, but obviously there's
something wrong: as a friend agreed, 'or' often doesn't seem to do
what you expect in SQL!
So then I tried this:
select
[fields I want to display from t1]
from
t1, t2, t3, t4, t5, t6
where
t2.id = t1.id and t3.id = t1.id and t4.id = t1.id and t5.id =
t1.id and t6.id = t1.id and
(t2.text like '%search_term%' or t3.text like '%search_term%' or
t4.text like '%search_term%' or t5.text like '%search_term%' or
t6.text like '%search_term%')
That produced no results at all, and I quickly saw why: if a person
has no rows at all in any of T2..T6 they'll be excluded. Once I
removed T6 from the search, one person reappeared. She didn't have
the search term in all the other tables, but she does at least have
entries there, but none in T6.
The first solution definitely seems more logical and elegant, but
where have I gone wrong, O MySQL gurus? I've investigated left joins
(which I've so far had no use for, strangely enough) but can't see
how to apply them in this case. Maybe subqueries, which I know we'll
get in MySQL 5, are the answer?
A workaround would be to force a row for every person in each of the
multi-lingual text tables - for English, say - even if the text field
is empty, but I don't like introducing redundant non-data like that.
At least the second search would work though.
Another possibility would be to use several queries, but that would
mean some fairly hefty rewriting of the php that puts this stuff
together, so I'd prefer to avoid it if possible. Surely it /must/ be
possible in one query?
(Before we decided to make this thing multi-lingual it was simple of
course, because the five lumps of text were all in T1.)
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
I used to think I was indecisive, but now I'm not so sure.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
