[RESOLVED] Update query

[philweb]

Hi,

I am having trouble with my update form, I wish simply to be able to change the email address in the database. Could someone please have a look to see where I might be going wrong.

Thanks,

Phil

//change_password.php

// Set the page title and include the HTML header.
$page_title = 'Change Your Password';


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

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

// Create a function for escaping the data.
function escape_data ($data) {
	global $dbc; // Need the connection.
	if (ini_get('magic_quotes_gpc')) {
		$data = stripslashes($data);
	}
	return mysql_real_escape_string($data, $dbc);
} // End of function.

$message = NULL; // Create an empty new variable.

// Check for a username.
if (empty($_POST['first_name'])) {
	$fn = FALSE;
	$message .= '<p>You forgot to enter your username!</p>';
} else {
	$fn = escape_data($_POST['first_name']);
}

// Check for an existing password.
if (empty($_POST['email'])) {
	$e = FALSE;
	$message .= '<p>You forgot to enter your existing password!</p>';
} else {
	$e = escape_data($_POST['email']);
}

// Check for a password and match against the confirmed password.
if (empty($_POST['new_email'])) {
	$ne = FALSE;
	$message .= '<p>You forgot to enter your new password!</p>';
} else {
	if ($_POST['new_email'] == $_POST['new_email1']) {
		$ne = escape_data($_POST['new_email']);
	} else {
		$ne = FALSE;
		$message .= '<p>Your new password did not match the confirmed new password!</p>';
	}
}

if ($fn && $e && $ne) { // If everything's OK.

	$query = "SELECT first_name FROM employees WHERE (first_name='$fn' AND email='$e' )";		
	$result = @mysql_query ($query);
	$num = mysql_num_rows ($result);
	if ($num == 1) {
		$row = mysql_fetch_array($result, MYSQL_NUM);

		// Make the query.
		$query = "UPDATE employees SET email='$ne' WHERE user_id=$row[0]";		
		$result = @mysql_query ($query); // Run the query.
		if (mysql_affected_rows() == 1) { // If it ran OK.

			// Send an email, if desired.
			echo '<p><b>Your password has been changed.</b></p>';

			exit(); // Quit the script.

		} else { // If it did not run OK.
			$message = '<p>Your password could not be changed due to a system error. We apologize for any inconvenience.</p><p>' . mysql_error() . '</p>'; 
		}		
	} else {
		$message = '<p>Your username and password do not match our records.</p>'; 
	}
	mysql_close(); // Close the database connection.

} else {
	$message .= '<p>Please try again.</p>';		
}

} // End of the main Submit conditional.

// Print the error message if there is one.
if (isset($message)) {
	echo '<font color="red">', $message, '</font>';
}

HERE IS THE FORM TO GO WITH IT, THANKS

<form action=" <?php echo $_SERVER['PHP_SELF']; ?> " method="post">
<fieldset><legend>Enter your information in the form below:</legend>

<p><b>First Name:</b> <input name="first_name" type="text" id="first_name" value=" <?php if (isset($_POST['first_name'])) echo $_POST['first_name']; ?> " size="10" maxlength="20" />
</p>

<p><b>Current Email:</b> <input name="email" type="text" id="email" size="20" maxlength="60" />
</p>

<p><b>New Email:</b> <input name="new_email" type="text" id="new_email" size="20" maxlength="60" />
</p>

<p><b>Confirm New Email:</b> <input name="new_email1" type="text" id="new_email1" size="20" maxlength="60" />
</p>
</fieldset>

<div align="center"><input type="submit" name="submit" value="Update My Account" /></div>

</form><!-- End of Form -->

[NogDog]

I'd start by making sure your queries are valid, using a pattern such as:

$query = "UPDATE employees SET email='$ne' WHERE user_id=$row[0]";
$result = @mysql_query ($query) or die("Query failed ($query): ".mysql_error());

[philweb]

I will try a few other things and get back to you.

Cheers!

[philweb]

Hi

I have tried a few other things and I still seem to be getting an error, I have included the error reporting function but this doesn't throw up anything, but the error I am getting is Unknown column 'Phil' in 'where clause'

Phil is the name that is put into the first_name box.

NogDog I tried changing the line to what you put, but that didn't seem to work, could you define your line more clearly so I can get it to work with the script I have please.

thanks,

Phil

[jernhenrik]

<snip>

$query = "SELECT first_name FROM employees WHERE (first_name='$fn' AND email='$e' )";         

        $result = @mysql_query ($query); 
        $num = mysql_num_rows ($result); 
        if ($num == 1) { 
            $row = mysql_fetch_array($result, MYSQL_NUM); 

        // Make the query. 
        $query = "UPDATE employees SET email='$ne' WHERE user_id=$row[0]";         

</snip>

You are selecting first_name and using the result which in your example would be "Phil" to update the same table where user_id is also "Phil"? And that query would look like:

UPDATE employees SET email='mynewpassworddisguisedasemailvariable' WHERE user_id=Phil

If user_id is the same as first_name and user_id is a string the above will fail.
If user_id is an integer the above will fail.

I would imagine that your first query should return user_id (which probably is an integer) that you could the use update your user information.

Example:

$query = "SELECT user_id FROM employees WHERE (first_name='$fn' AND email='$e' )";
	//echo $query;         

	$result = mysql_query ($query) or die('Mysql threw an error: ' . mysql_error()); 
	$num = mysql_num_rows ($result); 
	if ($num == 1) { 
		$row = mysql_fetch_array($result, MYSQL_NUM); 

	// Make the query. 
	$query = "UPDATE employees SET email='$ne' WHERE user_id=$row[0]";      
	//echo $query;   

When facing problems like this, it's an advantage to echo your query. If you can't get the above to work, please post your echoed queries as well.

hth

[philweb]

Hi Jernhenrik,

Hurray! It works, and all I had to do was change the

$query = "SELECT first_name FROM employees WHERE first_name='$fn' AND email='$e' ";

to

$query = "SELECT user_id FROM employees WHERE first_name='$fn' AND email='$e' ";

Thank you again jernhenrik, I am now very happy!

Phil