How to update all product prices and sale prices?

Hi @salvisb,

Thank you for your question.

You can change the currency from the path:

Admin -> Products -> Store Settings -> General Tab -> Currency Settings

Unfortunately currently 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 export the database as an sql file 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.

Alternatively you can develop php code to achieve it similar like specified here.

Best Regards,

Could you help write such a script? I`m weak in programming. The thing is that I must update all prices and sale prices by dividing them by appropriate exchange rate.
It would be an easy task if meta_value column would contain only the price without any additional information. In that case I could write simple SQL query.

Hi @salvisb,

Thank you for your reply.

I have provided two solutions.
Are you facing any problem implementing first solution?

Kind Regards,

I can’t understand how first solution could help?

I have 1400 products in one store and about the same amount in two other.

How could I by search and replace method change all of my prices at the same time? Almost each price are different.

I tried second solution.

This line of code:

$price = $price + 1; //Change the 1 to whatever you want to increment the price by

I changed to:

$price = $price / 0.702804 ; //Change the 1 to whatever you want to increment the price by

All prices turned into 0.

For what purpose is those characters used in the price field (example at the beginning: a:1:{i:0;d: , at the end: ;}) ?
If, by updating prices, I lose them, what would happen?

Hi Salvis,

To make the first solution work you have to change the price for all products manually and individually.

Can you just check the solution provided below?

<?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 wp_postmeta 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'];
	$price = unserialize($val);
	$price = $price[0] / 0.702804 ; //Change the 1 to whatever you want to increment the price by
	$qry2 = "UPDATE wp_postmeta SET meta_value = '".serialize($price)."' where post_id = '".$row['post_id']."' and meta_key = 'mp_price'";
	$result2 = mysql_query($qry2) or die(mysql_error());
}

mysql_close($link);
?>

Please back up your database and test it thoroughly on your test site before doing it on live site.

Regards,

Think I found working solution by modifying code from link that you provided.

<?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_lenght = strlen($val);
	$string_lenght = $string_lenght -11;
	$chars_start = substr($val, 0, - $string_lenght );
	$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 / 0.702804;
	$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_sale_price' ";
	echo $query . "<br/>";
	$result2 = mysql_query($query) or die(mysql_error());
}

mysql_close($link);
?>

As far as I tested it works like it should.
Thanks a lot!

But I’am still curious, for what purpose are used a:1:{i:0;d:? They appear the same on every entry. After d follows price, but what about letters “a” and “i”?

Hi Salvis,

Awesome, great to see you made it working.

Thank you for sharing the solution, it will really help others who is searching for the same solution.

But I’am still curious, for what purpose are used a:1:{i:0;d:? They appear the same on every entry. After d follows price, but what about letters “a” and “i”?

After a follows count of number of variations, after d follows price and after i follows 0 based variation index.

You can check it by adding more than one variation for the product.

Cheers.