MySQL command error on product list in admin

  • Posts: 2
  • Thank you received: 0
11 years 3 months ago #88199

whenever I try to go here:

. . . /administrator/index.php?option=com_hikashop&ctrl=product

I get this:

An error has occurred.
500 SQL=SELECT a.ordering, b.* FROM hikashop_product_category AS a LEFT JOIN hikashop_product AS b ON a.product_id=b.product_id WHERE a.category_id=2 AND b.product_type = 'main' ORDER BY a.category_name asc LIMIT 0, 20
Error in query: Unknown column 'a.category_name' in 'order clause'

Last edit: 11 years 3 months ago by tedfrazier.

Please Log in or Create an account to join the conversation.

  • Posts: 26034
  • Thank you received: 4006
  • MODERATOR
11 years 3 months ago #88244

Hi,

Which version of HikaShop are you using ?

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 2
  • Thank you received: 0
11 years 3 months ago #88253

Hikashop version : 2.0.0

Joomla! 3.0.2 Stable

PHP 5.3.20

MySQL 5.5.29

BUT . . . It just now quit giving me that error. I have changed nothing since yesterday. Just logged back in this morning and everything seems to be working again.

Please Log in or Create an account to join the conversation.

  • Posts: 26034
  • Thank you received: 4006
  • MODERATOR
11 years 3 months ago #88258

Hi,

Good thing !
The column category_name exists since a long long time so it was a little bit wired that the column not found except if the installation was not complete.

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 22
  • Thank you received: 0
10 years 11 months ago #107548

Hello,

I've got the same problem.

When i tried to sort the name column in de category picker, i got the following error.
I looked in the tables and i saw that there is no column category_name to sort on.

Can you please fix this.



1054 Unknown column 'a.category_name' in 'order clause' SQL=SELECT a.ordering, b.* FROM spa_hikashop_product_category AS a LEFT JOIN spa_hikashop_product AS b ON a.product_id=b.product_id WHERE a.category_id=2 AND b.product_type = 'main' ORDER BY a.category_name asc LIMIT 0, 20

Please Log in or Create an account to join the conversation.

  • Posts: 26034
  • Thank you received: 4006
  • MODERATOR
10 years 11 months ago #107683

Hi,

Same question: Which version of HikaShop are you using ?

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 22
  • Thank you received: 0
10 years 11 months ago #107705

Hi Jerome,

I have installed Hikashop Business v2.1.3.

As far as I can find out, occurs the problem in the backend view "product/selectcategory. php

On line 59 the field "category_name" is used, but this field is not a part of table "hikashop_product_category".

<?php echo JHTML::_('grid.sort', JText::_('HIKA_NAME'), 'a.category_name', $this->pageInfo->filter->order->dir,$this->pageInfo->filter->order->value ); ?>

When i sort on the Name column, the folowing error occurs:

1054 Unknown column 'a.category_name' in 'order clause' SQL=SELECT a.ordering, b.* FROM spa_hikashop_product_category AS a LEFT JOIN spa_hikashop_product AS b ON a.product_id=b.product_id WHERE a.category_id=2 AND b.product_type = 'main' ORDER BY a.category_name asc LIMIT 0, 20

Regards,

René

Please Log in or Create an account to join the conversation.

  • Posts: 26034
  • Thank you received: 4006
  • MODERATOR
10 years 11 months ago #107793

Hi,

Please logout and clean your cookies.
I don't know which view broke your session but the problem does not come from the "product | selectcategory".
I checked a lot of view but I can't find which one have overwrite the filter value for the product listing.
So the best is too clean your cookies so the sortering filters will be reset.

We will add new checks in future release is order to remove wrong sorting (and not generate a sql error).

Regards,

Jerome - Obsidev.com
HikaMarket & HikaSerial developer / HikaShop core dev team.

Also helping the HikaShop support team when having some time or couldn't sleep.
By the way, do not send me private message, use the "contact us" form instead.

Please Log in or Create an account to join the conversation.

  • Posts: 22
  • Thank you received: 0
10 years 11 months ago #108280

Hi Jerome,

I cleaned my cookies after I was logged out, and try it again.

As I expected, it didn't solve the problem.

For so far as I can see, is the real problem a wrong column name in the underlying query statement.

As you can see in the attached error picture, there is no field with the name "category_name" in the table "hikashop_product_category".

The only conclusion I can draw, is that there is an typo in the query statement.

Regards,

René

Attachments:
Last edit: 10 years 11 months ago by Rebo2k.

Please Log in or Create an account to join the conversation.

  • Posts: 81714
  • Thank you received: 13104
  • MODERATOR
10 years 11 months ago #108291

Hi,

It's not that simple. The order by of the query is generated dynamically based on the title that is selected for the ordering on the listing. By default the products listing is ordered by the "ordering" column.
So if you logout and login again, that should wipe out your user session and thus the title selection for the ordering and it should go back to the default "ordering" title of the products listing.

If you want to look at the code, you can see that in administrator/components/com_hikashop/views/view.html.php, you have the line:
$pageInfo->filter->order->value = $app->getUserStateFromRequest( $this->paramBase.".filter_order", 'filter_order', 'a.ordering','cmd' );

which loads the ordering title from the user selection/user session. And as you can see, the default column is a.ordering and not category_name.

So I can't see how that's possible that you have the category_name title used for the ordering of the listing after a user session wipe out.

At least, we are not able to reproduce the problem on our end and no one else besides you too reported the issue so I can only think of something that you have in common on your website which other users don't have. If there was such issue by default in HikaShop which would have had 100s of people reporting the issue since last release.

Can you provide a backend access to your website so that we can see that error as otherwise we won't be able to move forward on the issue ?

Please Log in or Create an account to join the conversation.

Time to create page: 0.083 seconds
Powered by Kunena Forum