[SOLVED] Problem with SQL statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

[SOLVED] Problem with SQL statement

Post 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
Last edited by jonthomas83 on Fri Aug 17, 2007 2:46 am, edited 1 time in total.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Need more specifics on the error.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post 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!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

You have no FROM clause.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post 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?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

jonthomas83 wrote:but I'm not too sure how to implement a table reference to the statement.

Code: Select all

`table`.`column`
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post 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?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post 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.
jonthomas83
Forum Newbie
Posts: 15
Joined: Sun Aug 12, 2007 6:21 pm

Post by jonthomas83 »

Ok thanks, just checking I got the right place!
Post Reply