Announcement

Announcement Module
Collapse
No announcement yet.

Query OK in localhost, error on ISP server.

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Query OK in localhost, error on ISP server.

    The following query run flawlessly in localhost but produces error on ISP server:
    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'distinct sf_threads.views) as views, ((count(distinct sf_messages.' at line 6

    The actual behaviour here:
    http://eduforums.us or
    http://wconti.com/schoolsforums/index.cfm

    I am using the exact same database both in local and server.
    Running MySQL 5 in localhost and supposedly versions 4 and 5 sopported by the ISP server.

    Thanks for helping

    ------------

    SELECT
    conferences.id,
    conferences.name,
    count(distinct forums.id)-1 as schools,
    count(distinct threads.id) as topics,
    count(distinct messages.id) as msgs,
    sum(distinct threads.views) as views,
    ((count(distinct messages.id) *2) +sum(distinct threads.views) ) as activity,
    0 as hBarLength
    FROM ((conferences
    left JOIN forums ON conferences.id = forums.conferenceidfk)
    left JOIN threads ONforums.id = threads.forumidfk and threads.author <> 'admin')
    left JOIN messages ON threads.id = messages.threadidfk and messages.author <> 'admin'
    GROUP BY conferences.id

  • #2
    1) There is mising space in line "left JOIN threads ONforums.id = threads.forumidfk and threads.author <> 'admin') " between "ON" and "forums.id".
    2) Count with distinct syntax you are using, works in MySQL 5.x, and looks like your ISP has 4.x. Please check if it's possible to switch you hosting account to MySQL 5.x.

    Comment


    • #3
      Thanx very much Mikhail.
      The space is OK in the real query, but you are probably right for the version issue. I will request my ISP to switch to a mySQL 5. server.

      Comment


      • #4
        Complementing the post above:

        I have found that SUM(DISTINCT xxx) is valid with v.5x, not so in v.4x.
        While I am trying to convince my ISP to switch the database to a v.5x server, I would like some help with a workaround - please excuse my newbness. I have tried :

        (select sum(threads.views) from threads where threads.forumidfk = forums.id) as views,

        instead of :
        sum(distinct threads.views) as views,

        but it returns a zero flat instead of, say, 8, (5+3).

        Thank You.
        Walter conti

        Comment

        Working...
        X