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)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
