mySQL help needed

[Twaddle]

Not technically a Zen Cart issue as such, but I'm trying to run a stock updater script (supposedly works on ZC) that should change the RRP in my database by taking an xml feed, but so far not having a lot of luck with it.

There's no errors when I run it, other than a long list of all the products saying,

"[FONT=verdana]Product A price not updated to 13.99"
[/FONT]"[FONT=verdana]Product B price not updated to 8.99"
[/FONT]"[FONT=verdana]Product C price not updated to 6.99"[/FONT] etc etc.

Am I supposed be changing the table prefixes and/or names somewhere within this script to match my set up? Or something else?

<?php
  
  echo "<title>Stock Manager</title>
<center>
<table width=900 border=1 cellpadding=5 bordercolor=#9966ff><td><FONT SIZE=2 face=verdana COLOR=BLUE>Click here to set all your products to the RRP<BR></font><br><a href=\"free_price_updater.php?do=go\"></a><br><br>
<FONT SIZE=2 face=verdana COLOR=BLUE>This update is based on your product code.<br><br></div>";

  $alllocalproductmodels;
  $instockflag;
  $productname = "";
  $model = "";
  $allxmlproducts = array();
  $allDiscontProducts = array();
  require('includes/configure.php');
  global $show_output, $instockflag, $model, $productname, $tag;
  
  if ($_REQUEST['do'] == "go") {
      checkStock(1);
  }
  echo "";
  echo "<BR><BR><FONT SIZE=2 face=verdana COLOR=BLUE></FONT><BR></td></tr></table><BR><BR>";
  function checkStock($showOutput)
  {
      global $price, $productname, $pre_model_value, $show_output;
      $show_output = $showOutput;
      connecttodatabase();
      $xmlstockfeed = "http://xxxx.com/catalog/xml_id.xml";
      $xmlfile = $xmlstockfeed;
      $xml_parser = xml_parser_create();
      xml_set_element_handler($xml_parser, "startElement2", "endElement2");
      xml_set_character_data_handler($xml_parser, "characterData2");
      $data = get_content($xmlfile);
      xml_parse($xml_parser, $data) or die(sprintf("XML error: %s at line %d", xml_error_string(xml_get_error_code($xml_parser)), xml_get_current_line_number($xml_parser)));
      xml_parser_free($xml_parser);
  }
  function startElement2($parser, $name, $attrs)
  {
      global $price, $model, $productname, $tag;
      $tag = $name;
      switch ($name) {
          case "PRODUCT":
              $productname = $attrs[NAME];
              $model = $attrs[ITEM];
              break;
              echo "product name: $productname <br>";
      }
  }
  function characterData2($parser, $data)
  {
      global $price, $model, $productname, $tag;
      switch ($tag) {
          case "RRP":
              $price = $data;
              break;
          case "MODEL":
              $model = $data;
              break;
          case "NAME":
              $productname = $data;
              break;
      }
  }
  function endElement2($parser, $name)
  {
      global $price, $instockflag, $model, $productname, $tag, $pre_model_value;
      switch ($name) {
          case "PRODUCT":
              $q = "SELECT products_model FROM products where products_model='$model'";
              $result = mysql_query($q);
              $rows = mysql_numrows($result);
              $q = "UPDATE products SET products_price='$price' WHERE products_model='$model'";
              if (!db_query($q)) {
                  echo "<br><font color=red>$productname price not updated to $price</font><br>";
              } else {
                  if ($rows > 0) {
                      echo " <br><img src=http://xxxx.com/catalog/images/flash.gif>&nbsp; <font color=green>$productname price set to $price</font><br>";
                  } else {
                      echo "<br><img src=http://xxxx.com/catalog/images/notfound.gif>&nbsp; <font color=red>$productname. Code: $model not found in your shop. <a href=http://xxxx.com/catalog/advanced_search_result.php?keywords=$model target=blank> View Here</a></font><br>";
                  }
              }
              $price = "";
              $model = "";
              $productname = "";
      }
  }
  function get_content($url)
  {
      $ch = curl_init();
      curl_setopt($ch, CURLOPT_URL, $url);
      curl_setopt($ch, CURLOPT_HEADER, 0);
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
      $string = curl_exec($ch);
      curl_close($ch);
      return $string;
  }
  function connecttodatabase()
  {
      $dbcnx = @mysql_connect(DB_SERVER, $username = DB_SERVER_USERNAME, $password = DB_SERVER_PASSWORD, $database = DB_DATABASE);
      if (!$dbcnx) {
          exit('<p>Unable to connect to the ' . 'database server.Please try later, check the installation guide or contact support</p>');
      }
      if (!@mysql_select_db($database, $dbcnx)) {
          exit('<p>Unable to connect to the ' . 'database server. Please trylater, check the installation guide or contact support.</p>');
      }
  }
  function db_query($query)
  {
      global $b_debugmode;
      $result = mysql_query($query);
      if (!$result) {
          if ($b_debugmode) {
              $message = '<b>Invalid query:</b><br>' . mysql_error() . '<br><br>';
              $message .= '<b>Whole query:</b><br>' . $query . '<br><br>';
              die($message);
          }
      }
      return $result;
  }
?>

[RodG]

Am I supposed be changing the table prefixes and/or names somewhere within this script to match my set up? Or something else?

This script has been written to NOT use table prefixes, so if your database does use them you'll need to modify the script.

Specifically:
line 68 :
$q = "SELECT products_model FROM products where products_model='$model'";

line 71:
$q = "UPDATE products SET products_price='$price' WHERE products_model='$model'"


Change the items in RED to suit your database.

Cheers
Rod.

ps. These are the only immediate/obvious problems I spotted. There may or may not be others.

[Twaddle]

This script has been written to NOT use table prefixes, so if your database does use them you'll need to modify the script.

Specifically:
line 68 :
$q = "SELECT products_model FROM products where products_model='$model'";

line 71:
$q = "UPDATE products SET products_price='$price' WHERE products_model='$model'"


Change the items in RED to suit your database.

Cheers
Rod.

ps. These are the only immediate/obvious problems I spotted. There may or may not be others.

I feel a bit of a chump now, I'd spotted line 68 and added the prefix, but I missed putting it on line 71 to actually update, so no wonder it wasn't working LOL

Cheers Rod, I just ran it again with line 71 corrected and it's updated all 3500 or so products. I'd all but given up on it yesterday. Many thanks!

Oh, and as an added bonus I just noticed, the script actually informs me of which products are discontinued, along with new items available - that's going to save a lot of time/hassle in the future

[RodG]

I feel a bit of a chump now,

Surely there is nothing unusual about that?
<insert suitable smiley here>

Cheers
Rod

[Twaddle]

Surely there is nothing unusual about that?
<insert suitable smiley here>

Perhaps not, but at least I am man enough to admit it

<insert highly unsuitable smiley here>

Cheers
Twaddle