VirtueMart Forum

VirtueMart 2 + 3 => Virtuemart Development and bug reports => Topic started by: dennisg on November 24, 2020, 14:26:56 pm

Title: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: dennisg on November 24, 2020, 14:26:56 pm
Hi,

Just updated my VM installation to the latest 3.8.6 10373

After the update, when in products in the back-end and if I apply any search filter I get the above error.

Any ideas on what could have gone wrong and how to fix it?

Thanks!
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: Partyzant on November 24, 2020, 18:20:11 pm
unfortunately I have the same problem :-(
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: PRO on November 24, 2020, 22:47:39 pm
have you tried "install or update tables" in tools and migration ?
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: dennisg on November 25, 2020, 08:31:44 am
Yep, no luck...
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: dennisg on November 25, 2020, 08:35:33 am
Even optimised db from within VM tools, repaired tables in phpMyAdmin....
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: Jumbo! on November 25, 2020, 09:23:01 am
Go to Joomla Global Configuration and enable "Debug System" option. Next set "Error Reporting" option to "Development" mode.

Go to VirtueMart Configuration and set "PHP Developer mode" option as "For all".

Now go to the error page, take a screenshot and post it here.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: igormoor on November 25, 2020, 10:15:27 am
VirtueMart 3.8.6 10373
Joomla! 3.9.23
PHP 7.4


On same hosting VirtueMart 3.6.10 10211 working properly.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: Cococoder on November 25, 2020, 10:16:29 am
I have the same error and here is the error: PHP Notice: Array to string conversion in /administrator/components/com_virtuemart/models/product.php on line 478
And the call stack:
Call stack
#   Function   Location
1   ()   JROOT/libraries/joomla/database/driver/mysqli.php:665
2   JDatabaseDriverMysqli->execute()   JROOT/libraries/joomla/database/driver.php:1550
3   JDatabaseDriver->loadColumn()   JROOT/administrator/components/com_virtuemart/helpers/vmmodel.php:865
4   VmModel->exeSortSearchListQuery()   JROOT/administrator/components/com_virtuemart/models/product.php:833
5   VirtueMartModelProduct->sortSearchListQuery()   JROOT/administrator/components/com_virtuemart/models/product.php:1992
6   VirtueMartModelProduct->getProductListing()   JROOT/administrator/components/com_virtuemart/views/product/view.html.php:394
7   VirtuemartViewProduct->display()   JROOT/administrator/components/com_virtuemart/helpers/vmcontroller.php:121
8   VmController->display()   JROOT/libraries/src/MVC/Controller/BaseController.php:710
9   Joomla\CMS\MVC\Controller\BaseController->execute()   JROOT/administrator/components/com_virtuemart/virtuemart.php:76
10   require_once()   JROOT/libraries/src/Component/ComponentHelper.php:402
11   Joomla\CMS\Component\ComponentHelper::executeComponent()   JROOT/libraries/src/Component/ComponentHelper.php:377
12   Joomla\CMS\Component\ComponentHelper::renderComponent()   JROOT/libraries/src/Application/AdministratorApplication.php:101
13   Joomla\CMS\Application\AdministratorApplication->dispatch()   JROOT/libraries/src/Application/AdministratorApplication.php:159
14   Joomla\CMS\Application\AdministratorApplication->doExecute()   JROOT/libraries/src/Application/CMSApplication.php:196
15   Joomla\CMS\Application\CMSApplication->execute()   JROOT/administrator/index.php:51

PHP-Version 7.3.21
Joomla version, 3.9.23.
VirtueMart 3.8.6 10373

Trouble shooting done so far: Deactivated plugins, reinstall Joomla core files. Fix database. Install and if necessary update missing table . Clear cache. Tested on a test server with same template and version and error could not be reproduced.

Note for anyone with the issue: please post your php, Joomla and virtuemart version.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: GJC Web Design on November 25, 2020, 10:29:26 am
I can't recreate it on

PHP-Version 7.3.21
Joomla version, 3.9.23.
VirtueMart 3.8.6 10373

line 478 is  $where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;

so somehow  $virtuemart_category_id must be an array -- is it possible now to have the category select as a multiselect??

and why does it now say "Unselect any product category" ?
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: AH on November 25, 2020, 10:40:08 am
"and why does it now say "Unselect any product category" ?"
"is it possible now to have the category select as a multiselect??"

New functionality - enabled by hidden config

Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: igormoor on November 25, 2020, 10:42:45 am
Hidden config? This is great! New functionality - even better.
But how we can solve this?
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: k2bmedia on November 25, 2020, 11:15:31 am
I think the problem only occur when you use subproducts (product is children of)
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: igormoor on November 25, 2020, 11:20:49 am
I think the problem only occur when you use subproducts (product is children of)

I don't have any children products. And have this situation on two sites, with different articles and structure.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: AH on November 25, 2020, 11:44:02 am
Hidden config? This is great! New functionality - even better.
But how we can solve this?

No idea - what I was answering was GJC's questions regarding the screen shots
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: EsSa55 on November 25, 2020, 11:50:52 am
I can't recreate it on

PHP-Version 7.3.21
Joomla version, 3.9.23.
VirtueMart 3.8.6 10373

Hello John

I have same issue:

PHP-Version 7.3.24
Joomla version, 3.9.23.
VirtueMart 3.8.6 10373

Also, site product images "disappeared" which is why I wanted to check in backend.

See: https://back3.4footyfans.com
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: dennisg on November 25, 2020, 13:42:12 pm
Here it is
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: ruleragency on November 25, 2020, 13:51:25 pm
Hello,
Same Problem Here.
After the update i cannot search,filter products or change page on back-end virtuemart products page.

Anyone figured it out?

PHP  7.4.12
Joomla:  Joomla! 3.9.23 Stable [ Amani ] 24-November-2020 15:00 GMT
Database Version:  5.5.5-10.3.17-MariaDB
VirtueMart 3.8.6 10373
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: roby on November 25, 2020, 13:55:34 pm
Same problem  I think Child but i don't have child category

Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: Jumbo! on November 25, 2020, 14:28:44 pm
Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code: [Select]
                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code: [Select]
if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: dennisg on November 25, 2020, 14:33:30 pm
Thank you Jumbo! that did the trick!

Looking forward to the next release :-)
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6
Post by: ruleragency on November 25, 2020, 15:00:57 pm
Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code: [Select]
                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code: [Select]
