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.
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!