How to update a table column based on it's relation to another table?

I have a table called "profiles". There was a column in this table called "user_id" which I have changed the name to "username" and the type from INT(11) to Varchar(255) . However the contents of this column are still numeric ids.

These ids correspond to another table called "users". That table has fields called "user_id" and "username". For each row on the "profiles" table I need to first check to see what the username field has for the id and do a look up on the users table to get the username that correcponds to that id and then update the profile table's username value for with the proper username.

Profiles table:

username | blah blah...
------------------------
1        | ...

Users table:

user_id | username
------------------------
1       | Joe

I need the value for the username field on the profiles table to be updated to "Joe".

I came up with this php script but it stops working after updating just 4 records for some reason:

    $sql = 'SELECT username FROM profiles';
    $query = mysql_query($sql);
    while($row = mysql_fetch_assoc($query)) {
        $id = $row['username'];
        $sql = 'SELECT username FROM users WHERE user_id = '. $id;
        $query = mysql_query($sql);
        while($row = mysql_fetch_assoc($query)) {
            $sql = "UPDATE profiles SET username = '".$row['username']."' WHERE username = $id";
            $query = mysql_query($sql);
            if(!$query) {
                echo 'error!';
            }
        }
    }

My script isn't all that efficient to begin with, although that's not that big an issue since the table has just 50k records. Anyway what would be a way to do this directly from mysql?

Answers


You are manipulating the $row variable inside the first loop. Obviously it does not work. Try giving the variable in inner loop a different name.

And not only that almost all the variables you are using, outside the first loop, you are using the same names inside the inner loop.

Also coming to db schema I would prefer ids to be foreign keys not usernames because querying using ids would be faster than with columns of type varchar.

One more suggestion is that there is no need to use

while($row = mysql_fetch_assoc($query))

because there would be only one row


As Napster said you're overwriting your $query, and $row variables. That should fix your immediate issue.

Additionally, a query inside of a while loop, inside of a while loop is absolutely terrible. No offense intended! We all have to start somewhere. But I would strongly look into how you can rewrite this with a JOIN just so you have something in your toolbelt for that next time.

Hope that helps!


I think relationship between your tables is wrong, you should have a foreign key in profiles table that links to user_id in the users table, then if you change something like Joe to Michael, its corresponding record in profiles table will be updated.

Profiles table:

user_id  | blah blah...
------------------------
1        | ...

Users table:

user_id | username
------------------------
1       | Joe

You can do this in one query

UPDATE profiles t1 
    INNER JOIN users t2 ON t1.username=t2.user_id 
SET t1.username=t2.username

Why you would want to do this is another question.


Need Your Help

Additional properties to entity Field Type in a form in Symfony2

php forms symfony properties entity

In Symfony2, is there a way to map more fields from an entity to the option tag of a select dropdown generated from a form which is based on an entity?

Detecting spaces and applying to a variable

python string list type-conversion

I have a string 'word words wordword' which is converted into a list using the .split method. I want to write code which will detect each space in the list and put the text beforehand into a variab...