if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Cococoder on November 25, 2020, 15:06:57 pm
Jumbo! Thanks it worked for me.

If it doesn't work maybe try logigng out the admin panel and log back in again?
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Jumbo! on November 25, 2020, 15:07:34 pm
Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code: [Select]
                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code: [Select]
if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.

Then you must have changed the wrong codes. Category filtering works fine now.

Modified file attached.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: EsSa55 on November 25, 2020, 15:10:59 pm
Thanks, Jumbo

Products now open in the backend.

However, Product Price, image(s), Category(ies) and Manufacturer are now empty.

TIA
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: igormoor on November 25, 2020, 15:21:34 pm
Work fine. Thanks.

This is permanent solution or it will be some update soon?
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: ruleragency on November 25, 2020, 16:25:37 pm
Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code: [Select]
                if(!empty($cats)){
                    $joinCategory = TRUE;
$cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}

Replace above by:

Code: [Select]
if(!empty($cats)) {
$joinCategory = TRUE;
$cats .= $virtuemart_category_id;
$where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
} else {
if(is_array($virtuemart_category_id)) {
$category_id = array_filter(array_map('intval', $virtuemart_category_id));
if(!empty($category_id))
{
$where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
}
} else {
$where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
}
}

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.

Then you must have changed the wrong codes. Category filtering works fine now.

Modified file attached.

Thank you for the file. I uploaded the file in administrator/components/com_virtuemart/models, I cleared all browser history and cookies and I still have the same problem after the update.
Any ideas?
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: aLioneL on November 25, 2020, 17:14:31 pm
Thank you Jumbo !!!!!!!!!!!
...tu m'as sauvé  ;)
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Jumbo! on November 25, 2020, 19:24:24 pm
Thanks, Jumbo

Products now open in the backend.

However, Product Price, image(s), Category(ies) and Manufacturer are now empty.

TIA


That's a different issue. It appears that the database was not updated correctly during the update and the data were lost.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: shyntaxx on November 26, 2020, 13:57:22 pm
Thank you very much that did the trick.

I'm not sure if its related but I have one more problem here that is when I click "New" on the Virtuemart product page I get a HTTP ERROR 500 after a little while.

I'm able to clone or edit though but unable to add a new product.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: vale_berto on November 26, 2020, 14:52:03 pm
Hi Jumbo,
thank you very much .. now it is working.


Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Partyzant on November 26, 2020, 16:46:53 pm
thanks!!!!, it works!!!!!
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: EsSa55 on November 26, 2020, 20:01:57 pm
Thanks, Jumbo

Products now open in the backend.

However, Product Price, image(s), Category(ies) and Manufacturer are now empty.

TIA


That's a different issue. It appears that the database was not updated correctly during the update and the data were lost.

The database content is correct but it is not being read.
Do you think repeating the update might solve the issue?
Thankfully, this was on a dev installation, not Live.

TIA
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: EsSa55 on November 26, 2020, 20:44:44 pm
I have restored another Live backup into my dev installation and repeated the VM update.

The issue is the same.

There are 6740 products.

The latest 114 display correctly.

The remainder have no images, categories, manufacture, price.

The database tables are correct.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: citylop on November 26, 2020, 21:49:04 pm
Thank you Jumbo!, your solution worked for me.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: freethud on November 27, 2020, 10:53:36 am
Your solution is ok!!
But now, the product order doesn't work :-(

I'll be waiting for the ultimate solution!!
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Milbo on November 27, 2020, 12:14:20 pm
Please test http://dev.virtuemart.net/attachments/1276/com_virtuemart.3.8.7.10378.zip
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: patosal on November 27, 2020, 20:26:42 pm
Please test http://dev.virtuemart.net/attachments/1276/com_virtuemart.3.8.7.10378.zip
THIS worked! THANK YOU VERY MUCH! :D
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: freethud on November 29, 2020, 08:26:36 am
Your solution is ok but... changing the product order doesn't work!!
Do you think to develop another version??
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Milbo on November 30, 2020, 21:01:32 pm
When I wrote the fix, I did not see the version of Jumbo. My fix works completly different, because the basic construction had a big error. So the technic now is to convert it always to an array.

