Solved

Parse and Update Query

Posted on 2011-03-04
7
195 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 16

Expert Comment

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

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 108

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 108

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to count occurrences of each item in an array.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now