Announcement Module
No announcement yet.

Query OK in localhost, error on ISP server.

Page Title Module
Move Remove Collapse
Conversation Detail Module
  • 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: or

    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


    count(distinct as schools,
    count(distinct as topics,
    count(distinct as msgs,
    sum(distinct threads.views) as views,
    ((count(distinct *2) +sum(distinct threads.views) ) as activity,
    0 as hBarLength
    FROM ((conferences
    left JOIN forums ON = forums.conferenceidfk)
    left JOIN threads = threads.forumidfk and <> 'admin')
    left JOIN messages ON = messages.threadidfk and <> 'admin'

  • #2
    1) There is mising space in line "left JOIN threads = threads.forumidfk and <> 'admin') " between "ON" and "".
    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.


    • #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.


      • #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 = 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