Using MySQL queries in while() loops

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Using MySQL queries in while() loops

Post 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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Using MySQL queries in while() loops

Post by Eran »

Without seeing some code, it would be really hard to tell what you are doing wrong
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Using MySQL queries in while() loops

Post 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());
            
        }
        
    }
User avatar
Jonah Bron
DevNet Master
Posts: 2764
Joined: Thu Mar 15, 2007 6:28 pm
Location: Redding, California

Re: Using MySQL queries in while() loops

Post 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:
mikosiko
Forum Regular
Posts: 757
Joined: Wed Jan 13, 2010 7:22 pm

Re: Using MySQL queries in while() loops

Post 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
User avatar
mecha_godzilla
Forum Contributor
Posts: 375
Joined: Wed Apr 14, 2010 4:45 pm
Location: UK

Re: Using MySQL queries in while() loops

Post 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!
Post Reply