Help with Mysql 5 Statement update

[fourmat]

Hi Guys,

I upgraded my web server and have since had to update to mysql 5. As I'm sure much of you have discovered you'll get some '1054 Unknown column 'p.products_id' in 'on clause' errors. I have fixed all of the areas where the problems have popped up, except for one, which is a query from my own highly modified version of featured_products.php I grew enough talent to create the query at the time, but apparently my talent has left. I have beat myself up to try to figure out what the correct new syntax for this query is, but I'm missing something. Can someone take a look and nudge me in the right direction? Thanks.

select distinct p.products_id, p.products_quantity, p.products_image, m.manufacturers_id, m.manufacturers_image, m.manufacturers_name, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price, p.products_price,  d.short_desc            from " . TABLE_PRODUCTS . " as p left join " . TABLE_SPECIALS . " as s on (p.products_id = s.products_id), " . TABLE_PRODUCTS_TO_CATEGORIES . " as p2c, " . TABLE_CATEGORIES . " as c left join " . TABLE_FEATURED . " f on (p.products_id = f.products_id) left join " . TABLE_MANUFACTURERS . "  m on (p.manufacturers_id = m.manufacturers_id) left join " . TABLE_PRODUCTS_DESCRIPTION . " d on (p.products_id = d.products_id) where p.products_id = p2c.products_id and d.products_id = p2c.products_id and f.products_id = p2c.products_id and s.products_id = p2c.products_id and p2c.categories_id = c.categories_id and c.parent_id = '" . $featured_products_category_id . "' and p.products_status = '1' and f.status = '1' order by rand() DESC limit " . MAX_DISPLAY_FEATURED_PRODUCTS

[icecold]

Hi, take a look at this query and you will see you have ( in the wrong places in your query:

PHP Code:

select distinct p.products_image, p.products_weight, m.manufacturers_name, m.manufacturers_id, p.products_id, pd.products_name from (products p left join products_to_products_extra_fields p2pef on p.products_id=p2pef.products_id) left join manufacturers m ON p.manufacturers_id = m.manufacturers_id left join specials s on p.products_id = s.products_id, products_description pd, categories c, products_to_categories p2c where p.products_status = '1' and p.products_id = pd.products_id and pd.language_id = '3' and p.products_id = p2c.products_id and p2c.categories_id = c.categories_id and m.manufacturers_id = '1' and ((pd.products_name like '%rubia%' or m.manufacturers_name like '%rubia%' or p2pef.products_extra_fields_value like '%rubia%' or pd.products_description like '%rubia%') ) order by pd.products_name 


[fourmat]

Bit late, but thanks for the help! I didn't realize that I didn't thank you previously...got so tied up in getting my shop working again.

[jpf]

Please note: If you using OSCMAX RC3 or earlier - it is NOT PHP version 5 or MySQL version 5 compatibly! (Please review compatibly in the specification.)

However OSCMAX RC4 is compatible (but is currently only available as a beta in SVN).

[fourmat]

We just went through an security audit where the major credit card companies are requiring that my clients are conforming to a new universal security standard that was approved by all of the major CC companies.

As part of this new security standard, they are requiring that the web servers be brought up to date with the latest PHP releases. So this breaks a lot of scripts. This will eventually effect EVERYONE who runs an OSC shop and also offers credit cards as a form of payment.

Credit-card security standard issued after much debate - Network World