Check if Form Values Changed

doubledee · June 16, 2012

When a user submits the "User Details" Form, if there are NO changes, then I want a message displayed as such.

What is the easiest way to do this?

When I did my Q&A Form, it seemed easier, because I had all 10 Answers in a responseArray, and so i could just do a loop and compare the Form Answer with the Stored Answer.

But since my User Details for has things like: First Name, Gender, Location, Occupation, etc, I don't see how I could use a loop here with the different $_POST names?!

BTW, I was under the impression that the code below would Affected Rows !== 1 if nothing changed in the Form?! :confused:

// ************************
// Update Member Record.	*
// ************************

// Build query.
$q1 = "UPDATE member
		SET first_name=?,
		gender=?,
		birth_year=?,
		location=?,
		occupation=?,
		interests=?,
		about_me=?,
		updated_on=NOW()
		WHERE id=?
		LIMIT 1";

// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);

// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 'ssissssi', $firstName, $gender, $birthYear, $location,
								$occupation, $interests, $aboutMe, $sessMemberID);

// Execute query.
mysqli_stmt_execute($stmt1);

// Verify Update.
if (mysqli_stmt_affected_rows($stmt1)==1){
	// Update Succeeded.
	$detailsChanged = TRUE;

Suggestions?

Thanks,

Debbie

Drummin · June 16, 2012

No Debbie, I don't think you are correct. As far as I know, when making an update, there are no "checks" to see if values are different. You are only going to get a count of the number of records updated. The only why I can think of is passing hidden input fields for each field and then doing a comparison of posted values from hidden input (from DB) and user input.

xyph · June 16, 2012

If affected_rows is 0, no rows were changed. This will happen if you try to update with the same values that are already in the database

Drummin · June 16, 2012

Oh! Okay, good to know. Thanks.

Adam · June 16, 2012

Although you are updating a column:

updated_on=NOW()

doubledee · June 16, 2012

Although you are updating a column:
updated_on=NOW()

Well, a few things...

1.) That seems like a catch-22. If there is an update, I need the current time in the "updated_on" field, but if nothing changes, then you are saying that makes a change?!

2.) I don't see how this code can catch the three conditions I am trying to catch of:

- User Details Successfully Updated

- User Details Were Not Changes

- User Details Update Failed

// ************************
// Update Member Record.	*
// ************************

// Build query.
$q1 = "UPDATE member
		SET first_name=?,
		gender=?,
		birth_year=?,
		location=?,
		occupation=?,
		interests=?,
		about_me=?,
		updated_on=NOW()
		WHERE id=?
		LIMIT 1";

// Prepare statement.
$stmt1 = mysqli_prepare($dbc, $q1);

// Bind variables to query.
mysqli_stmt_bind_param($stmt1, 'ssissssi', $firstName, $gender, $birthYear, $location,
								$occupation, $interests, $aboutMe, $sessMemberID);

// Execute query.
mysqli_stmt_execute($stmt1);

// Verify Update.
if (mysqli_stmt_affected_rows($stmt1)==1){
	// Update Succeeded.
	$detailsChanged = TRUE;

	// Update Session Variable.
	$_SESSION['sessFirstName'] = $firstName;

}else{
	// Update Failed.
	$_SESSION['resultsCode'] = 'DETAILS_NEW_DETAILS_FAILED_2132';

	// Set Error Source.
	$_SESSION['errorPage'] = $_SERVER['SCRIPT_NAME'];

	// Redirect to Display Outcome.
	header("Location: " . BASE_URL . "/account/results.php");

	// End script.
	exit();

}//End of UPDATE MEMBER RECORD

// Display Form Outcome
if (empty($_SESSION['resultsCode'])){
	if ($detailsChanged == TRUE){
		// Changes Succeeded.
		$_SESSION['resultsCode'] = 'DETAILS_NEW_DETAILS_SET_2131';

	}elseif ($detailChanged == FALSE){
		// No Changes.
		$_SESSION['resultsCode'] = 'DETAILS_NO_CHANGES_2272';
	}

	// Set Error Source.
	$_SESSION['errorPage'] = $_SERVER['SCRIPT_NAME'];

	// Redirect to Outcome Page.
	header("Location: " . BASE_URL . "/account/results.php");

	// End script.
	exit();
}//End of DISPLAY FORM OUTCOME

