having huge trouble with WP e-Commerce mass price update

  • Zealous
  • Guru
  • Guru
  • User avatar
  • Posts: 1244
  • Loc: Sydney

Post 3+ Months Ago

I have got a WP shop working and finally got it back to updated but the prices are still at wholesale and not retail prices.

I found the update scripts but they are pointing in the wrong direction.

I am sure that this is the one that multiplies the price but it can't find the table.

#1146 - Table 'mclovin.wp_wpsc_price' doesn't exist

SQL
Code: [ Select ]
SELECT meta_key, meta_value
                            FROM wp_postmeta
                            WHERE meta_key = "_wpsc_price"
                            SET meta_value = meta_value * 2.0
                            ORDER BY meta_value
  1. SELECT meta_key, meta_value
  2.                             FROM wp_postmeta
  3.                             WHERE meta_key = "_wpsc_price"
  4.                             SET meta_value = meta_value * 2.0
  5.                             ORDER BY meta_value


I am all most at the point where i need to pay someone to break this down for me. As the SQL is all over the place. But help would be appreciated to sort a update script or SQL command to update the 5k of products in 1 hit.

Site is running off WP with WP e-Commerce for the shop back end.

if anyone has any idea please post up.
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9092
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

So I am guessing you recently updated the WP e-commerce shop as well. Do you know what version you were at before, and what version you are at now?
  • Zealous
  • Guru
  • Guru
  • User avatar
  • Posts: 1244
  • Loc: Sydney

Post 3+ Months Ago

I have reverted back to a backup

Currant Version 3.8.8.5

There is a new version of WP e-Commerce available. 3.8.9.5 details
  • Zealous
  • Guru
  • Guru
  • User avatar
  • Posts: 1244
  • Loc: Sydney

Post 3+ Months Ago

WWWWWWWWWWWWEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE

I am a champion

the person who made that last SQL update script, changed it before giving control over so it was pointing to a non existing table. After a little consultation and looking around the SQL.

Found that wp_postmeta contained the data then changed the meta_value x2/double price where _wpsc_price was found in meta keys. Changed all 4200 products prices in half a second.

Code: [ Select ]
UPDATE wp_postmeta
SET meta_value = meta_value * 2.0
WHERE meta_value = meta_value AND meta_key = "_wpsc_price";
  1. UPDATE wp_postmeta
  2. SET meta_value = meta_value * 2.0
  3. WHERE meta_value = meta_value AND meta_key = "_wpsc_price";


then to top it off you can add this into a php update script to make life easy and don't have to login to Mysql. Just upload run ONCE! and delete when done.

I have been looking at this problem for a while now and i am ssoo happy i got it sorted :) also hopes this post helps others and i could not find much information on this what so ever.
Code: [ Select ]
<?php
$con = mysql_connect("localhost","db_user","db_pwd");
if (!$con)
 {
 die('Could not connect: ' . mysql_error());
 }

mysql_select_db("database_select", $con);

$result = mysql_query('UPDATE wp_postmeta
                                SET meta_value = meta_value * 2.0
                                WHERE meta_value = meta_value AND meta_key = "_wpsc_price";');

echo "<table border='1'>
<tr>
<th>meta_key</th>
<th>meta_value</th>
</tr>";

while($row = mysql_fetch_array($result))
 {
 echo "<tr>";
 echo "<td>" . $row['meta_key'] . "</td>";
 echo "<td>" . $row['meta_value'] . "</td>";
 echo "</tr>";
 }
echo "</table>";

mysql_close($con);
?>
  1. <?php
  2. $con = mysql_connect("localhost","db_user","db_pwd");
  3. if (!$con)
  4.  {
  5.  die('Could not connect: ' . mysql_error());
  6.  }
  7. mysql_select_db("database_select", $con);
  8. $result = mysql_query('UPDATE wp_postmeta
  9.                                 SET meta_value = meta_value * 2.0
  10.                                 WHERE meta_value = meta_value AND meta_key = "_wpsc_price";');
  11. echo "<table border='1'>
  12. <tr>
  13. <th>meta_key</th>
  14. <th>meta_value</th>
  15. </tr>";
  16. while($row = mysql_fetch_array($result))
  17.  {
  18.  echo "<tr>";
  19.  echo "<td>" . $row['meta_key'] . "</td>";
  20.  echo "<td>" . $row['meta_value'] . "</td>";
  21.  echo "</tr>";
  22.  }
  23. echo "</table>";
  24. mysql_close($con);
  25. ?>
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Posts: 9092
  • Loc: Seattle, WA & Phoenix, AZ

Post 3+ Months Ago

It is always interesting how when someone is about to finally give up and post their problem on Ozzu, then they figure it out themselves usually within 24 hours ;)

Awesome, glad you resolved it!
  • Zealous
  • Guru
  • Guru
  • User avatar
  • Posts: 1244
  • Loc: Sydney

Post 3+ Months Ago

Bigwebmaster wrote:
It is always interesting how when someone is about to finally give up and post their problem on Ozzu, then they figure it out themselves usually within 24 hours ;)

Awesome, glad you resolved it!


It is good to see eh, but i could of used this a while ago but finally dedicated myself to finding the problem one last time.

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 95 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.