Chris Sansom wrote:
At 15:56 +0200 25/4/06, Barry wrote:
And you don't see any misdone queries when you echo them, right?
Hope you checked that.
Hi Barry
I was wrong about its being a PHP issue: it's definitely a MySQL
error. I realised I hadn't handled the error in such a way that I
could see what it was, but now I have, so...
The full query, in all its hideousness (but prettied up a bit in the
formatting :-) ) is:
----------
select count(distinct uid) as c
from aptg_guides_restricted as r, aptg_guides as g
left join guides_biography as b on b.guide_id = r.uid
left join guides_interests as i on i.guide_id = r.uid
left join guides_tours as t on t.guide_id = r.uid
left join guides_walks as w on w.guide_id = r.uid
left join guides_lectures as l on l.guide_id = r.uid
where g.guide_uid = r.uid and show_on_web = '1' and
(b.biography like '%london%' or i.interests like '%london%' or t.tours
like '%london%' or w.walks like '%london%' or l.lectures like '%london%')
----------
and the error I get back is:
Unknown column 'r.uid' in 'on clause'
...but I can assure you there is definitely a 'uid' column in
aptg_guides_restricted. If I take out the 'r.' from those left joins
(there's no uid in any other table mentioned here) I get basically the
same error: Unknown column 'uid' in 'on clause'. And if I spell out
'aptg_guides_restricted.uid' in the joins I /still/ get the error:
Unknown column 'aptg_guides_restricted.uid' in 'on clause'
So what /is/ the problem here? I say again: this and /exactly/ this
worked perfectly in MySQL 3.23, so there's obviously some change in
syntax handling or whatever between versions.
Yes. 3.23 was not correct in the order of precedence.
This has been answered many times here.
You need to change your comma join to an inner join.
select count(distinct uid) as c
from aptg_guides_restricted as r
inner join aptg_guides as g on g.guide_uid = r.uid
left join guides_biography as b on b.guide_id = r.uid
left join guides_interests as i on i.guide_id = r.uid
left join guides_tours as t on t.guide_id = r.uid
left join guides_walks as w on w.guide_id = r.uid
left join guides_lectures as l on l.guide_id = r.uid
where show_on_web = '1' and
(b.biography like '%london%' or i.interests like '%london%' or t.tours
like '%london%' or w.walks like '%london%' or l.lectures like '%london%')
In fact, this is a preliminary query to establish the total. If there
is a total, I then run this:
----------
select distinct uid, firstname, lastname, year_qualified,
other_qualifications, guide_driverguide, guide_photo_1
from aptg_guides_restricted as r, aptg_guides as g
left join guides_biography as b on b.guide_id = r.uid
left join guides_interests as i on i.guide_id = r.uid
left join guides_tours as t on t.guide_id = r.uid
left join guides_walks as w on w.guide_id = r.uid
left join guides_lectures as l on l.guide_id = r.uid
where g.guide_uid = r.uid and show_on_web = '1' and
(b.biography like '%london%' or i.interests like '%london%' or t.tours
like '%london%' or w.walks like '%london%' or l.lectures like '%london%')
order by from_unixtime(unix_timestamp(guide_last_updated)) *
(rand(1569933185) + ((length(guide_photo_1) > 1) / 3)) desc
----------
...and if I run that directly in the SQL window in phpMyAdmin, I get
the same error: Unknown column 'r.uid' in 'on clause'.
¿Qué?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
