Can Marketpress bulk edit price?

I have a store with over 500 products priced around $1.99. I need to ba able to quickly adjust the price of all products by $1.00. Can I do this in bulk? or have to change each one individually?

  • aecnu

    Greetings rydelano,

    Welcome to WPMU Dev!

    Can I do this in bulk? or have to change each one individually?

    There is no native facility in Market Press to bulk change the prices.

    However, with some creative database manipulation it can be done quite easily.

    Before you try any of the below possibilities, please be sure to backup your database.

    Using phpmyadmin or better yet Navicat I would export the database as an sql file, I would then import it into a text editor such as notepad and do a search and replace of the amounts then import the SQL back in.

    Remember to backup the database before attempting this operation.

    Thank you for being a WPMU Dev Member!

    Cheers, Joe

  • digitsoft

    Although Joe's solution will work, my way is MUCH easier :wink:

    @Joe - this would be useful code to add to MP

    I worked on this since you posted it...what a pain! Tested, BUT backup your db before running it. It's set to increment by 1, but you can change it - see the code below for the line.

    Create a php file in the root of your domain with this code (get the db username, password, and name from your wp-config.php file):

    <?php
    $db_server       = 'localhost' ;
    $db_username     = '' ;
    $db_password     = '' ;
    $db_databasename = '' ;
    
    $link = mysql_connect($db_server, $db_username, $db_password);
    mysql_select_db($db_databasename);
    
    $qry = "SELECT post_id, meta_value FROM <code>wp_postmeta</code> where meta_key = 'mp_price' and post_id in (select id from wp_posts where post_type = 'product')";
    
    $result = mysql_query($qry) or die(mysql_error());
    
    while($row = mysql_fetch_array($result)){
    	$val = $row['meta_value'];
    	$pos = strpos($val, '"');
    	$pos2 = strpos($val, '"', $pos+1);
    	$price = substr($val, $pos+1, $pos2-$pos-1);
    	$price = $price + 1; //Change the 1 to whatever you want to increment the price by
    	$qry2 = "UPDATE <code>wp_postmeta</code> SET meta_value = '".substr($row['meta_value'], 0, $pos+1).$price.substr($row['meta_value'], $pos2, strlen($row['meta_value'])-$pos2)."' where post_id = '".$row['post_id']."' and meta_key = 'mp_price'";
    	//echo $qry2.'<br>';
    	$result2 = mysql_query($qry2) or die(mysql_error());
    }
    
    mysql_close($link);
    ?>

    Once the file is saved just open it in your browser like this:
    http://mydomain.com/fixprices.php

    LMK how this goes for you...

    Rob

  • digitsoft

    @Joe - I can't edit the posts any longer???

    Anyway...you need to change the line in the code above from this:
    $qry = "SELECT post_id, meta_value FROM <code>wp_postmeta</code> where meta_key = 'mp_price' and post_id in (select id from wp_posts where post_type = 'product')";

    to this

    $qry = "SELECT post_id, meta_value FROM 'wp_postmeta' where meta_key = 'mp_price' and post_id in (select id from wp_posts where post_type = 'product')";

    replace the ' around wp_postmeta with the slanted apostrophe to the left of the 1 on the keyboard - I can't put it here since it's what designates code within a post.

  • aecnu

    Greetings rydelano,

    We have not heard back from you as to the status of this issue.

    If you are still having an issue please let us know so that we may try to get you fixed up as soon as possible by choosing to check mark this ticket as unresolved below and posting any new errors or symptoms you are noticing.

    This action will also bring your ticket up front back in plain view again within the ticket system.

    Thank you for being a WPMU Dev member!

    Cheers, Joe

  • Andris
    <?php
    $db_server       = '' ;
    $db_username     = '' ;
    $db_password     = '' ;
    $db_databasename = '' ;
    
    $link = mysql_connect($db_server, $db_username, $db_password);
    mysql_select_db($db_databasename);
    
    $qry = "SELECT post_id, meta_value FROM <code>wp_postmeta</code> where meta_key = 'mp_price' and post_id in (select id from wp_posts where post_type = 'product')";
    
    $result = mysql_query($qry) or die(mysql_error());
    
    while($row = mysql_fetch_array($result)){
    	$val = $row['meta_value'];
    	$pos = strpos($val, '"');
    	$pos2 = strpos($val, '"', $pos+1);
    	$price = substr($val, $pos+1, $pos2-$pos-1);
    	$price = $price / 0.702804 ; //Change the 1 to whatever you want to increment the price by
    	$qry2 = "UPDATE <code>wp_postmeta</code> SET meta_value = '".substr($row['meta_value'], 0, $pos+1).$price.substr($row['meta_value'], $pos2, strlen($row['meta_value'])-$pos2)."' where post_id = '".$row['post_id']."' and meta_key = 'mp_price'";
    	//echo $qry2.'<br>';
    	$result2 = mysql_query($qry2) or die(mysql_error());
    }
    
    mysql_close($link);
    ?>

    I tried to use this code from your specified record just by changing the from incrementing by 1 to division by 0.702804. But what it did, it changed all prices to 0, so something went wrong.
    What I must accomplish is that I need divide all prices and sale prices by 0.702804. How could I do that?

  • Alexander

    Hi @Andris,

    I'm sorry, we missed this one as it is quite an old thread. Do you still need help here? If so, would you mind opening up a new thread for this? That way we don't miss anything, and can be courteous to the users above. This way they won't be receiving post notifications from our conversation - long after the topic was originally raised.

    It's also easier for us to keep track of newer threads.

    You can start a new topic here: https://premium.wpmudev.org/forums/#question

    Thanks.

  • Jolandak

    Hi,

    I have been working on bulk editing prices in Marketpress using the solution mentioned here.
    It's been difficult but I have managed to get some result.
    But.....

    It's not completely effective and I could use some help.
    The code that I have used is the following:

    <?php
    $db_server       = ' ' ;
    $db_username     = ' ' ;
    $db_password     = ' ' ;
    $db_databasename = ' ' ;
    
    $link = mysql_connect($db_server, $db_username, $db_password);
    mysql_select_db($db_databasename);
    
    $qry = "SELECT post_id, meta_value FROM <code>wp_postmeta</code> where meta_key = 'mp_price' and post_id in (select id from wp_posts where post_type = 'product')";
    
    $result = mysql_query($qry) or die(mysql_error());
    while($row = mysql_fetch_array($result)){
    	$val = $row['meta_value'];
    
    	$string_length = strlen($val);
    	$string_length = $string_length -11;
    	$chars_start = substr($val, 0, - $string_length );
    	$chars_end = substr($val, -2);
    	$price = substr($val, 11, -2);
    	$price = round($price, 2);
    
    	//echo $val . "<br/>";
    	//echo $price . "<br/>";
    	//echo $chars_start . $price . $chars_end . "<br/>";
    
    	$updated_price = $price *1.05;
    	$updated_price = round($updated_price, 2);
    	$update = $chars_start . $updated_price . $chars_end;
    	//echo $update . "<br/>";
    	$query = "UPDATE <code>wp_postmeta</code> SET meta_value = '$update' WHERE post_id = '".$row['post_id']."' and meta_key = 'mp_price' ";
    	echo $query . "<br/>";
    	$result2 = mysql_query($query) or die(mysql_error());
    }
    
    mysql_close($link);
    ?>

    What it does is change all the prices, but it messes up the sales prices. So any normal product with no extra discount is okay, but the ones that have a regular price and a sale price don't add up. Price goes to € 0,00 and the sale price becomes the former price.

    Second problem:
    It doesn't work on product variations. They all set to € 0,00

    Hopefully someone here can help me figure this out.

    Best regards,
    Jolanda