Link to home
Start Free TrialLog in
Avatar of Robert Granlund
Robert GranlundFlag for United States of America

asked on

Parse and Update Query


I have been working on this.  There is an XML feed being parsed the information is being compared and then if the information is new, the table is updated.  Please look at this code.  It seems to run all the way through but stops at the first one.  I can't seem to find where my flaw is.

<?php 
// CONNECT AND SELECT THE DATA BASE
set_error_handler ('oops'); 

// custom error handler 
function oops($type, $msg, $file, $line, $context) { 
	switch ($type) { 
// notices 
	case E_NOTICE: 
	break; 
// warnings 
	case E_WARNING: 
// report error 
	print "Non-fatal error on line $line of $file: $msg <br />"; 
	break; 
// other 
	default: 
	print "Error of type $type on line $line of $file: $msg <br />"; 
	break; 
	} 
} 
error_reporting(E_ALL);

include('/connect.php');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update and Report</title>
<style>
body	{
	background-color:#D9DCF0;
}

fieldset	{
	border:double 5px #333333;
	padding:9px;
}

#wrapper	{
	width:850px;
	padding:20px;
	margin:0px auto 0px auto;
}

.content	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:13px;
	font-weight:600;
	color:#333333;
}
</style>
</head>

<body>

<!--  WRAPPER  -->
<div id="wrapper" class="content">
<?php
echo "<h1>Update and Report</h1><br /><br /><fieldset>";
// GET THE XML FEED IN A STRING
$xml = file_get_contents($url);
 
// MAKE AN OBJECT OUT OF THE XML
$obj = SimpleXML_Load_String($xml);
 

foreach ($obj->model as $model)
{  //  FOR EACH
	$field_id_30 = (string)$model->brand;
	$field_id_101 = (string)$model->prodSubType;
	$field_id_102 = (string)$model->prodType;
	$field_id_103 = (string)$model->prodName;
	$field_id_104 = (string)$model->prodNo;
	
	$field_id_30 = mysqli_real_escape_string($dbc, "$field_id_30");
	$field_id_101 = mysqli_real_escape_string($dbc, "$field_id_101");
	$field_id_102 = mysqli_real_escape_string($dbc, "$field_id_102");
	$field_id_104 = mysqli_real_escape_string($dbc, "$field_id_104");
    $new_103 = mysqli_real_escape_string($dbc, "$field_id_103");
	if ($field_id_30=='Fender' && $field_id_101=='Electric Guitars' && $field_id_102=='Guitars') {  //  MAIN IF  //

//  END OF PARSE AND PREP
	

// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS
			class Changes
			{
    			protected $field_id_104, $old_103, $new_103;

   				public function __construct($field_id_104)
    			{
        		$this->field_id_104 = $field_id_104;
        		$this->old_103 = NULL;
        		$this->new_103 = NULL;
    			}

   			 	public function set_103($old_103, $new_103)
    			{
        		$this->old_103 = $old_103;
       			$this->new_103 = $new_103;
    			}


    			public function get_change_message()  //  GET CHANGE MESSAGE  //
    			{
        // SET THE MESSAGE TO THE OBJECT'S nom ONLY
       			 $msg = $this->field_id_104;

        // ADD TO THE MESSAGE FOR ANY CHANGES
       				 if ($this->old_103)
        			{
            		$msg .= "<br /> Product Name <strong>CHANGED FROM</strong><br /> $this->old_103 <br /><strong>TO:</strong><br /> $this->new_103<br />";
        			}
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
       				if ($msg == $this->field_id_104)
        			{
            		$msg .= " NO CHANGES";
        			}
       	 			return $msg;
    			}  //  END GET CHANGE MESSAGE  //
			}  //  END CHANGES  //

// OUR CHANGELOG IS AN ARRAY OF OBJECTS
		$changelog = array();
		
    // SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
   		$q = "SELECT field_id_103 FROM exp_weblog_data WHERE field_id_104 = '$field_id_104' LIMIT 1";
    	$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		$row = mysqli_fetch_assoc($r);
    	$old_103 = $row["field_id_103"];

echo "SELECT Query<br />";
echo $old_103;
    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    	$c = new Changes($field_id_104);
    		if ($new_103 != $old_103) $c->set_103($old_103, $new_103);
    		$changelog[] = $c;

//echo PHP_EOL . "<br/><br />"; 
echo "<fieldset><br />";
var_dump($c);
echo $new_103;
echo "<br /></fieldset>";

    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
   		$q = "UPDATE exp_weblog_data SET field_id_103 = '$new_103' WHERE field_id_104='$field_id_104' LIMIT 1";
    	$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		}  
//  END ITERATE OVER THE UPDATE QUERY DATA  //


// UPDATES COMPLETE, DUMP THE TABLE TO SHOW ALL THE NEW VALUES
		$q = "SELECT field_id_104, field_id_103 FROM exp_weblog_data";
		$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
			while ($row = mysqli_fetch_assoc($r))
			{
//echo PHP_EOL . "<br/><br />"; 
//echo "<fieldset><br />";
//var_dump($row);
//echo "<br /></fieldset>";
			}

// NOW SHOW THE CHANGELOG
				foreach ($changelog as $change_object)
				{
				$msg = $change_object->get_change_message();
    			echo "<br /><fieldset><br/>$msg" . PHP_EOL . "<br /></fieldset><br />";
				}

	}  //  MAIN IF  //

