List:General Discussion« Previous MessageNext Message »
From:Barry Date:April 25 2006 1:56pm
Subject:Re: Stumped again by joins
View as plain text  
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)
Thread
Stumped again by joinsChris Sansom25 Apr
  • Re: Stumped again by joinsBarry25 Apr
    • Re: Stumped again by joinsChris Sansom25 Apr
    • Re: Stumped again by joinsChris Sansom25 Apr
      • Re: Stumped again by joinsgerald_clark25 Apr
        • Re: Stumped again by joinsChris Sansom25 Apr
      • Re: Stumped again by joinsPeter Brawley25 Apr
Re: Stumped again by joinsPhilippe Poelvoorde25 Apr