Professional Web Applications Themes

Query problem moving from mysql 4 to 5 - MySQL

I am currently developing a website that is used to display college course information. I have everything running exactly as intended on my server which utilizes Mysql 4.1. However, I recently uploaded it to the college server which has Mysql 5.0 on it. The query that I am having problems with is as follows: SELECT DISTINCT course.id, course.courseid, course.subject, course.coursenumber, course.section, course.title, course.credits, course.type, FORMAT(course.taken, 0), FORMAT(course.total, 0), course.extrainfo, course.gradelevel, course.semester, DATE_FORMAT(FROM_UNIXTIME(courseinfo.starttime), "%h:%i %p"), DATE_FORMAT(FROM_UNIXTIME(courseinfo.endtime), "%h:%i %p"), courseinfo.days, courseinfo.faculty, courseinfo.room, dept.title, DATE_FORMAT(FROM_UNIXTIME(courseinfo.starttime), "%m/%e/%Y"), DATE_FORMAT(FROM_UNIXTIME(courseinfo.endtime), "%m/%e/%Y"), DATE_FORMAT(FROM_UNIXTIME(semester.startdate), "%m/%e/%Y"), DATE_FORMAT(FROM_UNIXTIME(semester.enddate), "%m/%e/%Y") FROM course INNER JOIN courseinfo INNER JOIN dept INNER JOIN semester ...

  1. #1

    Default Query problem moving from mysql 4 to 5

    I am currently developing a website that is used to display college
    course information. I have everything running exactly as intended on my
    server which utilizes Mysql 4.1. However, I recently uploaded it to the
    college server which has Mysql 5.0 on it.

    The query that I am having problems with is as follows:

    SELECT
    DISTINCT course.id,
    course.courseid,
    course.subject,
    course.coursenumber,
    course.section,
    course.title,
    course.credits,
    course.type,
    FORMAT(course.taken, 0),
    FORMAT(course.total, 0),
    course.extrainfo,
    course.gradelevel,
    course.semester,
    DATE_FORMAT(FROM_UNIXTIME(courseinfo.starttime), "%h:%i %p"),
    DATE_FORMAT(FROM_UNIXTIME(courseinfo.endtime), "%h:%i %p"),
    courseinfo.days,
    courseinfo.faculty,
    courseinfo.room,
    dept.title,
    DATE_FORMAT(FROM_UNIXTIME(courseinfo.starttime), "%m/%e/%Y"),
    DATE_FORMAT(FROM_UNIXTIME(courseinfo.endtime), "%m/%e/%Y"),
    DATE_FORMAT(FROM_UNIXTIME(semester.startdate), "%m/%e/%Y"),
    DATE_FORMAT(FROM_UNIXTIME(semester.enddate), "%m/%e/%Y")
    FROM
    course
    INNER JOIN courseinfo
    INNER JOIN dept
    INNER JOIN semester
    ON semester.id = course.semester
    ON dept.subject = course.subject
    ON courseinfo.courseid = course.courseid
    AND courseinfo.semester = course.semester
    WHERE
    course.semester = 2
    AND (course.subject = 'CSCI')
    ORDER BY
    dept.title ASC,
    course.subject ASC,
    course.coursenumber ASC,
    course.section ASC

    The error that is currently being reported is:

    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 'ON
    dept.subject = course.subject ON courseinfo.courseid = course.courseid
    A' at line 1

    Could someone point me in the right direction or give me some insight as
    to what is happening here? TIA!

    --
    Justin Koivisto, ZCE - [email]justinkoivi.com[/email]
    [url]http://koivi.com[/url]
    Justin Koivisto Guest

  2. #2

    Default Re: Query problem moving from mysql 4 to 5

    "Justin Koivisto" <justinkoivi.com> wrote in message
    news:xbqdndSdwOnTdWTe4p2dnAonvoy.com...
    > FROM
    > course
    > INNER JOIN courseinfo
    > INNER JOIN dept
    > INNER JOIN semester
    > ON semester.id = course.semester
    > ON dept.subject = course.subject
    > ON courseinfo.courseid = course.courseid
    > AND courseinfo.semester = course.semester
    >
    > The error that is currently being reported is:
    >
    > 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 'ON
    > dept.subject = course.subject ON courseinfo.courseid = course.courseid
    > A' at line 1
    I don't usually see join syntax like yours; I'm not sure why it worked on
    MySQL 4.1.

    I'd use this form:
    FROM course
    INNER JOIN courseinfo ON courseinfo.courseid = course.courseid
    INNER JOIN dept ON dept.subject = course.subject
    INNER JOIN semester ON semester.id = course.semester AND
    courseinfo.semester = course.semester

    You may also need to use parentheses to force appropriate order of
    evaluation of the joins. There has been a change in MySQL 5.0 that affects
    precedence of JOIN operations, to bring MySQL closer to the ANSI SQL
    standard.

    See the discussion in this thread:
    [url]http://groups.google.com/group/mailing.database.mysql/browse_thread/thread/eee40e1bd0a0464b/[/url]

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Query problem moving from mysql 4 to 5

    Bill Karwin wrote:
    > "Justin Koivisto" <justinkoivi.com> wrote in message
    > news:xbqdndSdwOnTdWTe4p2dnAonvoy.com...
    >> FROM
    >> course
    >> INNER JOIN courseinfo
    >> INNER JOIN dept
    >> INNER JOIN semester
    >> ON semester.id = course.semester
    >> ON dept.subject = course.subject
    >> ON courseinfo.courseid = course.courseid
    >> AND courseinfo.semester = course.semester
    >>
    >> The error that is currently being reported is:
    >>
    >> 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 'ON
    >> dept.subject = course.subject ON courseinfo.courseid = course.courseid
    >> A' at line 1
    >
    > I don't usually see join syntax like yours; I'm not sure why it worked on
    > MySQL 4.1.
    >
    > I'd use this form:
    > FROM course
    > INNER JOIN courseinfo ON courseinfo.courseid = course.courseid
    > INNER JOIN dept ON dept.subject = course.subject
    > INNER JOIN semester ON semester.id = course.semester AND
    > courseinfo.semester = course.semester
    >
    > You may also need to use parentheses to force appropriate order of
    > evaluation of the joins. There has been a change in MySQL 5.0 that affects
    > precedence of JOIN operations, to bring MySQL closer to the ANSI SQL
    > standard.
    >
    > See the discussion in this thread:
    > [url]http://groups.google.com/group/mailing.database.mysql/browse_thread/thread/eee40e1bd0a0464b/[/url]
    Thanks! This appears to be working exactly as the old query was:

    INNER JOIN courseinfo ON courseinfo.courseid = course.courseid AND
    courseinfo.semester = course.semester

    INNER JOIN dept ON dept.subject = course.subject
    INNER JOIN semester ON semester.id = course.semester

    --
    Justin Koivisto, ZCE - [email]justinkoivi.com[/email]
    [url]http://koivi.com[/url]
    Justin Koivisto Guest

Similar Threads

  1. mysql++ i mysqlpp::Query problem
    By jaccurek@op.pl in forum MySQL
    Replies: 1
    Last Post: July 26th, 10:02 AM
  2. mySQL query writing problem
    By JackM in forum MySQL
    Replies: 2
    Last Post: July 16th, 02:18 AM
  3. MySQL Query Problem
    By cf101 in forum Coldfusion Database Access
    Replies: 7
    Last Post: June 10th, 07:45 PM
  4. Problem with MySQL Query
    By Phillip Blancher in forum PHP Development
    Replies: 13
    Last Post: January 14th, 09:14 AM
  5. PHP/MySQL Query Problem in Dreamweaver
    By Aeris Leonna in forum PHP Development
    Replies: 2
    Last Post: October 20th, 03:22 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