Jump to content


Photo

Problem with SELECT field, field2 as...


  • Please log in to reply
2 replies to this topic

#1 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 29 January 2006 - 02:59 PM

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
:: Be patient with me, I\'m new to PHP and MySQL :: :)

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 29 January 2006 - 06:40 PM

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):

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"

Hope that helps.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 nezbie

nezbie
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts
  • LocationFINLAND

Posted 29 January 2006 - 09:11 PM

[!--quoteo(post=340907:date=Jan 29 2006, 01:40 PM:name=fenway)--][div class=\'quotetop\']QUOTE(fenway @ Jan 29 2006, 01:40 PM) View Post[/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):

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"

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
:: Be patient with me, I\'m new to PHP and MySQL :: :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users