Convert a MySQL 4+ query to MySQL

[DangerMouse1981]

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.

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

[r937]

you're storing a comma-delimited string of values in the category_id column? you are in for more problems than you realize...

[DangerMouse1981]

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

[r937]

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?

[r937]

try this --

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

[r937]

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?

[DangerMouse1981]

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