echo "<br /><br /></fieldset>";
?>

</div>
<!--  END WRAPPER  -->
</body>
</html>

Open in new window

Avatar of Chris Harte
Chris Harte
Flag of United Kingdom of Great Britain and Northern Ireland image
What do you mean stops? Are you getting an error message?
Avatar of Chris Harte
Chris Harte
Flag of United Kingdom of Great Britain and Northern Ireland image
Also at line 142 echo both $new_103 and $old_103 to check that they are different.
Avatar of Robert Granlund
Robert Granlund
Flag of United States of America image

ASKER

It creates the array but seems to stop the update process after the first item.
ASKER CERTIFIED SOLUTION
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Granlund
Robert Granlund
Flag of United States of America image

ASKER

Here is a much cleaner version:  It is almost there and the suggestions are helping alot!

include('/fmicdev/html/priv/config/customshop/connect/cs_sku_parse_connect.php');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update and Report</title>
<style>
body	{
	background-color:#D9DCF0;
}

fieldset	{
	border:double 5px #333333;
	padding:9px;
}

#wrapper	{
	width:850px;
	padding:20px;
	margin:0px auto 0px auto;
}

.content	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:13px;
	font-weight:600;
	color:#000000;
}

.green	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:14px;
	font-weight:600;
	color:#009900;
}

.bold	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:15px;
	font-weight:600;
	color:#000000;
}

</style>
</head>

<body>

<!--  WRAPPER  -->
<div id="wrapper" class="content">
<?php
echo "<h1>Update and Report</h1><br /><br /><fieldset>";

// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS
class Changes
{
	protected $prodNo, $old_prodName, $new_prodName;

   	public function __construct($prodNo)
    {
    	$this->prodNo = $prodNo;
    	$this->old_prodName = NULL;
		$this->new_prodName = NULL;
    }

   	public function set_prodName($old_prodName, $new_prodName)
    {
		$this->old_prodName = $old_prodName;
		$this->new_prodName = $new_prodName;
    }

	public function get_change_message()  //  GET CHANGE MESSAGE  //
    {
        // SET THE MESSAGE TO THE OBJECT'S nom ONLY
		$msg = $this->prodNo;

        // ADD TO THE MESSAGE FOR ANY CHANGES
       		if ($this->old_prodName) {
				$msg .= "<br /> <span class=\"green\">PRODUCT NAME: $this->new_prodName<br /><strong>CHANGED FROM</strong><br /> $this->old_prodName <br /><strong>TO:</strong><br /> $this->new_prodName</span><br />";
			}
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
			if ($msg == $this->prodNo) {
            	$msg .= " <br /><span class=\"bold\">NO CHANGES:<br />$this->new_prodName </span><br />The guitar may need to have the GUITAR PARSE";
        	}
       	 	return $msg;
			
	}  //  END GET CHANGE MESSAGE  //
}  //  END CHANGES  //
$changelog = array();						
// GET THE XML FEED IN A STRING
$xml = file_get_contents($url);
 
// MAKE AN OBJECT OUT OF THE XML
$obj = SimpleXML_Load_String($xml);
 

foreach ($obj->model as $model) {  //  FOR EACH
	$brand = (string)$model->brand;
	$subType = (string)$model->prodSubType;
	$prodType = (string)$model->prodType;
	$prodName = (string)$model->prodName;
	$prodNo = (string)$model->prodNo;
	
    $prodName = mysqli_real_escape_string($dbc, "$prodName");
	
    if ($brand=='Fender' && $subType=='Electric Guitars' && $prodType=='Guitars') {  //  MAIN IF  //

//  END OF PARSE AND PREP
	
		
		
    // SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
   		$q = "SELECT field_id_103, entry_id
			FROM exp_weblog_data
			WHERE field_id_104 = '$prodNo'
			AND weblog_id ='11'
			LIMIT 1";
    	$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		$row = mysqli_fetch_assoc($r);
    	$old_prodName = $row["field_id_103"];
    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    	$c = new Changes($prodNo);
    		if ($prodName != $old_prodName) $c->set_prodName($old_prodName, $new_prodName);
    		$changelog[] = $c;

    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
   		$q = "UPDATE exp_weblog_data
			SET field_id_103 = '$prodName'
			WHERE field_id_104='$prodNo'
			LIMIT 1";
    	$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		
	}  //  END MAIN IF  //  
}  //  END MAIN FOREACH  //


