SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Query Fails On Mysql 5, Works On Mysql 4

    Strange. I'm curious, what is it about this query that trips up mysql 5? I use:

    • Server version: 5.0.91-community

    I notice there were bugs in earlier versions. Maybe my host just for this test server needs to update mysql 5?
    http://bugs.mysql.com/bug.php?id=13551

    Query Below:

    Code:
    SELECT music_albums.*,users.username,inventory.stock,inventory.upc_code,          DATE_FORMAT(release_date,'%M %d, %Y')  AS rel FROM  music_albums,users LEFT JOIN inventory ON (inventory.album_id=music_albums.id)   WHERE users.uid=music_albums.artist_id AND  music_albums.id='8'
    This has worked perfectly on mysql 4. Now, with mysql 5+, it gives this error:

    #1054 - Unknown column 'music_albums.id' in 'on clause'

    How does it not recognize the column music_albums.id ? What do I have to do differently, from now on, for mysql 5? I am testing our site on php 5/mysql 5 to make sure our site won't break before we update our servers.

    Thanks.

    PS. Edit: I see it was a feature change and am reading the mysql docs but I think I need coffee to understand it...but for those going through the same thing, look at this page:

    http://dev.mysql.com/doc/refman/5.0/en/join.html

  2. #2
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Fixed it with a lame query (for now);

    SELECT music_albums.*,users.username,inventory.stock,inventory.upc_code, DATE_FORMAT(release_date,'%M %d, %Y') AS rel FROM music_albums,users,inventory WHERE users.uid=music_albums.artist_id AND music_albums.id='8' limit 1

    I hope this helps others who are stuck.

  3. #3
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    get out of the habit of using comma join syntax. stick specifically with INNER and OUTER joins. The problem you encounter is mysql 5 being more conforming with sql standards.
    the comma takes precedence over the LEFT (OUTER) join and thus it can't find the column inventory.album_id because it is only looking at music_albums and users and trying to perform that join first.

    Code:
    SELECT 
      music_albums.*,
      users.username,
      inventory.stock,
      inventory.upc_code,          
      DATE_FORMAT(release_date,'%M %d, %Y')  AS rel 
    FROM  
      music_albums
    INNER JOIN
      users 
    ON
      users.uid=music_albums.artist_id
    LEFT JOIN inventory 
    ON 
      inventory.album_id=music_albums.id   
    WHERE
      music_albums.id=8

  4. #4
    SitePoint Guru
    Join Date
    Mar 2002
    Posts
    608
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you for this. I have to re-wire my brain for mysql 5. It should be a tremendous learning experience.


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
  •