'order by' clause not working with 1&1 Hosting

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

'order by' clause not working with 1&1 Hosting

Author

Topic

jimproz
Starting Member

1 Post

Posted - 2012-09-15 : 18:17:24

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 SQL than 1&1 is using. Currently I'm running 5.0.9 I believe with 1&1 Hosting. 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 SQL 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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-15 : 18:27:35

This is a Microsoft SQL Server forum, so there are very few if any experts in MySQL that post here. You may get better and faster answers at forums.mysql.com or dbforums.com

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-09-15 : 18:48:28

Best solution would be asking in a mysql forum.

This might work though:
remove the group section and the having section, and insert this:
where CONCAT(REPLACE(po.products_options_name,' ',''),pov.products_options_values_name)='Sizes80x32'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources