Problem with SELECT field, field2 as...

[nezbie]

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

[fenway]

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.

[nezbie]

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