MySQL Forums
Forum List  »  Newbie

'order by' clause not working with 1&1 Hosting
Posted by: James Prozinski
Date: September 15, 2012 04:50PM

Hello all. I am as green as they come when it comes to PHP/MySQL. Trying to learn it to get a better grasp at it. I'm in the process of building an online big/tall men's clothing store
http://www.bigandtallwarehouse.com using Zen Cart and I have 1&1 for my hosting (linux). I installed a module, Dynamic Filter that filters attributes to get to the desired results. For instance, if you select 'Jeans' and then in the filter column, you pick a size..52x32 for instance, the filter works properly and returns the correct search results, which displays: 'Displaying 1 to 7 (of 7 products)'.
But then on some sizes, like 62x32, it shows 'Displaying 1 to 7 (of 7 products)' but instead of displaying all 7 products, it's only displaying just 1 of the 7 products. Very weird indeed. And if I select the size '80x32' it will show 'Displaying 1 to 2 (of 2 products)' but actually be blank and show nothing.

I contacted the guy who wrote the mod for Zen Cart and he had my website on his testing server, which was using a different version of MySQL than 1&1 is using. Currently my database server with 1&1 Hosting is running 5.0. He said it was working fine on his end. He thinks there's a bug with the 'order by' clause with the version I'm running. What is strange, if I go to my phpAdmin and run the SQL query both with and without the 'order by' clause, with the clause it isn't working, but if I remove it, it will display the correct output if you will. Here is the code I used to run the MySQL query, both with and without the 'order by' clause.

SELECT DISTINCT p.products_image, pd.products_name, m.manufacturers_name,
p.products_model, p.products_quantity, p.products_id, p.products_type,
p.master_categories_id, p.manufacturers_id, p.products_price,
p.products_tax_class_id, pd.products_description, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status =1, s.specials_new_products_price,
p.products_price) as
final_price, p.products_sort_order,
p.product_is_call,
p.product_is_always_free_shipping,
p.products_qty_box_status
FROM products p
LEFT JOIN specials s on
p.products_id = s.products_id
LEFT JOIN products_description pd on
p.products_id = pd.products_id
LEFT JOIN manufacturers m on
p.manufacturers_id = m.manufacturers_id JOIN products_to_categories
p2c on p.products_id = p2c.products_id JOIN products_attributes p2a on
p.products_id = p2a.products_id JOIN products_options po on
p2a.options_id = po.products_options_id JOIN products_options_values
pov on p2a.options_values_id = pov.products_options_values_id WHERE
p.products_status = 1 and pd.language_id = '1'
and p2c.categories_id =
'17'
GROUP BY p.products_id
HAVING (

FIND_IN_SET('Sizes80x32',GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name,
' ', ''),pov.products_options_values_name))))
order by
p.products_sort_order, pd.products_name limit 12




Without 'Order by' clause

SELECT DISTINCT p.products_image, pd.products_name,
m.manufacturers_name, p.products_model, p.products_quantity,
p.products_id, p.products_type, p.master_categories_id,
p.manufacturers_id, p.products_price, p.products_tax_class_id,
pd.products_description, IF(s.status = 1,
s.specials_new_products_price, NULL) as specials_new_products_price,
IF(s.status =1, s.specials_new_products_price,
p.products_price) as
final_price, p.products_sort_order,
p.product_is_call,
p.product_is_always_free_shipping,
p.products_qty_box_status
FROM products p
LEFT JOIN specials s on
p.products_id = s.products_id
LEFT JOIN products_description pd on
p.products_id = pd.products_id
LEFT JOIN manufacturers m on
p.manufacturers_id = m.manufacturers_id JOIN products_to_categories
p2c on p.products_id = p2c.products_id JOIN products_attributes p2a on
p.products_id = p2a.products_id JOIN products_options po on
p2a.options_id = po.products_options_id JOIN products_options_values
pov on p2a.options_values_id = pov.products_options_values_id WHERE
p.products_status = 1 and pd.language_id = '1'
and p2c.categories_id =
'17'
GROUP BY p.products_id
HAVING (

FIND_IN_SET('Sizes80x32',GROUP_CONCAT(CONCAT(REPLACE(po.products_options_name,
' ', ''),pov.products_options_values_name))))
limit 12


If anyone could help me with this, I would be so appreciative and grateful. I really need this filter for my store to work. It's a must. Tech support from 1&1 haven't been helpful at all trying to help me with this problem. I really thank whoever can help me with this. Also, I hope I posted this in the right forum catagory. My apologies if I didn't.



Edited 2 time(s). Last edit at 09/15/2012 09:17PM by James Prozinski.

Options: ReplyQuote


Subject
Written By
Posted
'order by' clause not working with 1&1 Hosting
September 15, 2012 04:50PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.