SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Convert a MySQL 4+ query to MySQL

    Hi there,

    Hoping someone can help me out converting a query so that it works in MySQL 3.23 - it includes a sub select so I believe that's what the problem is.

    Code:
    SELECT * 
    FROM products_extra_fields pef
    WHERE category_id LIKE '% 3,%'
    OR category_id =3
    OR category_id = 'all'
    OR pef.category_id = ( 
    SELECT c.parent_id
    FROM categories AS c
    WHERE c.categories_id =3 ) 
    ORDER BY products_extra_fields_order
    Is it a case of breaking it up in to two seperate queries?

    Any help much appreciated.

    Cheers,

    DM

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    you're storing a comma-delimited string of values in the category_id column? you are in for more problems than you realize...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Unfortunately this isn't my code and is just an osCommerce contribution.

    I appreciate it probably isn't particularly good, in the same way that the entire application isn't But its what I'm stuck with at the moment. I'm not familiar with spaghetti style PHP or osCommerce, so I'd rather no hack in my own solution at this stage.

    Will the string issue cause problems with MySQL versions also?

    Ta,

    DM

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DangerMouse1981 View Post
    Will the string issue cause problems with MySQL versions also?
    why does it matter? you're going down that road whether it causes problems or not, right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    try this --
    Code:
    SELECT * 
      FROM products_extra_fields AS pef
     WHERE category_id LIKE '% 3,%'
        OR category_id = 3
        OR category_id = 'all'
    UNION ALL
    SELECT * 
      FROM products_extra_fields pef
    INNER
      JOIN categories AS c
        ON c.parent_id = pef.category_id 
     WHERE c.categories_id = 3  
    ORDER 
        BY products_extra_fields_order
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    oh, wait, never mind, UNION isn't supported until 4.0

    listen, running mysql 3.23 on your server is like running windows 3.1 on your pc

    why can't you upgrade?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2008
    Posts
    109
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It looks like that's the best route to take - thanks for helping me out. I was hoping for a quick fix as this issue came from left field.

    I can't see any reason to be running such an antiquated version of MySQL either, and I didn't realise it was until I started to implement this hack. I'm not the original developer of the site, but am simply "tacking" something on to quick fix an issue before migrating to a better ecommerce package.

    Thanks again for your help.

    Cheers,

    DM


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
  •