March 30 2010
WP e-commerce SKU on variations
Tagged Under : e-commerce, fix, sku, Wordpress, wordpress plugin, wp e-commerce
UPDATE – 22/02/2011
Files for the 3.7.8 version of wp-e-commerce. I have provided only the altered files and full plugin if you just want to replace it.
DON’T FORGET TO BACKUP FIRST!!
Download → http://bit.ly/3-7-8-SKU
UPDATE – 01/09/2010
If you need additions that are not contemplated in this fix package you can email me for a specific quote.
UPDATE – 18/05/2010
There has been an update on wp-e-commerce a few days ago and if you have applied my add-on you’ll probably have problems with the plugin. Post a comment or email me if you’re having problems.
===============================
If you are using wp-ecommerce plugin for wordpress and you ran into some missing functionalities this might help you.
The specific mod I’ll show you today will enable you to specify SKU to any variation on any product on your system.
I don’t know how proficient your are on programming WordPress, but let me know if you run into any difficulty.
So, first of all you need to add a new field on your database on table wp_wpsc_variations_properties called “sku”. I added this field as varchar with a length of 64 characters, but do adapt this one to fit your needs.
Backup your e-commerce plugin folder.
This should get you up and running. Let me know if you need anything.
Hi JP,
thanks a bunch for this code! I needed to make some changes to it since I’m using WP e-Commerce 3.7.6 and some of the text constants were replaced by WP’s __ function. And for some reason I had to add a global $wpdb; to wpsc_purchaselog_details_SKU().
Anyway, this is a much needed modification. Thanks again!
Tom
Hello Tom,
Thank you for the kind words. I’ve been having a lot of requests from a lot of people regarding this feature.
Yes some changes might be needed to adapt this to different versions. Let me know if you require and further assistance
Actually, there is one thing I need help on. I’m coding some extra stuff for my WPEC site and now I’m trying to get the variation SKU with a single SQL statement. I’ve been wrecking my head as SQL really isn’t my strong suit.
But I figure there must be a way to get from the purchase id to the cart id to the variations of the products in the cart all the way down to the variation SKU. But whatever I do, I get back too many rows.
Do you by any chance have an idea how to achieve this with an SQL query?
Hello again Tom,
We can do this but not in 1 single SQL statement, because field all_variation_ids is comma separated. If we would work with the SQL code we might be able to do this but would represent a break on the Database atomicity.
However I can provide you with 2 SQL statements that you can easily use to create a PHP functions to return a given product sku.
Store in an array the result of this SQL statement: SELECT `variation_id` FROM `wp_wpsc_variation_assoc` WHERE `type` IN (‘product’) AND `associated_id` = ’4′ ORDER BY `variation_id` ASC
Now lets say you have the array called $variation_array. You’ll have to do a second SQL as follows:
“SELECT `sku` FROM `wp_wpsc_variation_properties` WHERE `id` IN (SELECT DISTINCT `priceandstock_id` FROM `wp_wpsc_variation_combinations` WHERE `value_id` IN (SELECT `value_id` FROM `wp_wpsc_variation_values_assoc` WHERE `product_id` IN (’4′) AND `variation_id` IN ( SELECT `variation_id` FROM `wp_wpsc_variation_assoc` WHERE `type` IN (‘product’) AND `associated_id` = ’4′ ) AND `visible` IN (’1′)) AND `product_id` IN (’4′) AND `all_variation_ids` LIKE ‘”.implode(“,”,$variation_array).”‘)”
This will return the exact SKU for the given variation.
You just need to provide the product_id, in this case, it’s 4.
Let me know if this fits your needs.
I guess that’s why I was having such a hard time wrapping my head around this. I thought there had to be a way to get this done in a single query, but I kept hitting a roadblock with the variation_id and value_id fields.
I’ll give your solution a try, definitely looks good.
Thanks again!
WOW… this fix looks like it should do the trick but MAN what a mess. I am not a programmer so this may be a test of my ability to follow directions!
Hello Erika
Please let me know if you require aid on implementing the solution. Just follow the directions and you should be fine.
Jose – I implemented everything as is listed above in your post. Upon ‘save’ and then trying to view the backend or the frontend the site was gone. Everything was blank. Ideas? I’m using WP Version 2.9.2 & WPEC Version 3.7.5.9.
Hello Erika,
Lets try to see what is going wrong.
First thing we must do is see why the page is blank. It’s probably due to a PHP error and since WP omits all error we need to turn debug on WP.
To do this open the wp-config.php file and add this line:
define(‘WP_DEBUG’, true);
After that open your blog and check what error it’s outputting.
Looking forward to earing from you.
Hey José,
I’ve been trying to apply the changes to different versions (i.e. 3.7.5.3 and 3.7.6.1) but I get a feeling I’m either missing something or I’m not applying your changes properly. I keep getting syntax errors, SKUs don’t save on variations, etc.
Would it be possible for you to take the latest version, apply the changes yourself, and make them available for download? That way, if someone who’s not all that experienced is doing something wrong, we can rule out newbie editing mistakes.
Thank you.
John
Hello John,
I can certainly make the adaptations for different versions. I know that for some versions the syntax changes a bit.
Please sent me the two files I mention on the guide, by email, to me and I’ll gladly make the changes and provide them here on the post.
Best regards.
Hey José,
I tried sending you an email but for some reason, Yahoo couldn’t find your domain at joseairosa@core-creations.pt.
Rather, I’ve uploaded the files for 3.7.6.1 at:
http://hotfile.com/dl/39664833/262b118/3.7.6.1-files.zip.html
John
Hey John,
That email doesn’t exist anymore. I probably forgot to change it on the Blog. I’ll download and upload the corrected files here on the Post.
Hello again John,
I’ve added the changes to the files. I didn’t have a chance to test them so please give me some feedback.
http://www.joseairosa.com/downloads/3.7.6.1-files.zip
Hey José,
The modifications worked great. I’m in the process of integrating it into my site. Thanks!
John
I’m glad it worked!
Great addition to the module
One thing I noticed is when you add a variation and enter the SKU number, when saving the product, the first save doesn’t store the SKU number in the database, and the SKU field is empty, so you have to enter it again and save. I modified the insert query in the update_variation_values function to include ‘{$variation_sku}’ in the inserted fields.
$wpdb->query(“INSERT INTO `”.WPSC_TABLE_VARIATION_PROPERTIES.”` ( `product_id` , `stock`, `price`, `weight`, `weight_unit`, `file`, `sku` ) VALUES (‘{$product_id}’, ‘{$variation_stock}’, ‘{$variation_price}’, ‘{$variation_weight}’, ‘{$variation_weight_unit}’, ‘{$variation_file}’, ‘{$variation_sku}’);”);
Hello Milos,
Glad you’ve found it useful. And thank you for the addition
Jose – will you be releasing any updates to match the new release of wp-e-commerce (3.7.6.3)?
Hello Erika,
At the moment it will be a bit hard for me to focus on releasing a new compatible version.
I have already noted down this on my to-do list and I’ll try to have it ready for next week
Sorry for not being sooner.
Will the same work around you have created (above) work?
I will have to take a look at the new code, but if they didn’t change anything on those 2 files it will work as before.
You can test it out but create a backup of your wp-e-commerce files first and database.
Also, don’t forget to backup both files (variations.class.php and purchaselogs.class.php) before installing the new version. After installing the new version got to your FTP and replace them. Again, don’t forget to backup your files
I have a partial fix, but need help on the rest.
I’m running Version 3.7.6.7 and some code has been updated from what is listed in this post. I was able to make the appropriate changes. Mainly reformatting some lines into tables( adding etc.) and changing a few ” to ‘
The only significant difference was instead of adding this line:
$output .= ”
“.TXT_WPSC_SKU.”
\n\r”;
Use this line:
$output .= ” “.__(‘sku’, ‘wpsc’).”\n\r”;
So, I’m pretty sure I have the variations.class.php file updated correctly (I’m not receiving any errors) and it looks good when adding a variation to the product.
However, I can not figure out how to update the suggested code for purchaselogs.class.php . I have tried everything I can think of, and am out of options for that one. I would greatly appreciate help with the updates to the added lines of code for purchaselogs.class.php.
Thanks,
–Blake
Ok.. I some how skipped over José’s comment with this attachment: http://hotfile.com/dl/39664833/262b118/3.7.6.1-files.zip.html
That fixed my issues with purchaselogs.class.php
Everything is working now… Thanks José !!!
Hi – awesome fix. I’m on 3.7.6.7 and it works great. I’m trying to extend it to allow for the SKU to come through in the admin email once the cart has submitted. I know it’s line ~180 in the transaction_result_functions.php but can’t nail the SQL. Any help would be greatly appreciated. Thanks again!
Hello Morgan.
I’m glad it was helpful to you. I will take a look at your issue and reply asap.
Hey guys, I’m having a bit of trouble with this, I can’t seem to find this code anywhere in the variations.class.php
—————–
$output .= ”
“.TXT_WPSC_VARIATION.”
\n\r”;
—————–
any ideas?
(I’m running version 3.7.6.4 by the way)
Actually disregard all that, all seems to be working now!
I’m glad you got it to work Dan.
Let me know if you require additional assistance
Thank you VERY MUCH Jose for this addon!!!
Could you please also tell us how to print the SKU for each product variation (on the checkout page for example).
Thank you!
Ciprian
ps: or to send the sku codes in the admin email report…
Thank you VERY MUCH Jose for this addon!!! Could you please also tell us how to print the SKU for each product variation (on the checkout page for example). Thank you! Ciprian
(I’m running version 3.7.6.4 by the way)
Anyone know if this patch works with 3.7.8?
Hello Greg!
I don’t know how much of the code was changed but I wouldn’t adventure in applying the same patch to the newest version.
I will try to post an updated version that will work.
Thanks José. Appreciate it! Anyway to subscribe to this thread so I get emailed when you’ve posted an updated patch?
Greg,
I will try to release it this weekend
An update release would be AMAZING! Really! A life saver for two stalled out client website that I can FINALLY complete. You are the ONLY person working on this issue. SO EXCITED — I will watch this site for your update. YAY!
Maybe this weekend?
Man if you can release the update soon it would be great, you would be saving me!
I’ll try to watch this thread for updates
Thank you!
[...] un po’ di ricerche online, ho trovato un sito web che proponeva una soluzione, non più adatta però alle ultime versioni del plugin. Fortunatamente [...]
I should be able to release the new version updated for the new installation of wp-e-commerce by the week end. I know a lot of you are eager to have it.
New version is out as requested! It was earlier than the weekend
Jose — do you have a list of features for your fix? As in, does the SKU pass thru to the checkout page? Does the SKU pass thru to the Purchase Receipt to the customer or to the store owner?
Really excited about the update so far — but the customizations we paid for may not be included in this bigger upgrade feature you have done.
Hey JP, thanks for your plugin (I made an Italian article for it on my blog), but I have problems!
I updated the database and the files I’ve just downloaded, and the field appears right next to the variation; I can specify a number of items per variation but when someone buy an amount of the variation the number doesn’t change! And if the costumer wants to buy something more of what is available (i.e.: The variation has 5 units in stock, the costumer asks for 10) it goes one with the transaction without any error/blocking message.
Please help me because it’s important for a website I’m developing (you know, something about wearing… so tot. t-shirt with this color/size, tot. with another color/size, and so on… So I need your feature!). Thank you!!
Hello webQbe,
This solution only gives you the ability to add SKU to variations. It does not (should not!) interact with the stock you have of that specific variation.
I think this might be some other problem but I will take a look on my sandbox wp installation.
Oh, I didn’t get that!
If you are able to add a field that decreases the quantity when someone buys it, well it would be wonderful!!!
I’m not so good in PHP & SQL, but I can help you developing it!
Please notice me, even to my mail if you want
Hi José,
Is it possible to display the variation SKU in the checkout? I appreciate the work you’ve done on this mod. Thanks!
…JP? I really need this feature! It’s so hard to develop it? u.u Please let me know!
Hello Jeff,
Yes, it is certainly possible.
Just call this function where ever you like in you checkout template: cart_item->product_id,((is_array($wpsc_cart->cart_item->variation_values) && !empty($wpsc_cart->cart_item->variation_values)) ? current($wpsc_cart->cart_item->variation_values) : 0 )); ?>
Take note:
- You must call this inside the wpsc_cart loop
Hello webQbe,
I’ve been completely overflowing with work and I was unable to dedicate some time to this.
I will try to give you a reply in the next few days. I apologize for this.
Ah ok no problem! Sorry if I’ve been pressing
Send me an email when you want to!
Thanks José,
I’m not sure how to implement the function you supplied – do I create a function somewhere else and call it in my shopping_cart_page.php file or do I place that code directly in the shopping cart file?
Would it also be possible to display the variation SKU on the single_product.php page and have it update when different variations are selected just like the price updates? Thanks again!
Ok, ok JP! No more problems, I resolved the whole thing. Thank you very much!
webQbe
@José P. Airosa – My SKU’s do not save into the form fields… I tried adding the code from @Milos and still they do not save.
Is there a fix?
Hello BrettDz,
Have you added the required fields to the database?
Just a quick way to add the sku field to the database:
query(“ALTER TABLE wp_wpsc_variation_properties ADD sku VARCHAR(64);”); ?>
Add that to your header.php file load your page once, then remove the line.
Good tip Joshua.
Thanks!
Here’s a revised version:
query(“ALTER TABLE ” . $wpdb->prefix . “wpsc_variation_properties ADD sku VARCHAR(64);”); ?>
Hello Joshua,
Thank you for your contribution. I will take a look and add it to documentation on the post.
Your bit.ly link is 404ing.
Hi!
I will take a look at the link and replace it. Thank you.