1054 - Unknown column 'a.authors_id' in 'on clause'

[fuzzyphil]

Hi using latest release of oscmax. I cannot get the Articles function to work correctly.
I keep getting the following error mesage...

1054 - Unknown column 'a.authors_id' in 'on clause'

select count(*) as total from articles a, articles_description ad left join authors au on a.authors_id = au.authors_id, articles_to_topics a2t left join topics_description td on a2t.topics_id = td.topics_id where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_status = '1' and au.authors_id = '1' and a.articles_id = a2t.articles_id and ad.articles_id = a2t.articles_id and ad.language_id = '1' and td.language_id = '1'

[TEP STOP]

Is there an obvious reason I should be looking for here as I cant see where I am going wrong?
Have tried with both PHP4 and 5. No difference.
Have created author, article, enabled articles in admin so link can be seen from front page but just keep getting error above.

Please advise.

Phil.

[fuzzyphil]

So 40 plus people have viewed this problem yet no one can answer the question!!!
Presumably if people ae viewing it then they to are looking for a way to make it work.
I have also noticed that many other people have posted similar questions on other forums and got nowhere.

Does this feature work or not?

Surely someone must have some ideas please.
Phil

[michael_s]

Nobody has responded because you did not provide enough info for anyone to help. We need to know how you generate the error, what page it happens on, what versions of mysql, what webserver (apache? IIS?)...

[Pape Design]

Edit the files called articles.tpl.php and articles_new.tpl.php in your templatename/content folder.

Look for the queries containing:
from " . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t left join " . TABLE_TOPICS_DESCRIPTION . " td on

and change them into:
from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t) left join " . TABLE_TOPICS_DESCRIPTION . " td on

(putting parenthesis after "from" and before "left join" statements).


MySQL 5.0.15 follows the syntax rules of SQL:2003:

<from clause> ::= FROM <table reference list>
<table reference list> ::=
<table reference> [ { <comma> <table reference> }… ]
<table reference> ::=
<table factor>
| <joined table>
<joined table> ::=
<cross join>
| <qualified join>
| <natural join>
…

[MindTwist]

Bleh... I am having that exact ame error too. Will look into it.

Running:

Linux 2.6.9-42.ELsmp
MySQL 5.0.27-standard
PHP 5.1.6 (Zend: 2.1.0)
Apache/1.3.37 (Unix) PHP/5.1.6 mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 FrontPage/5.0.2.2635.SR1.2 mod_ssl/2.8.28 OpenSSL/0.9.7a

[MindTwist]

Ok, I would say it is working perfect for me now, I changed several more lines besides the ones that Pape Design says, otherwise I could see all articles, but it was giving me errors when seeing a given category.

The solution was over here:
article manager 1.2b-1054-'a.authors_id' - osCommerce Community Support Forums

-----
I had this problem. I wrapped the FROM statements in () and it works fine now. Something to do with the way the new MySQL 5 functions are prioritized.