// NOW SHOW THE CHANGELOG
foreach ($changelog as $change_object) {
	$msg = $change_object->get_change_message();
	echo "<br /><fieldset>";
    echo "<br />$msg" . PHP_EOL . "<br /></fieldset><br />";
}



echo "<br /><br /></fieldset>";
?>

</div>
<!--  END WRAPPER  -->
</body>
</html>

Open in new window

Avatar of Robert Granlund
Robert Granlund
Flag of United States of America image

ASKER

This is a better example.  It goes through the entire code and reports that it has updated but when I look at the database, the old info is still there. It gives me the same message everytime.

include('/connect.php');

// Make the connection:
$dbc = @mysqli_connect (DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die ('Could not connect to MySQL: ' . mysqli_connect_error() );
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Update and Report</title>
<style>
body	{
	background-color:#D9DCF0;
}

fieldset	{
	border:double 5px #333333;
	padding:9px;
}

#wrapper	{
	width:850px;
	padding:20px;
	margin:0px auto 0px auto;
}

.content	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:13px;
	font-weight:600;
	color:#000000;
}

.green	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:14px;
	font-weight:600;
	color:#009900;
}

.bold	{
	font-family:Arial, Helvetica, sans-serif;
	font-size:15px;
	font-weight:600;
	color:#000000;
}

</style>
</head>

<body>

<!--  WRAPPER  -->
<div id="wrapper" class="content">
<?php
echo "<h1>Update and Report</h1><br /><br /><fieldset>";

// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS
class Changes
{
	protected $prodNo, $old_prodName, $new_prodName;

   	public function __construct($prodNo)
    {
    	$this->prodNo = $prodNo;
    	$this->old_prodName = NULL;
		$this->new_prodName = NULL;
    }

   	public function set_prodName($old_prodName, $new_prodName)
    {
		$this->old_prodName = $old_prodName;
		$this->new_prodName = $new_prodName;
    }

	public function get_change_message()  //  GET CHANGE MESSAGE  //
    {
        // SET THE MESSAGE TO THE OBJECT'S nom ONLY
		$msg = $this->prodNo;

        // ADD TO THE MESSAGE FOR ANY CHANGES
       	if ($this->old_prodName) {
			$msg .= "<br /> <span class=\"green\">PRODUCT NAME CHANGED FROM<br /> 
				$this->old_prodName<br />
				TO:<br /> $this->new_prodName </span><br />";
		}
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
		if ($msg == $this->prodNo) {
            $msg .= " <br /><span class=\"bold\">NO CHANGES:<br />$this->new_prodName </span><br />The guitar may need to have the GUITAR PARSE";
        }
       	 	return $msg;
			
	}  //  END GET CHANGE MESSAGE  //
}  //  END CHANGES  //
$changelog = array();						
// GET THE XML FEED IN A STRING
$xml = file_get_contents($url);
 
// MAKE AN OBJECT OUT OF THE XML
$obj = SimpleXML_Load_String($xml);
 

foreach ($obj->model as $model) {  //  FOR EACH
	$brand = (string)$model->brand;
	$subType = (string)$model->prodSubType;
	$prodType = (string)$model->prodType;
	$new_prodName = (string)$model->prodName;
	$prodNo = (string)$model->prodNo;
	
    $prodName = mysqli_real_escape_string($dbc, "$prodName");
	
    if ($brand=='Fender' && $subType=='Electric Guitars' && $prodType=='Guitars') {  //  MAIN IF  //

//  END OF PARSE AND PREP
	
		
		
    // SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
   		$q = "SELECT field_id_103, entry_id
			FROM exp_weblog_data
			WHERE field_id_104 = '$prodNo'
			AND weblog_id ='11'
			LIMIT 1";
    	$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		$row = mysqli_fetch_assoc($r);
    	$old_prodName = $row["field_id_103"];
    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    	$c = new Changes($prodNo);
    		if ($new_prodName != $old_prodName) $c->set_prodName($old_prodName, $new_prodName);
    		$changelog[] = $c;

    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
   		$q = "UPDATE exp_weblog_data
			SET field_id_103 = '$prodName'
			WHERE field_id_104='$prodNo'
			LIMIT 1";
    	$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
		
	}  //  END MAIN IF  //  
}  //  END MAIN FOREACH  //


// NOW SHOW THE CHANGELOG
foreach ($changelog as $change_object) {
	$msg = $change_object->get_change_message();
	echo "<br /><fieldset>";
    echo "<br />$msg" . PHP_EOL . "<br /></fieldset><br />";
}



echo "<br /><br /></fieldset>";
?>

</div>
<!--  END WRAPPER  -->
</body>
</html>

Open in new window

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image
Have you tried testing for the update count with this?
http://us3.php.net/manual/en/mysqli.affected-rows.php

And are you using error_reporting(E_ALL) to catch undefined variables?