SQL Error

The GYM Sitemaps & RSS module for phpBB. Sitemaps and RSS feeds for Google Yahoo! and MSN Live, support, add ons etc ...

Moderator: Moderators

SQL Error

Postby Leahcim » Mon Feb 09, 2009 8:47 pm

Hi

I receive following error when i press the RSS link on the Index page.

SQL ERROR [ mssql_odbc ]
[Microsoft][ODBC SQL Server Driver][SQL Server]Column "phpbb_topics.topic_last_post_id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. [37000]

Does anyone know what this can be?

/Mike
Leahcim
 
Posts: 8
Joined: Mon Feb 09, 2009 8:31 pm
Location: Gävle, Sweden

Advertisement

Postby dcz » Tue Feb 10, 2009 4:59 pm

mm, unfortunately, we never tried the mod with mssql_odbc, it would be a good occasion though.
I'm pretty sure it deals with the pretty complex query in gym_sitemaps/modules/rss_forum.php (list_topics()).

I need to knwo just a bit more before I get into it.
Could you tell me what options are you using, as far as showing first post, last post or both in feeds (for topics obviously) and have you tried the available combos for these ?
What if you try a news feed ?

Ultimately, do you have a bit more data for me to see in your error message (activating debug in config.php should help).

++
Useful links :
SEO Forum || SEO Directory || SEO phpBB || Search
____________________

Liens Utiles :
Forum référencement || Annuaire référencement || Référencement phpBB || Recherche
dcz
Admin
Admin
 
Posts: 21393
Joined: Fri Apr 28, 2006 9:03 pm

Postby Leahcim » Tue Feb 10, 2009 5:52 pm

This is the debug:

[Microsoft][ODBC SQL Server Driver][SQL Server]Column "phpbb_topics.topic_last_post_id" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause. [37000]

SQL

SELECT COUNT(topic_id) AS topic FROM phpbb_topics WHERE forum_id IN (4, 6, 7, 8, 9) AND topic_type <> 3 AND topic_status <> 2 ORDER BY topic_last_post_id DESC

BACKTRACE


FILE: includes/db/mssql_odbc.php
LINE: 165
CALL: dbal->sql_error()

FILE: gym_sitemaps/modules/rss_forum.php
LINE: 207
CALL: dbal_mssql_odbc->sql_query()

FILE: gym_sitemaps/includes/gym_sitemaps.php
LINE: 182
CALL: rss_forum->rss_main()

FILE: gym_sitemaps/includes/gym_sitemaps.php
LINE: 165
CALL: gym_sitemaps->load_module()

FILE: gym_sitemaps/includes/gym_rss.php
LINE: 318
CALL: gym_sitemaps->load_modules()

FILE: gym_sitemaps/includes/gym_rss.php
LINE: 128
CALL: gym_rss->rss_output()

FILE: gymrss.php
LINE: 21
CALL: gym_rss->gym_rss()

Where do i find the options?

/Mike
Leahcim
 
Posts: 8
Joined: Mon Feb 09, 2009 8:31 pm
Location: Gävle, Sweden

Postby dcz » Tue Feb 10, 2009 7:48 pm

Ho, I think that we found something to optimize here, try to replace :
Code: Select all
         $sql = "SELECT COUNT(topic_id) AS topic
            FROM " . TOPICS_TABLE . "
            WHERE $time_limit_sql
               forum_id " . $this->actions['in_id_sql'] . "
               AND topic_type <> " . POST_GLOBAL . "
               AND topic_status <> " . ITEM_MOVED . "
            ORDER BY topic_last_post_id " . $this->module_config['rss_sort'];

with :
Code: Select all
         $sql = "SELECT COUNT(topic_id) AS topic
            FROM " . TOPICS_TABLE . "
            WHERE $time_limit_sql
               forum_id " . $this->actions['in_id_sql'] . "
               AND topic_type <> " . POST_GLOBAL . "
               AND topic_status <> " . ITEM_MOVED;

and :
Code: Select all
                  $sql = "SELECT COUNT(topic_id) AS forum_topics
                     FROM " . TOPICS_TABLE . "
                     WHERE forum_id = $forum_id
                        topic_last_post_time > $time_limit
                        AND topic_type <> " . POST_GLOBAL . "
                        AND topic_status <> " . ITEM_MOVED . "
                        $approve_sql
                     ORDER BY topic_last_post_id " . $this->module_config['rss_sort'];

with :
Code: Select all
                  $sql = "SELECT COUNT(topic_id) AS forum_topics
                     FROM " . TOPICS_TABLE . "
                     WHERE forum_id = $forum_id
                        topic_last_post_time > $time_limit
                        AND topic_type <> " . POST_GLOBAL . "
                        AND topic_status <> " . ITEM_MOVED . "
                        $approve_sql";

and then :
Code: Select all
            $sql = "SELECT COUNT(topic_id) AS topic
               FROM " . TOPICS_TABLE . " t
               WHERE $time_limit_sql
                  topic_type <> " . POST_GLOBAL . "
                  AND topic_status <> " . ITEM_MOVED . "
                  AND forum_id " . $this->actions['in_id_sql'] . "
               ORDER BY t.topic_last_post_id " . $this->module_config['rss_sort'];

with :
Code: Select all
            $sql = "SELECT COUNT(topic_id) AS topic
               FROM " . TOPICS_TABLE . " t
               WHERE $time_limit_sql
                  topic_type <> " . POST_GLOBAL . "
                  AND topic_status <> " . ITEM_MOVED . "
                  AND forum_id " . $this->actions['in_id_sql'];

in gym_sitemaps/modules/rss_forum.php.

Could only be it since there is no need to sort a SELECT COUNT anyway :roll:

++
Useful links :
SEO Forum || SEO Directory || SEO phpBB || Search
____________________

Liens Utiles :
Forum référencement || Annuaire référencement || Référencement phpBB || Recherche
dcz
Admin
Admin
 
Posts: 21393
Joined: Fri Apr 28, 2006 9:03 pm

Postby Leahcim » Tue Feb 10, 2009 8:22 pm

Thanks

Now it works ok.

/Mike
Leahcim
 
Posts: 8
Joined: Mon Feb 09, 2009 8:31 pm
Location: Gävle, Sweden


Return to GYM Sitemaps & RSS

 


  • Related topics
    Replies
    Views
    Last post

Who is online

Users browsing this forum: No registered users and 2 guests