Back To Programming / Scripting / Coding Forum

having huge trouble with WP e-Commerce mass price update

  • Zealous
  • Guru
  • Guru
  • User avatar
  • Joined: Apr 15, 2011
  • Posts: 1195
  • Loc: Sydney
  • Status: Offline

Post March 18th, 2013, 9:34 am

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.
  • Anonymous
  • Bot
  • No Avatar
  • Joined: 25 Feb 2008
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post March 18th, 2013, 9:34 am

  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Joined: Dec 20, 2002
  • Posts: 8926
  • Loc: Seattle, WA & Phoenix, AZ
  • Status: Offline

Post March 18th, 2013, 9:56 am

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?
Ozzu Hosting - Want your website on a fast server like Ozzu?
  • Zealous
  • Guru
  • Guru
  • User avatar
  • Joined: Apr 15, 2011
  • Posts: 1195
  • Loc: Sydney
  • Status: Offline

Post March 18th, 2013, 8:30 pm

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
  • Joined: Apr 15, 2011
  • Posts: 1195
  • Loc: Sydney
  • Status: Offline

Post March 19th, 2013, 12:43 am

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.  }
  8. mysql_select_db("database_select", $con);
  10. $result = mysql_query('UPDATE wp_postmeta
  11.                                 SET meta_value = meta_value * 2.0
  12.                                 WHERE meta_value = meta_value AND meta_key = "_wpsc_price";');
  14. echo "<table border='1'>
  15. <tr>
  16. <th>meta_key</th>
  17. <th>meta_value</th>
  18. </tr>";
  20. while($row = mysql_fetch_array($result))
  21.  {
  22.  echo "<tr>";
  23.  echo "<td>" . $row['meta_key'] . "</td>";
  24.  echo "<td>" . $row['meta_value'] . "</td>";
  25.  echo "</tr>";
  26.  }
  27. echo "</table>";
  29. mysql_close($con);
  30. ?>
  • Bigwebmaster
  • Site Admin
  • Site Admin
  • User avatar
  • Joined: Dec 20, 2002
  • Posts: 8926
  • Loc: Seattle, WA & Phoenix, AZ
  • Status: Offline

Post March 19th, 2013, 10:16 am

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!
Ozzu Hosting - Want your website on a fast server like Ozzu?
  • Zealous
  • Guru
  • Guru
  • User avatar
  • Joined: Apr 15, 2011
  • Posts: 1195
  • Loc: Sydney
  • Status: Offline

Post March 19th, 2013, 8:47 pm

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.

Page 1 of 1

Post Information

  • Total Posts in this topic: 6 posts
  • Users browsing this forum: No registered users and 121 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
 
cron
 

© 2011 Unmelted, LLC. Ozzu® is a registered trademark of Unmelted, LLC.