Solved

Parse and Update Query

Posted on 2011-03-04
7
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 110

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 110

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

733 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