before - ("select count(*) as total from " . TABLE_TOPICS . " where parent_id =
after - ("select count(*) as total from (" . TABLE_TOPICS . ") where parent_id =
-----

So I did as stated, searched all "from"s on articles.tpl.php and put the () on them. There are I think 13 of them, I do not know if I needed the () on all of them, but everything seems to be working now.

[michael_s]

Hey,

I believe this is patched in SVN... You may want to grab the fileset. You could also try the articles update by JPF which is in a nice neat package for you (instead of fussing with SVN repository):

219: Problems with Article not showing up when clicking on them - Bug Tracker - open source Commerce Maximized :: osCMax

[MindTwist]

Hope I haven't messed up and it can help!

Articles.tpl.php from /fallback (lines should match for an unmodified template)

Line 6:

    $topic_query = tep_db_query("select td.topics_name, td.topics_heading_title, td.topics_description from (" . TABLE_TOPICS . " t, " . TABLE_TOPICS_DESCRIPTION . " td ) where t.topics_id = '" . (int)$current_topic_id . "' and td.topics_id = '" . (int)$current_topic_id . "' and td.language_id = '" . (int)$languages_id . "'");

Line 56:

          $topics_query = tep_db_query("select t.topics_id, td.topics_name, t.parent_id from (" . TABLE_TOPICS . " t, " . TABLE_TOPICS_DESCRIPTION . " td ) where t.parent_id = '" . (int)$topic_links[$i] . "' and t.topics_id = td.topics_id and td.language_id = '" . (int)$languages_id . "' order by sort_order, td.topics_name");

Line 61:

      $topics_query = tep_db_query("select t.topics_id, td.topics_name, t.parent_id from (" . TABLE_TOPICS . " t, " . TABLE_TOPICS_DESCRIPTION . " td ) where t.parent_id = '" . (int)$current_topic_id . "' and t.topics_id = td.topics_id and td.language_id = '" . (int)$languages_id . "' order by sort_order, td.topics_name");

Line 100:

    $topic_query = tep_db_query("select td.topics_name, td.topics_heading_title, td.topics_description from (" . TABLE_TOPICS . " t, " . TABLE_TOPICS_DESCRIPTION . " td ) where t.topics_id = '" . (int)$current_topic_id . "' and td.topics_id = '" . (int)$current_topic_id . "' and td.language_id = '" . (int)$languages_id . "'");

Line 108:

        $listing_sql = "select a.articles_id, a.authors_id, a.articles_date_added, ad.articles_name, ad.articles_head_desc_tag, au.authors_name, td.topics_name, a2t.topics_id from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_DESCRIPTION . " ad ) left join " . TABLE_AUTHORS . " au on a.authors_id = au.authors_id, " . TABLE_ARTICLES_TO_TOPICS . " a2t left join " . TABLE_TOPICS_DESCRIPTION . " td on a2t.topics_id = td.topics_id where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_status = '1' and au.authors_id = '" . (int)$HTTP_GET_VARS['authors_id'] . "' and a.articles_id = a2t.articles_id and ad.articles_id = a2t.articles_id and ad.language_id = '" . (int)$languages_id . "' and td.language_id = '" . (int)$languages_id . "' and a2t.topics_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' order by a.articles_date_added desc, ad.articles_name";

Line 111:

        $listing_sql = "select a.articles_id, a.authors_id, a.articles_date_added, ad.articles_name, ad.articles_head_desc_tag, au.authors_name, td.topics_name, a2t.topics_id from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_DESCRIPTION . " ad ) left join " . TABLE_AUTHORS . " au on a.authors_id = au.authors_id, " . TABLE_ARTICLES_TO_TOPICS . " a2t left join " . TABLE_TOPICS_DESCRIPTION . " td on a2t.topics_id = td.topics_id where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_status = '1' and au.authors_id = '" . (int)$HTTP_GET_VARS['authors_id'] . "' and a.articles_id = a2t.articles_id and ad.articles_id = a2t.articles_id and ad.language_id = '" . (int)$languages_id . "' and td.language_id = '" . (int)$languages_id . "' order by a.articles_date_added desc, ad.articles_name";

Line 117:

        $listing_sql = "select a.articles_id, a.authors_id, a.articles_date_added, ad.articles_name, ad.articles_head_desc_tag, au.authors_name, td.topics_name, a2t.topics_id from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_DESCRIPTION . " ad ) left join " . TABLE_AUTHORS . " au on a.authors_id = au.authors_id, " . TABLE_ARTICLES_TO_TOPICS . " a2t left join " . TABLE_TOPICS_DESCRIPTION . " td on a2t.topics_id = td.topics_id where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_status = '1' and a.articles_id = a2t.articles_id and ad.articles_id = a2t.articles_id and ad.language_id = '" . (int)$languages_id . "' and td.language_id = '" . (int)$languages_id . "' and a2t.topics_id = '" . (int)$current_topic_id . "' and au.authors_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' order by a.articles_date_added desc, ad.articles_name";

Line 120:

        $listing_sql = "select a.articles_id, a.authors_id, a.articles_date_added, ad.articles_name, ad.articles_head_desc_tag, au.authors_name, td.topics_name, a2t.topics_id from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_DESCRIPTION . " ad ) left join " . TABLE_AUTHORS . " au on a.authors_id = au.authors_id, " . TABLE_ARTICLES_TO_TOPICS . " a2t left join " . TABLE_TOPICS_DESCRIPTION . " td on a2t.topics_id = td.topics_id where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_status = '1' and a.articles_id = a2t.articles_id and ad.articles_id = a2t.articles_id and ad.language_id = '" . (int)$languages_id . "' and td.language_id = '" . (int)$languages_id . "' and a2t.topics_id = '" . (int)$current_topic_id . "' order by a.articles_date_added desc, ad.articles_name";

