Page 1 of 1

Using MySQL queries in while() loops

Posted: Thu Sep 09, 2010 6:04 pm
by mecha_godzilla
Ok, a real no-brainer for the experts... :)

I'm working on a DB which has all the passwords stored as plain text. What I want to do is retrieve all the passwords, convert them to SHA-256 hashes and then store them in a new field in the same table (I'm keeping both sets of passwords in there until I've updated other scripts).

The problem I'm getting is that my UPDATE query is in a while() loop and it only runs once. Rather than spend another hour trying out solutions that don't work, could anyone tell me what I'm doing wrong? I'm aware that running queries inside while() loops is sub-optimal but the script is only being run once.

Thanks in advance,

Mecha Godzilla

Re: Using MySQL queries in while() loops

Posted: Thu Sep 09, 2010 6:11 pm
by Eran
Without seeing some code, it would be really hard to tell what you are doing wrong

Re: Using MySQL queries in while() loops

Posted: Thu Sep 09, 2010 6:16 pm
by mecha_godzilla
Ok - here it is:

Code: Select all

    $sql = "SELECT user_id, password FROM users ORDER BY user_id";
            
    $result = mysql_query($sql, $conn) or MySQL_query_failure(mysql_error());
    $number_of_users = mysql_num_rows($result);
    
    if ($number_of_users != 0) {
    
        while ($result_array = mysql_fetch_array($result, MYSQL_ASSOC)) {

            $user_id = $result_array['user_id'];
            $password = $result_array['password'];
            
            $password_hashed = hash('sha256',$password,0);

            $sql = "UPDATE users
                    SET password_hashed = '$password_hashed'
                    WHERE user_id = '$user_id'";
                    
            $result = mysql_query($sql, $conn) or die(mysql_error());
            
        }
        
    }

Re: Using MySQL queries in while() loops

Posted: Thu Sep 09, 2010 6:42 pm
by Jonah Bron
Cooky. I can't see anything wrong with it.

Edit: I was about to show you how to do it in one SQL query (without the loop), but then I saw you want SHA-256 :mrgreen:

Re: Using MySQL queries in while() loops

Posted: Thu Sep 09, 2010 6:47 pm
by mikosiko
you are altering your first resultset ($result) inside of your while loop, therefore you while loop is inconsistent.

change your second $result for something else

Re: Using MySQL queries in while() loops

Posted: Thu Sep 09, 2010 7:20 pm
by mecha_godzilla
Ok - thanks for that mikosiko, that's fixed it!

Thanks for all the other responses as well - I'm sure there's a way to do this much more efficiently. Being lazy, I probably should have checked to see what hashing functions MySQL has access to.

Anyway, that's another mini-crisis done and dusted :mrgreen: That brain-dead question was brought to you today by the letters

A

S

S

Goodnight kids!