Solved

Parse and Update Query

Posted on 2011-03-04
7
199 Views
Last Modified: 2012-05-11

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

0
Comment
Question by:rgranlund
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:Chris Harte
ID: 35043308
What do you mean stops? Are you getting an error message?
0
 
LVL 17

Expert Comment

by:Chris Harte
ID: 35043337
Also at line 142 echo both $new_103 and $old_103 to check that they are different.
0
 
LVL 7

Author Comment

by:rgranlund
ID: 35044180
It creates the array but seems to stop the update process after the first item.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 35046502
There is something very wrong here.  Either we are missing some of the code or the $url variable on line 66 is undefined.  The PHP function file_get_contents() provides a return value.  You might want to test the return values from functions.  The PHP man page tells you how to do that.  See the Return Values and the Error/Exceptions:
http://us.php.net/manual/en/function.file-get-contents.php

Also, you have a class definition (line 91) inside an iterator (line 72).  I do not know whether that will work or not, but I have never seen anyone do that.

This code would be a lot easier to read and understand if you use a consistent coding standard.  The Zend coding standard is a good one to adopt (I use a slight modification of that).
http://framework.zend.com/manual/en/coding-standard.html

Best regards, ~Ray
0
 
LVL 7

Author Comment

by:rgranlund
ID: 35061507
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

0
 
LVL 7

Author Comment

by:rgranlund
ID: 35061689
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

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35061866
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?
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question