next »

[jonte_lee]

I can see that many others has got the same problem with mambo-phpshop and mysql 5.
No products is showing after changing to mysql 5.
Is there a real solution to this problem or what?

[Gilad Darshan]

looking for a way to solve that problem aswell, the change of all querys to work with mysql 5 didnt help much

[rexlatour]

We also have about 4 websites that are having problems exactly like this.

If anybody know the solution =, please reply to this.

[dj_mystic82]

Was there ever a solution to this? Our server has just been upgraded to mysql5 and 2 sites have been effected.

[dj_mystic82]

This did the trick:

Code:

//New Code
$list  = "SELECT DISTINCT $fieldnames FROM #__pshop_product LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 $count  = "SELECT $count_name FROM #__pshop_product LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";

// Original Code
 #$list  = "SELECT DISTINCT $fieldnames FROM #__pshop_product, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 #$count  = "SELECT $count_name FROM #__pshop_product, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 #$q  = "LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id ";

[bcause]

What File Is That Code From???

I am having same issue as my hosting (hostgator} just switched to MYSQL 5

[dj_mystic82]

That's a good point! I can't remember off hand. If you do a search on a bit of the old code, you should find it.

[bcause]

i have searched a few dozen php files....   in components phpshop folder, admin/components/phpshop files,  mambo files...  i dont have a clue where to look.

anyone??

[dj_mystic82]

Found it! It was shop.browse.php in aministrator/components/com_phpshop/html

Around line 97. Which has:
/*** GET ALL PUBLISHED PRODUCT WHICH MATCH PARAMETERS ***/

[bcause]

i have been on phone for 2 hrs.  just saw your response . going to give it a shot and will let you know.

[bcause]

not working for me but i am sure its something i am doing wrong.  Maybe i am not seeing all the code you changed.

My original code:

/*** GET ALL PUBLISHED PRODUCT WHICH MATCH PARAMETERS ***/
 $list  = "SELECT DISTINCT $fieldnames FROM #__pshop_product, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 $count  = "SELECT $count_name FROM #__pshop_product, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";

 $q  = "LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id ";
 $q .= "LEFT JOIN #__pshop_product_type_$product_type_id ON #__pshop_product.product_id = #__pshop_product_type_$product_type_id.product_id ";
 $q .= "LEFT JOIN #__pshop_product_product_type_xref ON #__pshop_product.product_id = #__pshop_product_product_type_xref.product_id ";
 $q .= "WHERE #__pshop_product_category_xref.category_id=#__pshop_category.category_id ";
// $q .= "AND #__pshop_product.product_id=#__pshop_product_category_xref.product_id ";
//  $q .= "AND #__pshop_product.product_parent_id='0' ";
 $q .= "AND (#__pshop_product.product_id=#__pshop_product_category_xref.product_id ";
 $q .= "OR #__pshop_product.product_parent_id=#__pshop_product_category_xref.product_id)";
 if( !$perm->check("admin,storeadmin") ) {
    $q .= " AND product_publish='Y'";
    if( CHECK_STOCK && PSHOP_SHOW_OUT_OF_STOCK_PRODUCTS != "1") {
        $q .= " AND product_in_stock > 0 ";


Am I replacing red code with your code below:

$list  = "SELECT DISTINCT $fieldnames FROM #__pshop_product LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 $count  = "SELECT $count_name FROM #__pshop_product LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";

I have tried this but I still have no products

mambo ver 4.5.5
phpshp 1.2 /3

[dj_mystic82]

I've just been going over my notes for this again. Try around line 218 that has " /*** GET ALL PUBLISHED PRODUCTS ***/"

The code is very similar. I have pasted below exactly what was changed :

 $list  = "SELECT DISTINCT $fieldnames FROM #__pshop_product LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 $count  = "SELECT $count_name FROM #__pshop_product LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
// Original Code
 #$list  = "SELECT DISTINCT $fieldnames FROM #__pshop_product, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 #$count  = "SELECT $count_name FROM #__pshop_product, #__pshop_category, #__pshop_product_category_xref,#__pshop_shopper_group ";
 #$q  = "LEFT JOIN #__pshop_product_price ON #__pshop_product.product_id = #__pshop_product_price.product_id ";

[bcause]

That Worked!!!

do u have a paypal account.... would like to buy you a few beers.

I really do appreciate your time and effort to see me thru this.  Now, upgrade to virtuemart for what I am sure will be a whole new set of problems.

Thanks again

[dj_mystic82]

Great. Glad it did the trick.

Sure do. I think you would just need my email address I use for it? dj_mystic82@hotmail.com

[bcause]

Beer Money Sent...