LEFT JOIN Problems

software development

#1

Hi

I’ve searched for this but couldn’t find the same issue. If someone could help out, I’d be grateful.

On my site I use this query:

SELECT blog.id, blog.cat_id, blog.title, blog.text, blog.tmstp, MONTHNAME( blog.tmstp ) AS mth, UNIX_TIMESTAMP( blog.tmstp ) AS unix_tmstp, blog.author, COUNT( blog_com.id ) AS numcomments, blog_cat.cat
FROM blog, blog_cat
LEFT JOIN blog_com ON blog.id = blog_com.blog_id
WHERE blog_cat.cat = 'Culture’
AND blog.cat_id = blog_cat.id
GROUP BY blog.id
ORDER BY blog.tmstp DESC

When I do this locally, it goes through fine. When I had this on my previous host it was fine, too. Now I’m with dreamhost I get a mysql_fetch_array message that is resulting from the sql. If I go into phpmyadmin and test the query this error message gets thrown up:

#1054 - Unknown column ‘blog.id’ in ‘on clause’

Now, this is where I’m confused. blog definitely exists as a table. id is the first column. Therefore, it exists. I am pulling my hair out on this one so if someone could tell me what’s wrong before I’m bald, my wife would be appreciative!

Cheers!


#2

I’m no mysql expert, but I’m guess your local box and your previous host were using mysql 4.x. New dremahost customers (and possible existing ones?) are now at mysql 5. I’ve read in the wiki that there’s some changes in the way join commands work now - so it may be you need to update some command syntax.

Here’s the Wiki article
And here’s somehting from Mysql.com

-Matttail


#3

dartcol, I suspect you’ve seen this by now, but I wanted to post it anyway. This came from the wiki page, and I’ve chopped a lot out…

We know that the following query breaks… FROM ibf_members m, ibf_groups g LEFT JOIN…
Here is how we fixed this query… FROM (ibf_members m, ibf_groups g) LEFT JOIN…

Have you made this change in your query yet?
Old: FROM blog, blog_cat LEFT JOIN…
New: FROM (blog, blog_cat) LEFT JOIN…

BC Tech
Team Shocker