Chris Sansom schrieb:
> 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.
Updating is always such a bad idea ;P
Do you know: never touch a running system? ^_^
> 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.)
And you don't see any misdone queries when you echo them, right?
Hope you checked that.
> 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.
ENCODE = NOT CODED into CODED
DECODE = CODED into NOT CODED
So encode the input into query and encode it afterwards :)
> 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.
So in simple words. You tried also to query the Table without encoding
it first into UTF-8?
> 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?
There are various, and the main spot here is the ENCODING of UTF-8.
More infos will be great.
It surely is tricky.
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 ...
I am not quite sure but using biography.guide_id instead of b.guide_id
would probably solve the problem.
I know that using aliases in WHERE clauses don't work really good, so
try this also please.
Greets
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
