1054 Unknown column 'Array' in 'where clause after update to 3.8.6 ** SOLVED **

[Partyzant]

thanks!!!!, it works!!!!!

[EsSa55]

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

[EsSa55]

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.

[citylop]

Thank you Jumbo!, your solution worked for me.

[freethud]

Your solution is ok!!
But now, the product order doesn't work :-(

I'll be waiting for the ultimate solution!!

[Milbo]

Please test http://dev.virtuemart.net/attachments/1276/com_virtuemart.3.8.7.10378.zip

[patosal]

Please test http://dev.virtuemart.net/attachments/1276/com_virtuemart.3.8.7.10378.zip

THIS worked! THANK YOU VERY MUCH!

[freethud]

Your solution is ok but... changing the product order doesn't work!!
Do you think to develop another version??

[Milbo]

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.

[freethud]

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

[GJC Web Design]

the idea is to install (carefully .. i.e. on clone etc first ) virtuemart.3.8.7.10378.zip

[AnnieG13]

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

[Jörgen]

This is because JOOMLA or PHP changed. Can You downgrade Your PHP perhaps ?

Jörgen @ Kreativ Fotografi

[rakeshy]

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

[ttanidis]

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 in Joomla! 3.9.23, VirtueMart 3.8.6 10373 & PHP Version 7.4.13.
Thank you very much!