Page 1 of 1

mysql_real_escape_string erases existing data?!

Posted: Sun Aug 17, 2008 3:26 pm
by JAB Creations
I haven't had the smoothest luck with mysql_real_escape_string and right now it's deleting existing data without updating it! What am I doing wrong here...

Code: Select all

$user = $_SESSION['member'];
$password = mysql_real_escape_string($_POST['password']);
$passwordnew = mysql_real_escape_string($_POST['passwordnew']);
$passwordconfirm = mysql_real_escape_string($_POST['passwordconfirm']);
$hometown = mysql_real_escape_string($_POST['hometown']);
$location = mysql_real_escape_string($_POST['location']);
$website = mysql_real_escape_string($_POST['website']);
 
 
 if ($_POST['passwordnew'] != '') {$result = mysql_query("UPDATE public_accounts SET password='$passwordnew', hometown='$hometown', location='$location', website='$website' WHERE username='$user'"); if (!$result) {$_SESSION['error'] = mysql_error(); header("location:profile.php?error");} else {header("location:profile.php?success=$user");}}
 else {$result = mysql_query("UPDATE public_accounts SET hometown='$hometown', location='$location', website='$website' WHERE username='$user'"); if (!$result) {$_SESSION['error'] = mysql_error(); header("location:profile.php?error");} else {header("location:profile.php?success=$user");}}

Re: mysql_real_escape_string erases existing data?!

Posted: Sun Aug 17, 2008 3:50 pm
by califdon
I'm sure it has nothing to do with your use of mysql_real_escape_string(). You may need to add some code after you populate your Post variables, to check if some values are missing, then only replace those that have updated values. For example, if a record previously had a hometown entered, then your code receives variables from a Form and the user doesn't enter anything for hometown, your code is going to replace the previous hometown data with a NULL. That's the way it's supposed to work, otherwise you could never delete data in a field.

What I suspect you would want to do is first examine all the $_POST array data, building your SQL statement as you loop through the array, including only those fields that are not blank. It is easier to build a SQL statement in such cases using the SET `field` = 'value' syntax, rather than the fields and VALUES lists. And of course, you would build the SQL statement as a string and then use the string as the argument to mysql_query(), rather than including the literal SQL in the function call.

(By the way, it would really help (us and YOU) if you would format your PHP code in blocks, instead of running all those IFs and ELSEs together.)