Code: [Select]
if (!empty($virtuemart_category_id )){
if( is_array($virtuemart_category_id)) {
$virtuemart_category_id = array_filter($virtuemart_category_id);
} else {
$virtuemart_category_id = array($virtuemart_category_id);
}
}
if (!empty($virtuemart_category_id )) {
$joinCategory = TRUE;
VmConfig::set('show_subcat_products',true);
if(VmConfig::get('show_subcat_products',false)){
/*GJC add subcat products*/
$catmodel = VmModel::getModel ('category');
$cats = '';
foreach($virtuemart_category_id as $catId){
$childcats = $catmodel->getChildCategoryList(1, $catId,null, null, true);
foreach($childcats as $k=>$childcat){
if(!empty($childcat->virtuemart_category_id)){
$cats .= $childcat->virtuemart_category_id .',';
}
}
$cats .= $catId;
}

                if(!empty($cats)){
                    $joinCategory = TRUE;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                }
} else {
$where[] = ' `pc`.`virtuemart_category_id` IN ('.implode(',',$virtuemart_category_id).') ';
}
} else if ($isSite) {
if (!VmConfig::get('show_uncat_parent_products',TRUE)) {
$joinCategory = TRUE;
$where[] = ' ((p.`product_parent_id` = "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` > "0") ';
}
if (!VmConfig::get('show_uncat_child_products',TRUE)) {
$joinCategory = TRUE;
$where[] = ' ((p.`product_parent_id` > "0" AND `pc`.`virtuemart_category_id` > "0") OR p.`product_parent_id` = "0") ';
}
}

It replaces more than Jumbos code suggestion.
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: freethud on December 01, 2020, 06:57:19 am
Excuse me,
Should I replace the code from your last update "virtuemart.3.8.7.10378.zip" with this one?
Which are the the row numbers?? With this patch, does it work the change of product order of a category in the frontend?
Thank you very much
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: GJC Web Design on December 01, 2020, 10:43:13 am
the idea is to install (carefully .. i.e. on clone etc first ) virtuemart.3.8.7.10378.zip
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: AnnieG13 on December 09, 2020, 06:05:33 am
I am getting this error too. When I tried to add some categories and products, I get it not when the created the category but when I went to create the products. (See screenshot)

I really need to get this fixed so if anyone has a solution for me I would really appreciate it.

Thanks Annie

Joomla 3.9
Virtuemart 3.4
PHP Version 7.4.13

www.puppylanetreats.com.au
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: Jörgen on December 09, 2020, 10:38:09 am
This is because JOOMLA or PHP changed. Can You downgrade Your PHP perhaps ?

Jörgen @ Kreativ Fotografi
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: rakeshy on December 10, 2020, 00:51:48 am
Please test http://dev.virtuemart.net/attachments/1276/com_virtuemart.3.8.7.10378.zip
Took quite some time to find this fix!
This error gives a LOT of results when searching: 1054 Unknown column 'Array' in 'where clause
Title: Re: 1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **
Post by: ttanidis on December 16, 2020, 15:20:03 pm
Open - administrator/components/com_virtuemart/models/product.php

Find the exact following codes between lines 473 to 479:

Code: [Select]
                if(!empty($cats)){
                    $joinCategory = TRUE;
               $cats .= $virtuemart_category_id;
                    $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
                } else {
               $where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
            }

Replace above by:

Code: [Select]
            if(!empty($cats)) {
               $joinCategory = TRUE;
               $cats .= $virtuemart_category_id;
               $where[] = ' `pc`.`virtuemart_category_id` IN ('.$cats.') ';
            } else {
               if(is_array($virtuemart_category_id)) {
                  $category_id = array_filter(array_map('intval', $virtuemart_category_id));
                  if(!empty($category_id))
                  {
                     $where[] = ' `pc`.`virtuemart_category_id` IN (' . implode(',', $category_id) . ')';
                  }
               } else {
                  $where[] = ' `pc`.`virtuemart_category_id` = ' . $virtuemart_category_id;
               }
            }

This will fix the issue for the time being. A permanent solution is expected in the next release.

Thank you but this didn't solve the problem for me. Same error when filtering by category, search or when I change page.

Then you must have changed the wrong codes. Category filtering works fine now.

Modified file attached.

Thank you for the file. I uploaded the file in administrator/components/com_virtuemart/models, I cleared all browser history and cookies and I still have the same problem after the update.
Any ideas?


It works perfectly for me too. My eshop operates with the template virtueplanet.com/joomla-templates/vp-smart (https://www.virtueplanet.com/joomla-templates/vp-smart) in Joomla! 3.9.23, VirtueMart 3.8.6 10373 & PHP Version 7.4.13.
Thank you very much!