Page 1 of 1

[SOLVED] Problem with SQL statement

Posted: Tue Aug 14, 2007 10:37 am
by jonthomas83
Hi guys, can anyone spot what's up with this SQL statement?

Code: Select all

SELECT latest_news.newsId, latest_news.newsDate, latest_news.newsHeadline, latest_news.newsStory, news_links.linkId, news_links.newsId, news_links.newsLink
LEFT JOIN latest_news, news_links 
ON latest_news.newsId = news_links.newsId
WHERE newsId = colname
(NOTE: colname is variable - $_GET['newsId'])


I'm getting an error to this effect

MySQL error# 1064
You have an error in your SQL syntax ... at line 1

Posted: Tue Aug 14, 2007 10:41 am
by feyd
Need more specifics on the error.

Posted: Tue Aug 14, 2007 11:03 am
by jonthomas83
the exact error I'm getting in Dreamweaver when I attempt to test the SQL is:

MySQL error #: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'LEFT JOIN latest_news, news_links ON latest_news.newsId = news_links.newsId WH'at line 1

Hope that helps a little!

Posted: Tue Aug 14, 2007 11:18 am
by feyd
You have no FROM clause.

Posted: Tue Aug 14, 2007 12:00 pm
by jonthomas83
Stupid mistake really sorry!

I'm trying to retrieve the 'newsId' passed through the url to match both the 'news_links' and the 'latest_news' record to that Id , however it's giving me an error saying:

MySQL error #: 1052

Column 'newsId' in where clause is ambiguous

Posted: Tue Aug 14, 2007 12:03 pm
by feyd
Because both tables have a newsId column, you need to specify which one you want the query to look at but adding a table reference to that particular statement.

Posted: Tue Aug 14, 2007 12:15 pm
by jonthomas83
Ok so I'm on the right lines, I knew there was something wrong with the statment but I'm not too sure how to implement a table reference to the statement.

The WHERE statment needs to change, I think! But not sure how, I've googled table references in sql but nothing relevant came up.

Do I need to do something with the table structure and data types?

Posted: Tue Aug 14, 2007 2:45 pm
by superdezign
jonthomas83 wrote:but I'm not too sure how to implement a table reference to the statement.

Code: Select all

`table`.`column`

Posted: Tue Aug 14, 2007 6:27 pm
by jonthomas83
Many thanks I got there in the end and it's all working for me now.

Code: Select all

SELECT latest_news.newsId
	, latest_news.newsDate
	, latest_news.newsHeadline
	, latest_news.newsStory
	, news_links.linkId
	, news_links.newsId
	, news_links.newsLink 
FROM latest_news 
LEFT JOIN news_links 
ON latest_news.newsId = news_links.newsId 
WHERE latest_news.newsId = var1
var1 = $_GET['newsId']

I now have a page that retrievs the newsId passed through the URL, matches it to the newsId column in the 'latest_news' table, the page displays all of the latest news according to the headline that was clicked in the previous page (the link that passes the variable). I have also LEFT JOINed another table called 'news_links' which displays all of the links for that news story, based on the newsId.

Many thanks to you guys for your help.

I'll try be a bit more efficient in my question asking next time, and now I've read through the docs in the useful links stickys I'll be a better equipped for this place!

Cheers,
Jonathan

P.S. For future reference, do MySQL questions go in this forum or in the PHP code forum?

Posted: Tue Aug 14, 2007 6:37 pm
by superdezign
jonthomas83 wrote:P.S. For future reference, do MySQL questions go in this forum or in the PHP code forum?
Here. If you are querying the database, but [you think] the problem is in the actual PHP, then you'd post it in the PHP forum.

Posted: Tue Aug 14, 2007 6:43 pm
by jonthomas83
Ok thanks, just checking I got the right place!