Jump to content

Archived

This topic is now archived and is closed to further replies.

nezbie

Problem with SELECT field, field2 as...

Recommended Posts

I have a following query to return some simple search results from database..

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]SELECT p.page_id, pv.page_header AS element_header, pv.page_content AS element_content, pv.page_title, m.menuitem_type, m.menuitem_target, mv.menuitem_url FROM pages AS p LEFT JOIN pages_values AS pv ON p.page_id = pv.page_id LEFT JOIN menuitems AS m ON m.menuitem_id = p.page_id LEFT JOIN menuitems_values AS mv ON mv.menuitem_id = m.menuitem_id WHERE MATCH(pv.page_title, element_header, element_content) AGAINST ("%yeah%") AND pv.languages_id = "1"[/quote]

When run, mySQL returns the following error:

Unknown column 'element_header' in 'where clause'...

Why? I think I have declared this field in "pv.page_header AS element_header.. :) I tried looking up mysql online manual but according to that I can't find anything wrong with my query (although there obviously is something wrong :P)


thanks beforehand.
- nezbie

Share this post


Link to post
Share on other sites
You can't use your column aliases in your WHERE clause, because when the query runs, those aliases haven't been "done" yet. That is, the query is run first, and then the selected columns are extracted and renamed according to your aliases. You have to use the "proper" name of the column in your query. Try the following (UNTESTED):

[code]SELECT p.page_id, pv.page_header AS element_header, pv.page_content AS element_content, pv.page_title, m.menuitem_type, m.menuitem_target, mv.menuitem_url FROM pages AS p LEFT JOIN pages_values AS pv ON p.page_id = pv.page_id LEFT JOIN menuitems AS m ON m.menuitem_id = p.page_id LEFT JOIN menuitems_values AS mv ON mv.menuitem_id = m.menuitem_id WHERE MATCH(pv.page_title, pv.page_header, pv.page_content ) AGAINST ("%yeah%") AND pv.languages_id = "1"[/code]

Hope that helps.

Share this post


Link to post
Share on other sites
[!--quoteo(post=340907:date=Jan 29 2006, 01:40 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 29 2006, 01:40 PM) [snapback]340907[/snapback][/div][div class=\'quotemain\'][!--quotec--]
You can't use your column aliases in your WHERE clause, because when the query runs, those aliases haven't been "done" yet. That is, the query is run first, and then the selected columns are extracted and renamed according to your aliases. You have to use the "proper" name of the column in your query. Try the following (UNTESTED):

[code]SELECT p.page_id, pv.page_header AS element_header, pv.page_content AS element_content, pv.page_title, m.menuitem_type, m.menuitem_target, mv.menuitem_url FROM pages AS p LEFT JOIN pages_values AS pv ON p.page_id = pv.page_id LEFT JOIN menuitems AS m ON m.menuitem_id = p.page_id LEFT JOIN menuitems_values AS mv ON mv.menuitem_id = m.menuitem_id WHERE MATCH(pv.page_title, pv.page_header, pv.page_content ) AGAINST ("%yeah%") AND pv.languages_id = "1"[/code]

Hope that helps.
[/quote]

ok yep the original query was like that. It's just I could have used the same php code later on this page for news and such stuff if the fields returned by sql were named the same.

Thanx anyway.
- nezbie

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.