Solved

PHP Parse Query Update and Report

Posted on 2011-03-03
4
256 Views
Last Modified: 2012-05-11
I have a script that parses a XML Feed.

I am using that feed to populate a database.
I also use the feed to update the database.
Now, I want to have a script that reports what has been updated.

Please look at the following script and give me a hint on what it is I may be doing wrong:

Thanks in advance!

<!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>Untitled Document</title>
<style>
body	{
	background-color:#D9DCF0;
}

fieldste	{
	border:double 3px #333333;
}

#wrapper	{
	width:750px;
	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 
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() );
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_103 = (string)$model->prodName;
	$field_id_104 = (string)$model->prodNo;

	
	$field_id_103 = mysqli_real_escape_string ($dbc, $field_id_103);
	$field_id_104 = mysqli_real_escape_string ($dbc, $field_id_104);
	
//  END OF PARSE AND PREP


// THE DATA FOR SOME UPDATE QUERIES
$usqls   = array();
$usqls[] = array('field_id_104' => '$field_id_104', 'field_id_103' => '$field_id_103'); // CHANGE TWO FIELDS


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

    public function __construct($field_id_104)
    {
        $this->field_id_104 = $field_id_104;
        $this->old103 = NULL;
        $this->new103 = NULL;

    }

    public function set_103($old103, $new103)
    {
        $this->old103 = $old103;
        $this->new103 = $new103;
    }


    public function 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->old103)
        {
            $msg .= "Product Name CHANGED FROM $this->old103 TO $this->new103<br />";
        }
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
        if ($msg == $this->field_id_104)
        {
            $msg .= " NO CHANGES to $this->field_id_104";
        }
        return $msg;
    }
}

// OUR CHANGELOG IS AN ARRAY OF OBJECTS
$changelog = array();


// ITERATE OVER THE UPDATE QUERY DATA
foreach ($usqls as $udata)  //  FOREACH  //
{
    $field_id_104 = mysqli_real_escape_string($dbc, $udata["field_id_104"]);
    $new103 = $udata["field_id_103"];

    // 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($dbc, $r);
    $old103 = $row["field_id_103"];


    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    $c = new Changes($field_id_104);
    if ($new103 != $old103) $c->set_103($old103, $new103);
    $changelog[] = $c;


    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
    $q = "UPDATE exp_weblog_data SET field_id_103 = $new103 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));
	if (mysqli_affected_rows($dbc)) {  //  IF DATA UPDATED  //
			echo '<fieldset>Data Updated<br /><strong>Product Number:</strong>&nbsp;';
			echo $field_id_104;
			echo "<br /></fieldset>";
	}  //  END IF DATA UPDATED  //
	else {
		echo "No Data was updated:<br />";
		echo $field_id_104;
		echo "<br />";
	}
}  //  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($dbc, $r))
{

    echo PHP_EOL . "Hello<br/>";
    var_dump($row);
}


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



}  //  END OF FOREACH  //
echo "<br /><br /></fieldset>";
?>

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

Open in new window

0
Comment
Question by:rgranlund
  • 2
  • 2
4 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35032331
For starters, it looks like $url is undefined.  Did you get a notice about that when you tried to run this?

The iterator on line 50 -- where is that loop concluded?
0
 
LVL 7

Author Comment

by:rgranlund
ID: 35037408
It is almost there, however, it is still not updating:

<!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>Untitled Document</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 
// 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: 
// do nothing 

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() );
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;
	
//  END OF PARSE AND PREP

if ($field_id_30=='Fender' && $field_id_101=='Electric Guitars' && $field_id_102=='Guitars') {  //  MAIN IF  //
	
// THE DATA FOR SOME UPDATE QUERIES
$usqls   = array();
$usqls[] = array('field_id_104' => '$field_id_104', 'field_id_103' => '$field_id_103'); // CHANGE TWO FIELDS


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

    public function __construct($field_id_104)
    {
        $this->field_id_104 = $field_id_104;
        $this->old103 = NULL;
        $this->new103 = NULL;

    }

    public function set_103($old103, $new103)
    {
        $this->old103 = $old103;
        $this->new103 = $new103;
    }


    public function 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->old103)
        {
            $msg .= "Product Name CHANGED FROM $this->old103 TO $this->new103<br />";
        }
        
        // IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
        if ($msg == $this->field_id_104)
        {
            $msg .= " NO CHANGES";
        }
        return $msg;
    }
}

// OUR CHANGELOG IS AN ARRAY OF OBJECTS
$changelog = array();


// ITERATE OVER THE UPDATE QUERY DATA
foreach ($usqls as $udata)  //  FOREACH  //
{
    $field_id_104 = mysqli_real_escape_string($dbc, $udata["field_id_104"]);
    $new103 = $udata["field_id_103"];

    // 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);
    $old103 = $row["field_id_103"];


    // INSTANTIATE A NEW OBJECT TO LOG THE CHANGES
    $c = new Changes($field_id_104);
    if ($new103 != $old103) $c->set_103($old103, $new103);
    $changelog[] = $c;


    // NOW THE CHANGES ARE LOGGED -- UPDATE THE ROW
    $q = "UPDATE exp_weblog_data SET field_id_103 = '$new103' 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));
}  //  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 />"; 
    var_dump($row);
}


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


}  //  MAIN IF  //
}  //  END OF FOREACH  //
echo "<br /><br /></fieldset>";
?>

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

Open in new window

0
 
LVL 7

Author Comment

by:rgranlund
ID: 35037526
This is what I get back:


array(2) { ["field_id_104"]=> string(6) "150270" ["field_id_103"]=> string(35) "Time Machine™ 1965 Stratocaster®" }


array(2) { ["field_id_104"]=> string(7) "9211000" ["field_id_103"]=> string(41) "Ultimate Relic Masterbuilt Stratocaster®" }
$field_id_104 NO CHANGES
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 35037578
Have you considered taking some classes in programming or basic computer science?  It would really help you out.  Let me give you an example:

case E_NOTICE:
// do nothing

What possible value could come from suppressing notices?  I can think of one or two - you can use undefined variables as if they were NULL, empty or zero.  In most PHP installations this is the "default" setting for error reporting.  One of the stated goals of PHP was to create a language that was so easy anyone could use it.  And indeed, that has come true.  Anyone, even someone with no programming experience, can make a PHP script do something.  But making a PHP script that is easy to understand, secure, scalable -- well that is a very different matter!

Have a look at these two variable names in the code snippet.  Which one is right?  Which one is the variable we want to use and which one is the typographical error?  It's kind of hard to see.  If you have E_NOTICE turned ON, the PHP interpreter will tell you if you accidentally use an undefined variable.  And you WILL do this, believe me.

Here is another issue that jumps out at me in the code sample above.  Look at the curly braces.  What structures do they close?  If the code is indented correctly it is very easy to tell.  Because it is not indented, it is easy for the programmer to become confused.

default:
print "Error of type $type on line $line of $file: $msg <br />";
break;
}
}

Programming is a craft - it takes practice and methodology to be successful.  These things can be taught and learned.

As for the code above, I have no idea why it is not updating, but here is what I usually do when I am not sure about program flow.  I put breakpoints and data visualization into the code.  Use var_dump() liberally.  It will help you see where the logic is going.

Best regards, ~Ray
$transaction_type_l23_O
$transaction_type_123_O

Open in new window

0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Format Data Field - SQL 11 37
check mysql insert 12 28
PHP: Best way to scan folders and process files 10 42
PHP website on Linux - server DNS address could not be found. 18 50
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 …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

820 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