Missing orders in Virtue Mart admin and how to fix it

I just realized that none of the recent orders in one of the Virtue Mart installations I manage were showing up. It looks like the most recent one was….three months ago. But when I search for a more recent order I get the correct results. So, the orders are in the database but they weren’t being listed on the main order list. This will go over what I’ve found in this install of Virtue Mart 1.1.2. Other versions may not suffer from this phenomenon.

Weird.

After a little digging I find that the order.order_list.php file in the /administrator/components/com_virtuemart/html folder has what I need to start my investigation. Starting at line 26 there is a bundle of SQL that joins the jos_vm_orders table to the jos_vm_order_user_info table. Well, since this is an administrator-only issue, I just echoed the $list variable at line 46, reloaded the order list in admin.

I then copied the query that was printed at the top of the page, replaced the #__{vm} with my installation’s table prefix of jos_vm and pasted the whole query in my MySQL tool of choice to query my database. Well, turns out the query isn’t returning an up to date result set. I know that the data for orders is being saved to jos_vm_orders because I can look at that table and everything is up to date. So next I move on to the table the query joins it with: jos_vm_order_user_info.

Aha! It looks like jos_vm_order_user_info wasn’t up to date at all. Weird.

According to this forum post it’s because there were custom fields added to the jos_vm_user_info database table and the jos_vm_order_user_info table no longer matched the columns. So the solution was to see what columns didn’t match up and alter the jos_vm_order_user_info table to have those missing columns. Turns out I added a single custom field and it didn’t get added to the order user info table…so after adding the column to the order user info table everything worked perfectly.

So, if you added custom fields to your user registration you should make sure your jos_vm_user_info table matches up those custom fields with the columns in jos_vm_order_user_info.

Ordering VirtueMart Child Items in Dropdowns

Ok, so a ton of people keep asking me about how to order child products in the dropdown list. I don’t develop with VirtueMart much anymore so the suggestions I give here are mere suggestions and have not been tested or put through any sort of trial. I’m just looking at the code and guessing at what to change.

This exploratory exercise uses VirtueMart 1.1.6 stable.

If it works for you let me know. If it kind of works for you but you fix something and it works better, let me know and I’ll update here.

  1. So let’s take a look at ps_product_attribute.php
  2. Somewhere in there is a bunch of functions that start with ‘list_attribute’. Let’s look at those.
  3. function list_attribute seems to be the main one that calls the other variations. It looks like by default it calls up the drop down menu for attributes. (line 221)
  4. Since that one is the default, let’s see if making modifications to any of its queries will give us the results we want. The function starts at line 241.
  5. At line 273ish you should see a SQL query: $q = “SELECT product_id,product_name FROM #__{vm}_product WHERE product_parent_id=$product_id AND product_parent_id<>0 AND product_publish=’Y'” ;
  6. This is the first query you’re going to play with. You can tack on any sort of ORDER BY directives that you wish:
    ex $q = “SELECT product_id,product_name FROM #__{vm}_product WHERE product_parent_id=$product_id AND product_parent_id<>0 AND product_publish=’Y’ ORDER BY product_name” ;
    I just ordered the SQL results by product name. You can put in whichever column or set of columns you wish to order by. It’s up to you.
  7. Now take this same principle and try it throughout the various ‘list_attribute’ functions and see if you can achieve the sorting results that you desire.

Joomla! migration headaches

Recently I moved an installation of Joomla! with VirtueMart to a new web host. The previous web host had restricted most of the export functionality in cPanel so I had to FTP everything over to the new host manually.

This created a lot of extra headaches. The most persistent was not being able to consistently save/update/create users in the administrative back-end. Every so often the form would time out and give the error message ‘Error Loading ModulesMySQL server has gone away’.

It took around 24 hours of constant searching, but I finally found a solution that worked for me at the Joomla forums.

Basically it states that the configuration.php needs to be edited so that it is in UTF-8 encoding without BOM. You can open the file in Notepad++ and set the encoding to this, then upload it and VIOLA.

Hopefully this saves someone else a headache.

Integrating VirtueMart with Constant Contact

This week I’ve been tackling a project that has been in the back of my mind for a few months now. I was able to leverage the Constant Contact API to integrate Constant Contact user records with VirtueMart/Joomla user profiles. Now when a user is viewed from within the VirtueMart user manager you will also be able to see/edit their related constant contact record and list subscriptions.

New contacts can be created at the same time as Joomla users and it will auto fill information into the constant contact record based on the VirtueMart information. If an existing user profile that does not have a constant contact record is viewed, then when the profile is saved a new constant contact record will be created for that user. I’ll eventually be making more use of the API to allow users to select from various newsletters when registering so that they will be able to have more targeted communications.

The only issue I ran into was when I uploaded the changes to the live web server with PHP 5.2.11. It seems that on my local server with PHP 5.2.9 everything worked well but the comments in the constant contact class file were causing parse errors. I was able to get around the errors by deleting some of the comments.

Ordering VirtueMart Child Items

Edit: This is easily my most popular post and solicits a lot of questions. This method sorts children in a table on the parent product page. It DOES NOT sort children in a drop down list. 1/10/2011

UPDATE: New methods here: I am updating with another post that will try to address how to universally sort attribute/child drop down lists.

I’ve been pulling my hair out the past couple of hours trying to find a built-in way to order child items in VirtueMart. There are plenty of ways to sort built-in to the category view of multiple products, but there is no built in function to sort child products (items). The guidance on the VirtueMart forums was lacking and would produce errors when including external child ids to be included, so I had to do some digging.

Around line 488 in ps_product_attribute I was able to add the following code after the if($child_ids){} clause:

elseif(!$child_ids){
$q .=” ORDER BY product_id”;
}

Now my child items are sorted by ID. You could easily product_id to be any other field, but for my purposes this worked great.