Problem with form element sql insertion

[mykmallett]

I have a edit-profile application that validates a form and then updates the records in the sql table.

 

Like most standard apps like this it fetches the users details from the database and populates the form, then the user can change anything and submit. Now when the information is entered into the form it goes through the app ok. If you then go back onto this page and do not touch any of the fields it doesnt work. Basically it won't let you submit anything thats already in the table row.

It's not getting through to the mysql part so its not an sql error, its a problem with the variables not being set:

 

if (isset($_POST['submitted'])) { // Handle the form.

require_once ('../mysql_connect.php'); // Connect to the database.


// Check for a first name.
if (preg_match('/^[[:alpha:]\.\' \-]{2,15}$/i', stripslashes(trim($_POST['first_name'])))) {
	$fn = escape_data($_POST['first_name']);
} else {

	$fn = FALSE;
	echo '<p><font color="red" size="+1">Please enter your first name!</font></p>';
}


// Check for a last name.
if (preg_match('/^[[:alpha:]\.\' \-]{2,30}$/i', stripslashes(trim($_POST['last_name'])))) {
	$ln = escape_data($_POST['last_name']);
} else {
	$ln = FALSE;
	echo '<p><font color="red" size="+1">Please enter your last name!</font></p>';
}

// $t = escape_data($_POST['profiletype']);

// Check for an email address.
if (preg_match('/^[[:alnum:]][a-z0-9_\.\-]*@[a-z0-9\.\-]+\.[a-z]{2,4}$/i', stripslashes(trim($_POST['email'])))) {
	$e = escape_data($_POST['email']);
} else {
	$e = FALSE;
	echo '<p><font color="red" size="+1">Please enter a valid email address!</font></p>';
}

// Check for a password and match against the confirmed password.
if (preg_match('/^[[:alnum:]]{4,20}$/i', stripslashes(trim($_POST['password1'])))) {
	$p = escape_data($_POST['password1']);
} else {
	$p = FALSE;
	echo '<p><font color="red" size="+1">Please enter a valid password!</font></p>';
}


$ma = escape_data(htmlspecialchars($_POST['about']));

if (preg_match('/^(http|https|ftp):\/\/([A-Z0-9][A-Z0-9_-]*(?:\.[A-Z0-9][A-Z0-9_-]*)+):?(\d+)?\/?/i', ($_POST['website']))) {
	$website = escape_data($_POST['website']);
	$mw = "'$website'";
} else if (empty($_POST['website'])) {
	$mw = 'NULL';
} else {
	$mw = FALSE;
	echo '<p><font color="red" size="+1">Please enter a valid website URL, or clear the entry</font></p>';
}




if ($fn && $ln && $e && $p && $ma && $mw) { // If everything's OK.

	// Query the database.
	$query = "SELECT username FROM users WHERE (username='$member' AND pass=SHA('$p'))";		
	$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

	if (@mysql_num_rows($result) == 1) { // A match was made.


		// Update records
		$query = "UPDATE users SET email='$e', first_name='$fn', last_name='$ln', member_about='$ma', member_website=$mw WHERE username='$member'";		
		$result = mysql_query ($query) or trigger_error("Query: $query\n<br />MySQL Error: " . mysql_error());

		if (mysql_affected_rows() == 1) { // If it ran OK.

			// Finish the page.
			echo '<h3>Thank you for registering! A confirmation email has been sent to your address. Please click on the link in that email in order to activate your account.</h3>';
			//include ('./includes/footer.html'); // Include the HTML footer.
			exit();	

		} else { // If it did not run OK.
			echo '<p><font color="red" size="+1">You could not be registered due to a system error. We apologize for any inconvenience.</font></p>'; 
		}		

	} else { // The password not matched.
		echo '<p><font color="red" size="+1">Your password is incorrect!</font></p>'; 
	}

} else { // If one of the data tests failed.
	echo '<p><font color="red" size="+1">Try Again ' . $member . $mw . '</font></p>';		
}



}

 

 

The error coming up is the 'could not be registered' line, suggesting that the variables aren't setting correctly for some reason.

 

Any help would be appreciated, thankyou

 

 

[schilly]

echo out your query to make sure it looks ok and echo mysql_affected_rows() to see actually how many rows were effected.

[mykmallett]

how do I echo mysql_num_rows? I can't seem to find a way that doesnt result in 'expects parameter 1 to be resource'?

[mykmallett]

I've echo'd the $query on a successful insertion (when I change a field) and on an unsuccessful one, where no fields have been changed and the query's are exactly the same. The variables are definitely set...

 

 

EDIT:

 

Sorry ive just realised why this is.

 

Its because it is actually inserting the data, but because the data is exactly the same the num_effect_rows != 1 and so is triggering the error.

 

I could take this out completely I guess and it wouldn't bring up a false error message. But is there a way of finding out if this was successful without num_affect_rows

 

 

[schilly]

instead of using mysql_affected_rows() just put the result of mysql_query in the if statement. From the manual for mysql_query:

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.

 

[mykmallett]

Great thankyou very much