mySQL 5 compatibility issue?

[Fredrik.r]

Hi,

 

Need some help with changing this query to work with mySQL 5;

$reviews_query = tep_db_query("select r.reviews_id, rd.reviews_text, rd.reviews_headline, r.reviews_rating, r.date_added, ri.reviews_image from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd left join " . TABLE_REVIEWS_IMAGES . " ri on r.reviews_id = ri.reviews_id and ri.reviews_status = '1' where r.products_id = '" . (int)$product_info['products_id'] . "' and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and r.approved = '1' order by date_added DESC LIMIT " . MAX_REVIEWS);

 

As it is now, it results in;

1054 - Unknown column 'r.reviews_id' in 'on clause'

 

select r.reviews_id, rd.reviews_text, rd.reviews_headline, r.reviews_rating, r.date_added, ri.reviews_image from reviews r, reviews_description rd left join reviews_images ri on r.reviews_id = ri.reviews_id and ri.reviews_status = '1' where r.products_id = '61' and r.reviews_id = rd.reviews_id and rd.languages_id = '4' and r.approved = '1' order by date_added DESC LIMIT 1

 

[TEP STOP]

[Jan Zonjee]

Does "left joining" reviews with reviews_images work?

$reviews_query = tep_db_query("select r.reviews_id, rd.reviews_text, rd.reviews_headline, r.reviews_rating, r.date_added, ri.reviews_image from " . TABLE_REVIEWS . " r left join " . TABLE_REVIEWS_IMAGES . " ri on r.reviews_id = ri.reviews_id, " . TABLE_REVIEWS_DESCRIPTION . " rd where ri.reviews_status = '1' and r.products_id = '" . (int)$product_info['products_id'] . "' and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and r.approved = '1' order by date_added DESC LIMIT " . MAX_REVIEWS);

[MrPhil]

MySQL 5 is incompatible with code written for MySQL 4 and earlier, due to changes to bring it into compliance with SQL standards. If you have a comma-separated list of tables between FROM and a JOIN, you need to wrap the list in parentheses. Find

$reviews_query = tep_db_query("select r.reviews_id, rd.reviews_text, rd.reviews_headline, r.reviews_rating, r.date_added, ri.reviews_image from " . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd left join " . TABLE_REVIEWS_IMAGES . " ri on r.reviews_id = ri.reviews_id and ri.reviews_status = '1' where r.products_id = '" . (int)$product_info['products_id'] . "' and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and r.approved = '1' order by date_added DESC LIMIT " . MAX_REVIEWS);

and change it to

$reviews_query = tep_db_query("select r.reviews_id, rd.reviews_text, rd.reviews_headline, r.reviews_rating, r.date_added, ri.reviews_image from (" . TABLE_REVIEWS . " r, " . TABLE_REVIEWS_DESCRIPTION . " rd) left join " . TABLE_REVIEWS_IMAGES . " ri on r.reviews_id = ri.reviews_id and ri.reviews_status = '1' where r.products_id = '" . (int)$product_info['products_id'] . "' and r.reviews_id = rd.reviews_id and rd.languages_id = '" . (int)$languages_id . "' and r.approved = '1' order by date_added DESC LIMIT " . MAX_REVIEWS);

[Fredrik.r]

Thanks a lot!!

[Guest]

Thanks a lot!!

Was that good or bad?