Ikool’s Blogbed

Fix of unable to update Product details in WP e-commerce

After playing with WP e-commerce for sometime and fixing some issues (see Incorrect URLs for images, css and js files in Products page of WP e-commerce plugin, Fix of Settings menu on WP e-commerce’s Products admin page and Tips on display categories in WP ecommerce), I am able to display products and categories. But I also notice an issue which is users can’t really update product details such as product name, description, product tags, and price and stock control after a product is added.

Let’s take a look at what the issue is. If you check your apache log for your site, then you will notice there is an error message regarding the update SQL query statement used to update the product details as shown below:

[Tue Jun 18 12:11:34 2008] [error] [client IP-ADDRESS] WordPress database error Incorrect integer value: '' for column 'brand' at row 1 for query UPDATE `wp_product_list` SET `name` = '9113 - Retractable ball pen', `description` = 'Plastic retractable ball pen', `additional_description` = '', `price` = '0.70', `pnp` = '', `international_pnp` = '5', `brand` = '', quantity_limited = '0', `quantity` = '0', `special`='0', `special_price`='', `display_frontpage`='0', `notax`='1', `donation`='0', `no_shipping` = '0' WHERE `id`='1' LIMIT 1 made by include, referer: http://blog.sdu2.hyperion.com/wp-admin/admin.php?page=wp-e-commerce/display-items.php

The error specifically points out that database error Incorrect integer value: '' for column 'brand'

So next let’s take a look at the definition of the field “brand” in <PREFIX>product_list table which is defined as bigint(20) not nullable, and therefore you must specify a value for this field, and empty/null is not allowed. Here is the line you need to modify in WP e-commerce (defined in display_items.php and line number is around 530) to make the update work:

$updatesql = "UPDATE `".$wpdb->prefix."product_list` SET `name` = '".$wpdb->escape($_POST['title'])."', `description` = '".$wpdb->escape($_POST['description'])."', `additional_description` = '".$wpdb->escape($_POST['additional_description'])."', `price` = '".$wpdb->escape(str_replace(",","",$_POST['price']))."', `pnp` = '".(float)$wpdb->escape($_POST['pnp'])."', `international_pnp` = '".(float)$wpdb->escape($_POST['international_pnp'])."', `brand` = '".$wpdb->escape($_POST['brand'])."', quantity_limited = '".$quantity_limited."', `quantity` = '".(int)$quantity."', `special`='$special', `special_price`='$special_price', `display_frontpage`='$display_frontpage', `notax`='$notax', `donation`='$is_donation', `no_shipping` = '$no_shipping' WHERE `id`='".$_POST['prodid']."' LIMIT 1";

As highlighted in red, $wpdb->escape($_POST['brand']) may be empty for our updates, therefore, we must validate it. We will set it to 0(zero) if it’s emty:

$brand = $wpdb->escape($_POST['brand']);
if (empty($brand)) $brand = 0;

So next thing for you to do is to replace the original $wpdb->escape($_POST['brand']) in the query with $brand, then you are all done.

Settings: WordPress 2.5.1 and WP e-commerce 3.6.6

Advertisements

June 24, 2008 - Posted by | How Tos, Wordpress Tips, WP E-Commerce | , , , ,

11 Comments »

  1. Thanks ikool. This is fixed now in 3.6.7

    Ciao,

    Dan

    Comment by Dan Milward | June 26, 2008 | Reply

  2. iKool spaketh of his problem, and Instinct listeneth.
    Instinct thought iKool good, and so fixed the hell out of his problem!

    Comment by Arbie | June 29, 2008 | Reply

  3. Hey Ikool,

    I notice you say you are “able to display products and categories”. I’m looking to do the same thing, but I’m not sure if what you’ve done is what I have in mind. Could you send me a link to a site where you’ve implemented this or post a screenshot?

    Thanks,
    Waldo

    Comment by Waldo | June 30, 2008 | Reply

  4. Do you have a download available of the fixed files?

    Comment by David Shaw | July 3, 2008 | Reply

  5. Waldo,
    I don’t have a site for this yet since I am still trying to integrate WP e-commerce with WordPress 2.5.1 in my local environment to be sure everything work fine before deploying it. But I will make a screen shot of it so that you can take a look at it

    Comment by ikool | July 6, 2008 | Reply

  6. Here are the screenshots for both products and product groups pages:


    and

    Comment by ikool | July 6, 2008 | Reply

  7. I don’t have one available but I can make one for you guys later

    Comment by ikool | July 6, 2008 | Reply

  8. Hi there,

    Thanks for this tip. I love WP E-Commerce, but I have worked with both 3.6.7 and 3.6.8 and I keep running into the same problems:

    1) Can’t edit, update or delete products
    2) Can’t get my “sale” price on items to carry over throughout the checkout process. For example, I have a book for $39.95, but it is on sale for $9.95, but when I go through the checkout system, PayPal tries to charge the full $39.95. I’m guessing that this is because e-commerce isn’t sending through the updated, sale price.

    Sorry to be hassling you here about this, but I have tried to get support for this in the e-commerce forum, but haven’t had any luck.

    Do you have any tips for me? Can you email me?

    Totally bummin.

    Josh

    Comment by Josh | October 29, 2008 | Reply

  9. Hello I have the same problem ,,,,cannot edit, products at all… when I first installed it worked fine…. I updated now I cannot edit…. I disabled, removed ,,,,plugin to no avail…. can you contact me via email
    paul

    Comment by paul | December 4, 2008 | Reply

  10. Hello: I don’t know what happened but I cannot edit any of the products. when I click add product it takes me back to the dashboard….I already deactivated the product, reenabled it, and uninstalled it….and reinstalled it….but no luck…
    can you have a look under the hood??
    paul

    Comment by paul inbody | December 18, 2008 | Reply

  11. hi,

    i have just successfully installed wp-e-commerce plugins. i have a problem when tried to change product settings i.e. change the display all product drop down menu and other settings but never been able to save it. settings always back to the default.

    appreciate to have your feed back.

    many thanks,
    anwar

    Comment by anwar kranggan | May 19, 2009 | Reply


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: