You are here:  » Get data from table to


Get data from table to

Submitted by cfrazeedbsi on Fri, 2010-03-05 13:27 in

Hi Dave,

I am in the final stages with my multiple xml parse and found that I need to pull information from an existing table to do a calculation. I have the calculation working in another script and it works great pulling all data from the xml and I am able to pull the information from the table in an independent script, but I am not selecting the data if id equals something specific.

Quick note on identifiers below:
GS_Data is the existing table
id is the table I want to compare to from the XML
MSRP & MAP are the fields in the table
PRICE & INVENTORYID are fields in the XML

<?php
error_reporting
(E_ERROR);
ini_set("display_errors","1");
    
$xml "my-gscount.xml";
    
$link mysql_connect("info removed for privacy") or die(mysql_error());
    
mysql_select_db("C285178_sunstore",$link) or die(mysql_error());
    
$data mysql_query("SELECT * FROM GS_Data") or die(mysql_error());
    
$info mysql_fetch_array$data ); 
  require(
"MagicParser.php");
function 
myRecordHandler($record)
{
        global 
$link;
        global 
$info;
        global 
$data;
        
$search = array("C","D");
        
$replace = array("y","n");
        
$record["STATUS"] = str_replace($search,$replace,$record["STATUS"]);    
        
$sql "SELECT id FROM GS_Data WHERE id = '".mysql_escape_string($record["INVENTORYID"])."'";
            if (
mysql_num_rows(mysql_query($sql,$link)))
            {                
                                        if (
$info["MAP"] <= 0)
                    {
                        
$cf_price = (($info ["MSRP"] - $record["PRICE"]) * .8) + $record["PRICE"];
                    }
                    else
                    {
                        
$cf_price = (($info ["MSRP"] - $record['PRICE']) * .8) + $record["PRICE"];
                        if (
$cf_price <= $info ["MAP"])
                        {
                            
$cf_price $info ["MAP"];
                        }
                        else
                        {
                            
$cf_price $cf_price;
                        }
                    }
            }
            else
            {
                print 
"did not match";
            }
          
mysql_query($sql,$link);
          print 
"MySQL Error Message: ".mysql_error($link);
        
// start enter main product information
            
$sql "SELECT id FROM products WHERE id = '".mysql_escape_string($record["INVENTORYID"])."'";
            if (
mysql_num_rows(mysql_query($sql,$link)))
            {
              
$sql "UPDATE products SET
                sku = '"
.mysql_escape_string($record["SKU"])."',
                orderable = '"
.mysql_escape_string($record["STATUS"])."',
                in_stock = '"
.mysql_escape_string($record["COUNT"])."',
                price =  '"
.mysql_escape_string($cf_price)."'
                        WHERE
                id = '"
.mysql_escape_string($record["INVENTORYID"])."'
                        "
;
            }
            else
            {
                print 
"nothing Entered <br>";
            }
            
mysql_query($sql,$link);
         
//   print $sql;
            
print "<br />";
            print 
"MySQL Error Message: ".mysql_error($link);
}
  
MagicParser_parse($xml,"myRecordHandler","xml|items/vwGSInvCounts/");
  print 
"<br />";
  print 
"complete";
?>

Submitted by support on Fri, 2010-03-05 14:21

Hi,

I'm not sure if something went wrong when posting your PHP code; but regarding this section:

$sql = "SELECT id FROM GS_Data WHERE id = '".mysql_escape_string($record["INVENTORYID"])."'";
if (mysql_num_rows(mysql_query($sql,$link)))
{
  if ($info["MAP"] <= 0)
  {
    $cf_price = (($info ["MSRP"] - $record["PRICE"]) * .8) + $record["PRICE"];
  }
  else
  {
    $cf_price = (($info ["MSRP"] - $record['PRICE']) * .8) + $record["PRICE"];
    if ($cf_price <= $info ["MAP"])
    {
      $cf_price = $info ["MAP"];
    }
    else
    {
      $cf_price = $cf_price;
    }
}

...it looks like it is missing the line to fetch the result into $info. Have a go with:

$sql = "SELECT id FROM GS_Data WHERE id = '".mysql_escape_string($record["INVENTORYID"])."'";
$result = mysql_query($sql,$link);
if (mysql_num_rows($result))
{
  $info = mysql_fetch_assoc($result);
  if ($info["MAP"] <= 0)
  {
    $cf_price = (($info ["MSRP"] - $record["PRICE"]) * .8) + $record["PRICE"];
  }
  else
  {
    $cf_price = (($info ["MSRP"] - $record['PRICE']) * .8) + $record["PRICE"];
    if ($cf_price <= $info ["MAP"])
    {
      $cf_price = $info ["MAP"];
    }
    else
    {
      $cf_price = $cf_price;
    }
}

Hope this helps!
Cheers,
David.