Professional Web Applications Themes

MySQL 5 upgrade - MySQL

Hi all, I've just moved my development server to FC5 which includes MySQL 5. Everything is fine in my old apps except this one query!! SELECT DISTINCT n.nav_order, n.cat_id, IF((LENGTH(c.category) > 0),c.category,s.title) as title, IF((LENGTH(c.category) > 0),'',n.page_id) as page_id FROM navigation n, story s LEFT JOIN categories c ON c.cat_id = n.cat_id WHERE nav_order IS NOT NULL AND n.page_id = s.page_uid ORDER BY nav_order It used to work just fine but now it get the following error: Couldn't execute navigation query.Unknown column 'n.cat_id' in 'on clause' Any ideas what's wrong? TIA Huw...

  1. #1

    Default MySQL 5 upgrade

    Hi all,

    I've just moved my development server to FC5 which includes MySQL 5.
    Everything is fine in my old apps except this one query!!

    SELECT DISTINCT n.nav_order,
    n.cat_id,
    IF((LENGTH(c.category) > 0),c.category,s.title) as
    title,
    IF((LENGTH(c.category) > 0),'',n.page_id) as page_id
    FROM navigation n,
    story s
    LEFT JOIN categories c ON c.cat_id = n.cat_id
    WHERE nav_order IS NOT NULL AND
    n.page_id = s.page_uid
    ORDER BY nav_order

    It used to work just fine but now it get the following error:
    Couldn't execute navigation query.Unknown column 'n.cat_id' in 'on
    clause'

    Any ideas what's wrong?

    TIA

    Huw

    huwpioden@gmail.com Guest

  2. #2

    Default Re: MySQL 5 upgrade

    [email]huwpioden[/email] wrote:
    > Hi all,
    >
    > I've just moved my development server to FC5 which includes MySQL 5.
    > Everything is fine in my old apps except this one query!!
    >
    > SELECT DISTINCT n.nav_order,
    > n.cat_id,
    > IF((LENGTH(c.category) > 0),c.category,s.title) as
    > title,
    > IF((LENGTH(c.category) > 0),'',n.page_id) as page_id
    > FROM navigation n,
    > story s
    > LEFT JOIN categories c ON c.cat_id = n.cat_id
    > WHERE nav_order IS NOT NULL AND
    > n.page_id = s.page_uid
    > ORDER BY nav_order
    >
    > It used to work just fine but now it get the following error:
    > Couldn't execute navigation query.Unknown column 'n.cat_id' in 'on
    > clause'
    >
    > Any ideas what's wrong?
    >
    > TIA
    >
    > Huw
    Try bracketing the FROM tables thus
    FROM (navigation n,
    story s)
    LEFT JOIN


    Paul Lautman Guest

  3. #3

    Default Re: MySQL 5 upgrade

    On Tue, 30 May 2006 19:14:07 +0100, "Paul Lautman"
    <paul.lautmanbtinternet.com> wrote:
    >huwpioden wrote:
    (...)
    >> FROM navigation n,
    >> story s
    (...)
    >Try bracketing the FROM tables thus
    >FROM (navigation n,
    > story s)
    > LEFT JOIN
    ....or say

    FROM navigation AS n, story
    --
    Paweł
    twierdza konserwy polskiej fizyki
    PFG Guest

  4. #4

    Default Re: MySQL 5 upgrade

    [email]huwpioden[/email] wrote:
    > FROM navigation n,
    > story s
    > LEFT JOIN categories c ON c.cat_id = n.cat_id
    MySQL 5.0.12 changed the precedence of comma-style joins vs. JOIN
    syntax. Read more about that under the heading "Join Processing Changes
    in MySQL 5.0.12" on this page:
    [url]http://dev.mysql.com/doc/refman/5.0/en/join.html[/url]

    Anyway, it's better to use JOIN syntax throughout, for better
    readability and predictable results:

    FROM navigation n,
    JOIN story s ON n.page_id = s.page_uid
    LEFT JOIN categories c ON c.cat_id = n.cat_id

    Regards,
    Bill K.
    Bill Karwin Guest

  5. #5

    Default Re: MySQL 5 upgrade

    On Tue, 30 May 2006 20:27:32 +0200, PFG
    <goranotthispart.if.uj.edu.pl> wrote:
    >On Tue, 30 May 2006 19:14:07 +0100, "Paul Lautman"
    ><paul.lautmanbtinternet.com> wrote:
    >
    >>huwpioden wrote:
    >
    >(...)
    >>> FROM navigation n,
    >>> story s
    >(...)
    >
    >>Try bracketing the FROM tables thus
    >>FROM (navigation n,
    >> story s)
    >> LEFT JOIN
    >
    >...or say
    >
    >FROM navigation AS n, story
    Oops, a fragmented post. What I intended to say was:

    .... or say

    FROM navigation AS n, story AS s
    LEFT JOIN
    etc

    --
    Pawel
    PFG Guest

Similar Threads

  1. Upgrade to MySQL 5.n ?
    By Marco A. Cruz Quevedo in forum MySQL
    Replies: 2
    Last Post: June 5th, 04:40 PM
  2. CF 7/MySQL upgrade nightmare!
    By Chris Vinson in forum Coldfusion Database Access
    Replies: 0
    Last Post: March 11th, 05:00 PM
  3. PHP & MySQL Upgrade
    By Jeremy Ross in forum PHP Development
    Replies: 1
    Last Post: September 29th, 06:19 PM
  4. [PHP] RH9.0: Upgrade to 4.3, mysql functions not working
    By Jay Blanchard in forum PHP Development
    Replies: 1
    Last Post: September 5th, 08:02 PM
  5. RH9.0: Upgrade to 4.3, mysql functions not working
    By Wouter Van Vliet in forum PHP Development
    Replies: 1
    Last Post: September 5th, 06:59 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139