updating form in sql not working

[wildteen88]

Or the alternative syntax is

UPDATE table_name SET (field1, field2) VALUES ('new value1', 'new value2') WHERE a_field='some value'

 

^^^ Must be from some other SQL. That's not working mysql.

Bugger, I think I got mixed up with the INSERT statement there.

[PFMaBiSmAd]

Sorry to pick, but back-ticks are mysql specific and should be avoided whenever possible, not unconditionally added.

 

The mysql PASSWORD() function should not be used by applications (already mentioned in reply #3 along with two alternate functions to use.) The mysql manual states why not to use it, along with all the applications that stopped working in the past and had to be fixed, so I won't repeat it here again.

 

The PASWORD() or the more correct MD5() or SHA1() function would not be enclosed in single-quotes in the query, because that would make it a piece of string data. Again, amplexus, in your code posted in reply #20 in this thread, you did not have single-quotes around that term in the query, but you later added them. Why did you do that.

 

After you get your code to work, you have a flaw in your password processing. You are retrieving the hashed password and putting it into the form field. When you submit that data and it gets updated, your current code is passing that value through the hash function (currently PASSWORD()) again and it will screw up the saved hash value so that the login won't ever work.

 

If your intent of the password processing is to allow an administrator to set a new password, but to do nothing if the original was unchanged, you would need to add logic so that if the original hashed value gets submitted that you don't alter the saved value but if the submitted value is a new password entered by the administrator that you would want to pass the new value through the hash function and update the saved value.

 

Edit: An alternative way of handling the entering of a new password might be to add a checkbox to each form that would tell the form processing code to use the value from the password field as a new password only when the checkbox is checked.

 

 

[amplexus]

PFMaBiSmAd,

 

With your patient help, and the help of others, I was able to get this to work ( thank you for being sorry enough to mention, but not enough to stop, picking  ).  I am now going to set out on the journey of refining, and also, fixing the little issues you have mentioned.  Honestly, I was using PASSWORD only as a means of testing the hash function, and never intended this to go wild without changing to MD5(). I also knew about the repassing of the hash, and intended to tell the admin that they would need to re enter both values, but I like the check box idea. now you'll probably see another post about fixing check boxes in my code!  ha ha. 

 

I'm posting my "final" code for this thread here, again, thank you so much to all for your patient help!

<?php
session_start();
include("dbinfo.inc.php");
mysql_connect($servname,$dbusername,$dbpassword);
mysql_select_db($database) or die( "Unable to select database");

if(isset($_GET['user_id'], $_GET['username'], $_GET['password']))
{
     $ud_id        = $_GET['user_id'];
     $ud_username = $_GET['username'];
     $ud_password = $_GET['password'];

    $query = "UPDATE `$newdbname` SET `username` = '$ud_username', `password` = PASSWORD('$ud_password') WHERE `user_id`= '$ud_id'";
    $result = mysql_query( $query ) or die( mysql_error() );
    
    if($result)
    {
         if(mysql_affected_rows() == 1)
        {
             echo 'Success! We have updated the username/password for the users id of -' . $ud_id;
             echo '<br /><br /><a href="update.php"> Click here to update more records</a>';
        }
        else
        {
            echo 'Sorry, no rows were affected with the userid of - '. $ud_id;
        }
    }
}
else
{
    echo 'Invalid data provided!<br /><a href="update.php">Chose a record to update</a>';
}

?>