Line 138:

               $author_query = tep_db_query("select au.authors_name, aui.authors_description, aui.authors_url from (" . TABLE_AUTHORS . " au, " . TABLE_AUTHORS_INFO . " aui ) where au.authors_id = '" . (int)$HTTP_GET_VARS['authors_id'] . "' and au.authors_id = aui.authors_id and aui.languages_id = '" . (int)$languages_id . "'");

Line 154:

        $filterlist_sql = "select distinct t.topics_id as id, td.topics_name as name from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t, " . TABLE_TOPICS . " t, " . TABLE_TOPICS_DESCRIPTION . " td ) where a.articles_status = '1' and a.articles_id = a2t.articles_id and a2t.topics_id = t.topics_id and a2t.topics_id = td.topics_id and td.language_id = '" . (int)$languages_id . "' and a.authors_id = '" . (int)$HTTP_GET_VARS['authors_id'] . "' order by td.topics_name";

Line 156:

        $filterlist_sql= "select distinct au.authors_id as id, au.authors_name as name from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t, " . TABLE_AUTHORS . " au ) where a.articles_status = '1' and a.authors_id = au.authors_id and a.articles_id = a2t.articles_id and a2t.topics_id = '" . (int)$current_topic_id . "' order by au.authors_name";

Line 246:

  $articles_all_query_raw = "select a.articles_id, a.articles_date_added, ad.articles_name, ad.articles_head_desc_tag, au.authors_id, au.authors_name, td.topics_id, td.topics_name from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t ) left join " . TABLE_TOPICS_DESCRIPTION . " td on a2t.topics_id = td.topics_id left join " . TABLE_AUTHORS . " au on a.authors_id = au.authors_id, " . TABLE_ARTICLES_DESCRIPTION . " ad where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_id = a2t.articles_id and a.articles_status = '1' and a.articles_id = ad.articles_id and ad.language_id = '" . (int)$languages_id . "' and td.language_id = '" . (int)$languages_id . "' order by a.articles_date_added desc, ad.articles_name";

Articles_new.tpl.php from /fallback (lines should match for an unmodified template)

Line 17:

  $articles_new_query_raw = "select a.articles_id, a.articles_date_added, ad.articles_name, ad.articles_head_desc_tag, au.authors_id, au.authors_name, td.topics_id, td.topics_name from (" . TABLE_ARTICLES . " a, " . TABLE_ARTICLES_TO_TOPICS . " a2t ) left join " . TABLE_TOPICS_DESCRIPTION . " td on a2t.topics_id = td.topics_id left join " . TABLE_AUTHORS . " au on a.authors_id = au.authors_id, " . TABLE_ARTICLES_DESCRIPTION . " ad where (a.articles_date_available IS NULL or to_days(a.articles_date_available) <= to_days(now())) and a.articles_id = a2t.articles_id and a.articles_status = '1' and a.articles_id = ad.articles_id and ad.language_id = '" . (int)$languages_id . "' and td.language_id = '" . (int)$languages_id . "' and a.articles_date_added > SUBDATE(now( ), INTERVAL '" . NEW_ARTICLES_DAYS_DISPLAY . "' DAY) order by a.articles_date_added desc, ad.articles_name";

[MindTwist]

Originally Posted by michael_s

Hey,

I believe this is patched in SVN... You may want to grab the fileset. You could also try the articles update by JPF which is in a nice neat package for you (instead of fussing with SVN repository):

219: Problems with Article not showing up when clicking on them - Bug Tracker - open source Commerce Maximized :: osCMax

Oh well, I might have wasted some time then... I suppose I am using Article Manager 1.0. It seems to be working great now, so I am kinda afraid of updating it

I did see a nice mod for being able to add page breaks to the articles (make them multi-page), I will be trying that one out.

[MindTwist]

michael_s,
For whatever reason, that didn't seem to solve it for me. I tried using the update you pointed me too, but the files on /template still seem to be giving me problems and I got the same 1054 error. When using the template I had changed, everything seemed to go ok