Solved

Query Update Message

Posted on 2011-03-15
1
173 Views
Last Modified: 2012-06-21
I have a script that some of you have been helping me with.  Thank you in advance.  Everything about this code works fine, but the update messages.  If a row is updated it prints the message.  But if the Row that is being updated is empty in the database, it does not report.  I want the message to print even if it entered new info into a row that was previously empty.

<?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);

echo "<h1>Update and Report</h1><br /><br />
*** To asure proper results, <h3>Please do not</h3> press any keys while this function is processing. ***<br /><br />
<fieldset>";

// THE CLASS DEFINITION TO HOLD THE CHANGELOG DATA FIELDS


class Changes
{
	protected $prodNo, $old_prodDisplayName, $new_prodDisplayName; 


//  CONSTRUCT PRODUCT NUMBER  //
   	public function __construct($prodNo)
    {
    	$this->prodNo = $prodNo;
		$this->old_prodDisplayName = NULL;
		$this->new_prodDisplayName= NULL;

		
    }
//  END CONSTRUCT PRODUCT NUMBER  //




//  DISPLAY NAME FUNCTION  //
	public function set_prodDisplayName($old_prodDisplayName, $new_prodDisplayName)
    {
		$this->old_prodDisplayName = $old_prodDisplayName;
		$this->new_prodDisplayName = $new_prodDisplayName;
    }




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


// ADD TO THE MESSAGE FOR ANY CHANGES
		


//  DISPLAY NAME  //
       	if ($this->old_prodDisplayName) {
			$msg .= "<br /> <h4>$this->new_prodName</h4><br /><span class=\"green\">THE DISPLAY NAME HAS CHANGED FROM<br />$this->old_prodDisplayName<br />
				TO:<br />$this->new_prodDisplayName</span><br />";
		}


// IF NO CHANGES NOTHING WAS ADDED TO THE MESSAGE STRING
		if ($msg == $this->prodNo)
		{
			echo "<fieldset>";
            $msg .= "<br/><span class=\"bold\">NO CHANGES MADE<br /> </span> <h4>$this->new_prodName</h4><br /></fieldset>";
        }
       	 	return $msg;
			
	}  //  END GET CHANGE MESSAGE  //

}  //  END CHANGES  //



$changelog = array();						
 						
 
 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() );

// 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
	$new_brand = (string)$model->brand;
	$$new_prodType = (string)$model->prodType;
	$new_prodSubType = (string)$model->prodSubType;
	$new_prodDisplayName = (string)$model->prod_displayName;
	$prodNo = (string)$model->prodNo;

	
//  END OF PARSE AND PREP


	
 if ($new_brand=='F' && $new_prodSubType=='EG' && $new_prodType=='G') { 
 
		
// SELECT THE ROW FROM THE TABLE AND GET THE OLD INFORMATION INTO LOCAL VARIABLES
   		$q = "SELECT * 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));
		while($row = mysqli_fetch_assoc($r)) {
		
		$old_prodDisplayName = $row['field_id_34'];

			
// INSTANTIATE A NEW OBJECT TO LOG THE CHANGES  //

    	$c = new Changes($prodNo);

			if(($new_prodDisplayName!=$old_prodDisplayName) || ($old_prodDisplayName == NULL))$c->set_prodDisplayName($old_prodDisplayName, $new_prodDisplayName);

			$changelog[] = $c;



   		
		//$q = "UPDATE exp_weblog_data SET field_id_34='$new_prodDisplayName' WHERE field_id_104='$prodNo' LIMIT 1";
    	//$r = mysqli_query($dbc, $q) or trigger_error("Query: $q\n<br />MySQL Error: " . mysqli_error($dbc));
	//echo $prodNo . "<br /><br />";	    
			
			}  //  END WHILE  //
	 }  //  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 /><h4>PRODUCT NUMBER: &nbsp; $msg <br />" . PHP_EOL . "<br /></fieldset><br />";
}
echo "<br /><br /></fieldset>";
?>

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
1 Comment
 
LVL 7

Accepted Solution

by:
lexlythius earned 500 total points
ID: 35143452
In a table there are no empty rows. Either you update an existing row, or insert a new one.

If your UPDATE clause finds no existing rows to modify, nothing will change, although the query itself succeeds.

That said, if you can't tell in advance whether the row exists, you can use MySQL's
INSERT ... ON DUPLICATE, or alternatively
REPLACE instead of INSERT

to insert or update depending on whether the row already exists or not.
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
Echo'd values in dropdowns 6 32
SQL to JSON 14 40
Dump data from mysql to xls php 10 24
How to add an Index to a date/time field? 15 14
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

730 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