When I did this for my Q&A Form, I put all of the Form Submission into an array, and then created a big loop, and in the loop I ran a SELECT for each Array Entry (i.e. Answer) and checked to see if the value in the Form matched what was in the Database.

It was a hell of a lot of code, and like I think I said earlier, was easier because at least I could use an array with the pattern of responseArray[0], responseArray[1],... versus $_POST['firstName'], $_POST['gender'], $_POST['location'],...

I'm not sure if I have to go to that length, or if I just need a simpler tweak to see what value Affected Rows is showing?! :confused:

Thanks,

Debbie

xyph · June 16, 2012

Run query that updates everything, except updated_on

If query fails, update failed

If affected rows is 0, nothing was changed.

If affected rows is 1, run another query updating updated_on, changes successful

doubledee · June 16, 2012

Run query that updates everything, except updated_on

If query fails, update failed

If affected rows is 0, nothing was changed.

If affected rows is 1, run another query updating updated_on, changes successful

Could I somehow run a SELECT query on my current Member Record, compare that to the Form Values, and if there was a change, THEN run my UPDATE??

Here is what I did this with my Q&A Form, but that was easier since every Answer was a unique record so I was only dealing with one column...

// ****************************
// Process each Form Field.		*
// ****************************
foreach($responseArray as $questionID => $answer){


	// **************************
	// Check for Answer in DB.	*
	// **************************

	// Build query.
	$q1 = "SELECT answer_text
			FROM bio_answer
			WHERE member_id=? AND question_id=?";

	// Prepare statement.
	$stmt1 = mysqli_prepare($dbc, $q1);

	// Bind variable to query.
	mysqli_stmt_bind_param($stmt1, 'ii', $sessMemberID, $questionID);

	// Execute query.
	mysqli_stmt_execute($stmt1);

	// Store results.
	mysqli_stmt_store_result($stmt1);

	// Check # of Records Returned.
	if (mysqli_stmt_num_rows($stmt1)==1){


		// ****************
		// Answer Found.	*
		// ****************

		// Bind result-set to variable.
		mysqli_stmt_bind_result($stmt1, $storedAnswer);

		// Fetch record.
		mysqli_stmt_fetch($stmt1);


		// ********************
		// Compare Answers.		*
		// ********************
		if ($responseArray[$questionID] !== $storedAnswer){
			// Answer Changed.


			// ******************
			// Update Answer.		*
			// ******************

I have to re-think how - and if - this would work where all of my Form Fields are in ONE RECORD... :shrug:

Debbie

xyph · June 16, 2012

Could I somehow run a SELECT query on my current Member Record, compare that to the Form Values, and if there was a change, THEN run my UPDATE??

You could, but it would be more work.

Edit - I suppose if you were processing the form on the same page as displaying it, a SELECT query first might be a good idea.

doubledee · June 16, 2012

Could I somehow run a SELECT query on my current Member Record, compare that to the Form Values, and if there was a change, THEN run my UPDATE??

You could, but it would be more work.

Edit - I suppose if you were processing the form on the same page as displaying it, a SELECT query first might be a good idea.

I think your way is more efficient, but here is my updated stab at what I might do when comparing the Form Values to One Member Record...

	// ******************************
	// Attempt to Change Details.		*
	// ******************************
	if (empty($errors)){
		// Valid form data.


		// ****************************
		// Process each Form Field.		*
		// ****************************
//			foreach($_POST as $key => $value){
//				echo '$key = ' . $key . ' and $value = ' . $value . '<br />';



		// **************************
		// Check for Details in DB.	*
		// **************************

		// Build query.
		$q1 = "SELECT first_name, gender, birth_year, location, occupation, interests, about_me
						FROM member
						WHERE member_id=?";

		// Prepare statement.
		$stmt1 = mysqli_prepare($dbc, $q1);

		// Bind variable to query.
		mysqli_stmt_bind_param($stmt1, 'i', $sessMemberID);

		// Execute query.
		mysqli_stmt_execute($stmt1);

		// Store results.
		mysqli_stmt_store_result($stmt1);

		// Check # of Records Returned.
		if (mysqli_stmt_num_rows($stmt1)==1){


			// ****************
			// Details Found.	*
			// ****************

			// Bind result-set to variable.
			mysqli_stmt_bind_result($stmt1, $s_firstName, $s_gender, $s_birthYear, 
									$s_location, $_occupation, $s_interests, $_aboutMe);

			// Fetch record.
			mysqli_stmt_fetch($stmt1);


			// ********************
			// Compare Answers.		*
			// ********************
			if (($s_firstName == $_POST['firstName'])
					&& ($s_gender == $_POST['gender'])
					&& ($s_birthYear == $_POST['birthYear'])
					&& ($s_location == $_POST['location'])
					&& ($s_occupation == $_POST['occupation'])
					&& ($s_interests == $_POST['interests'])
					&& ($s_aboutMe == $_POST['aboutMe'])){
				// Details did Not Change.
				// Display some message like "NO CHANGES MADE".

			}else{
				// Details Changed
				// Run UPDATE
			}
		}//End of CHECK FOR DETAILS IN DB

What do you think about doing it that way? :shrug:

Thanks,

Debbie

xyph · June 16, 2012

As I said, it works, but it's more code than you need.

doubledee · June 16, 2012

Run query that updates everything, except updated_on

If query fails, update failed

Will this *always* be "-1" ??

Debbie

xyph · June 16, 2012

Will what be -1 ? I've never mentioned negative integers.

Here's the way I'd do it

<?php

$sql = new MySQLi('localhost','root','','db');

$uid = 1;
$messages = array();
$data = array();

// PROCESS
if( $_SERVER['REQUEST_METHOD'] == 'POST' ) {
// sanitize and verify existence
$_POST['uname'] = isset($_POST['uname']) ? $sql->escape_string($_POST['uname']) : '';
$_POST['name'] = isset($_POST['name']) ? $sql->escape_string($_POST['name']) : '';
// build query
$query = 'UPDATE users SET uname="'.$_POST['uname'].'", name="'.$_POST['name'].'"'.
	' WHERE id='.(int)$uid;
if( $sql->query($query) === FALSE ) {
	if( $sql->errno == 1062 )
		$messages[] = 'That username already exists';
	else
		$messages[] = 'Update query error';
} elseif( $sql->affected_rows == 0 )
	$messages[] = 'No changes needed';
else {
	$query = 'UPDATE users SET updated=NOW() WHERE id='.(int)$uid;
	if( $sql->query($query) === FALSE )
		$messages[] = 'Second update query error';
	else
		$messages[] = 'Profile updated';
}

}
// END PROCCESS

// FORM
$query = 'SELECT uname, name, updated FROM users WHERE id='.(int)$uid;
if( ($result = $sql->query($query)) === FALSE )
$messages[] = 'Select query error';
else {
if( $result->num_rows != 1 )
	$messages[] = 'Bad user id given';
else
	$data = $result->fetch_assoc();
$result->free();
}

if( !empty($messages) )
echo '<h3>messages</h3><ul><li>'.
	implode('</li><li>',$messages).
	'</li></ul>';

if( !empty($data) )
echo '<form method="post" action="">'.
	'<p>Username: <input type="text" name="uname" value="'.$data['uname'].'"></p>'.
	'<p>Real Name: <input type="text" name="name" value="'.$data['name'].'"></p>'.
	'<p><input type="submit"></p>'.
	'Last updated: '.$data['updated'];

// END FORM

?>

Using the following table

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) NOT NULL,
  `name` varchar(40) NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uname` (`uname`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `uname`, `name`, `updated`) VALUES
(1, 'xyph', 'matt', '2012-06-16 11:27:03'),
(2, 'foobar', 'test', '2012-06-16 00:00:00');

Hope that helps.

doubledee · June 16, 2012

Will what be -1 ? I've never mentioned negative integers.

I believe the Manual says that Affected Rows returns a -1 if the UPDATE fails...

Debbie

xyph · June 16, 2012

Will what be -1 ? I've never mentioned negative integers.

I believe the Manual says that Affected Rows returns a -1 if the UPDATE fails...

Debbie

It does, but you're better off just checking if the query returned FALSE, IMO. Whatever your preference is, both will work.

Psycho · June 17, 2012

This is not the best practice of using an "updated_on" field

updated_on=NOW()

You can set the field up in the database to automatically set the value to the current timestamp when the record is updated. Then you remove that field from your INSERT/UPDATE queries entirely - as it will be automatically handled by the database.

Then just run your update query for all the other fields. If none of them are different then no update occurs and affected rows will be 0.

Sign In

Check if Form Values Changed

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information