As a relative newbie, and an almost total newbie to the use of left
joins, I'm aware that there's some difference in the way joins work
between MySQL 3.x and 5.x, but in my ignorance I can't figure out
what the heck it is from reading the 'upgrading' pages on
dev.mysql.com.
When I first joined this list (joined - geddit?), Barry in particular
solved a search problem for me by introducing left joins. A
simplified version of my query is this (it's a database of tourist
guides, where I've entered 'olympic' into the catch-all text field at
the bottom):
------------
select
[fields I want to display]
from
guides as g
left join biography as b on b.guide_id = g.id
left join interests as i on i.guide_id = g.id
left join tours as t on t.guide_id = g.id
left join walks as w on w.guide_id = g.id
left join lectures as l on l.guide_id = g.id
where
show_on_web = '1' and
(b.biography like '%olympic%' or i.interests like '%olympic%' or
t.tours like '%olympic%' or w.walks like '%olympic%' or l.lectures
like '%olympic%')
order by ...
------------
This worked like a charm (with fulltext indices on the text fields
being searched in those five tables) in 3.23.x, but now it falls over
and finds nobody at all in 5.0.19. The rest of the search is fine -
there are various <select>s and checkboxes on which you can search
and as long as I type nothing into the catch-all it behaves
perfectly, but as soon as I do I get a zero result. (The whole bit
with the left joins only gets added to the query if there's something
in the catch-all.)
The other major change is that I'm now using the utf8 charset
throughout the database and scripts, whereas before, with 3.23 not
supporting it, I was utf8_decode()ing everything that went to MySQL
and utf8_encode()ing everything that came out of it. I did try
putting back the utf8_decode() round the catch-all search string,
but (as I expected) it made no difference.
At first I thought the upgrade or utf8 might be having some effect on
the way "like '%...%'" works, but another simpler search uses that
and it's fine.
The whole point of having five separate tables for those elements is
that guides can record their information in a number of languages, so
there's a row per guide per language in each table - or maybe none at
all (not so many guides offer lectures, for example). I want users to
be able to find text in any of the languages on offer.
Where am I going wrong?
--
Cheers... Chris
Highway 57 Web Development -- http://highway57.co.uk/
The lead car is absolutely unique, except for
the one behind it which is identical.
-- Murray Walker
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
