Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3

    Question Mass Update new column in existing table with unique values?


    Hi all,

    I've got an existing table with 1647 rows. I've added a new column that needs to have unique values in it. The values will be created from Upper and lower case letters, as well as numbers. The script I have to do this one time update is this:
    PHP Code:
    for ($i=0;$i<1672;$i++) {
        
    //generate a 12 digit random alpha-numeric for session id
        
    $length=12;
        
    $pool="";
        
    // set pool of possible char
        
    if($pool == ""){
            
    $pool .= "abcdefghijklmnopqrstuvwxyz";
         
    $pool .= "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
         
    $pool .= "0123456789";
        }
    // end if
        
    mt_srand ((double) microtime() * 1000000);
        
    $id "";
        for (
    $index 0$index $length$index++) {
         
    $id .= substr($pool, (mt_rand()%(strlen($pool))), 1);
        }
    //** END nested for loop for $index **//

    $sql mysql_query ("Update Users Set Unique_ID = '$id'") or exit (mysql_error());
    //** END for loop **// 
    When I run this, each user gets updated with the same unique ID generated each time through the loop. In the end, all users wind up with the very last $id generated.

    How can I make this Update Unique so that each time through, the next user in the table gets the next unique $id?

    There is a field in the table that is unique already. It's called "Email."

    Thanks.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,466
    Rep Power
    594
    PHP Code:
    $sql mysql_query ("Update Users Set Unique_ID = '$id' WHERE Uniqiue_ID=''") or exit (mysql_error()); 
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Not quite, GW.

    The solution I gave him in his cross-post should be correct.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Thanks for the reply.

    However, when this condition is added
    Code:
    [WHERE Uniqiue_ID='']
    ...all of the rows are updated with the very first $id generated (opposite of before).

    Any other ideas?

    Is there some sort of SQL statement that would do the update on an existing unique field?

    Something like:

    $sql = mysql_query ("Update Users Set Unique_ID = '$id' WHERE UNIQUE(Email)") or exit (mysql_error());

    ?
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    ...

    really?
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Originally Posted by ManiacDan
    Not quite, GW.

    The solution I gave him in his cross-post should be correct.
    Thanks for the reply.

    When I try this:
    PHP Code:
    $sql mysql_query ("Update Users Set Unique_ID = '$id' WHERE Unique_ID IS NULL LIMIT 1") or exit (mysql_error()); 
    Nothing is updated. I get no mysql error either. I know the script is generating unique $id because I'm echoing them out in the loop prior to the mysql Update.

    I've sorted the rows in both directions (in phpMyAdmin) to confirm. No rows were updated.

    I also tried to use the same query, but removed the "LIMIT 1" and got the same results. No rows updated.

  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Originally Posted by ManiacDan
    ...

    really?
    yeah. apparently because it's a mass update on the first time through the loop and at that point all the 1600+ records have a null value in that new column, so it looks like they all get updated with the first $id.
  14. #8
  15. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Go read the thread again.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  16. #9
  17. Mad Scientist
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2007
    Location
    North Yorkshire, UK
    Posts
    3,661
    Rep Power
    4123
    I would not try to use my own unique ID, instead I would use MySQL's built in UUID() function and do this:

    Code:
    UPDATE `Users` SET `Unique_ID` = UUID();
    or this, if some records already have values,:

    Code:
    UPDATE `Users` SET `Unique_ID` = UUID() WHERE `Unique_ID` = '';
    I would execute at the MySQL command line (or direct interface to it, eg MySQL workbench, phpMyAdmin) as in this case PHP is not required
    I said I didn't like ORM!!! <?php $this->model->update($this->request->resources[0])->set($this->request->getData())->getData('count'); ?>

    PDO vs mysql_* functions: Find a Migration Guide Here

    [ Xeneco - T'interweb Development ] - [ Are you a Help Vampire? ] - [ Read The manual! ] - [ W3 methods - GET, POST, etc ] - [ Web Design Hell ]
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    thinking outloud here...maybe something like this:

    Code:
    //PSEUDO
    
    //Prior to for loop...
    $results = SELECT * from Users...
     do {
        $email[] = $results["Email"]; //Put results in array
     } while ($results)
    
    //Then, in the for loop...
    Update Users set Unique_ID = '$id' WHERE Email = [$i]
    Looks a bit crazy I admit. Can an UPDATE statement have an array like that?
  20. #11
  21. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    Ok seriously, I thought it would be funny but now it's sad.

    I gave you the answer on phpfreaks.

    I linked to that answer here in this thread.

    I asked you to go read this thread again when it was clear you missed it the first time.

    You have missed all three of these messages. Go read the thread again, find the link to the answer.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Originally Posted by Northie
    I would not try to use my own unique ID, instead I would use MySQL's built in UUID() function and do this:

    Code:
    UPDATE `Users` SET `Unique_ID` = UUID();
    or this, if some records already have values,:

    Code:
    UPDATE `Users` SET `Unique_ID` = UUID() WHERE `Unique_ID` = '';
    I would execute at the MySQL command line (or direct interface to it, eg MySQL workbench, phpMyAdmin) as in this case PHP is not required
    hmmm. interesting option. however, once I'm done populating this new column on this one table, going forward, I'll need to be able to insert this unique ID not only into this table, but another table as well when a new user is added. this Unique ID field will be the linkage between the two tables.

    reading the doco on UUID, it looks like each one is generated unique, ruling out the ability to have the unique ID in two different tables. unless I did the UUID on one table first, and then did a SELECT on it to get the UUID from that first table and then did an INSERT (or UPDATE) on the second table with the UUID.

    Seems more complex, and requiring more storage (128-bit or 16 bytes, VS 12 bytes) .
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Originally Posted by ManiacDan
    Ok seriously, I thought it would be funny but now it's sad.

    I gave you the answer on phpfreaks.

    I linked to that answer here in this thread.

    I asked you to go read this thread again when it was clear you missed it the first time.

    You have missed all three of these messages. Go read the thread again, find the link to the answer.


    I did read your reply on PHPFreaks.

    In my post #6 above, you'll see I mentioned I tried your suggestion:

    Code:
    mysql_query ("Update Users Set Unique_ID = '$id' WHERE Unique_ID IS NULL LIMIT 1")
    The results I got, where that no rows were updated.
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Posts
    150
    Rep Power
    3
    Drummin over at PHPFreaks provided an answer that worked for this one time purpose. I'd link to it, but I can't because I'm new here. I'll post their response here:

    Code:
    <?php
    	 $sql = "SELECT id FROM users";
    	 $result=mysql_query($sql) or die (mysql_error());
    	 while($row=mysql_fetch_array($result)){
    		 $user_id=$row['id'];
    		  
    		 //generate a 12 digit random alpha-numeric for session id
    		 $id="";	
    		 $length=12; 
    		 $pool=""; 
    		 // set pool of possible char 
    		 $pool .= "abcdefghijklmnopqrstuvwxyz"; 
    		 $pool .= "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; 
    		 $pool .= "0123456789";     
    		  
    		 for ($index = 0; $index < $length; $index++) { 
    			 $id .= substr($pool, (mt_rand()%(strlen($pool))), 1); 
    		 }//** END nested for loop for $index **// 
    	
    	 $sql = mysql_query ("Update Users Set Unique_ID = '$id' WHERE id = $user_id") or exit (mysql_error());
    
    }
    ?>
    Turned out to be similar to how my thinking (outloud) evolved here. Only, in the SQL statements, I selected out the "Email" field and used that later on for the Update condition clause.

    Thanks to all who tried to help on this.
  28. #15
  29. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,908
    Rep Power
    6351
    You must have not defined your unique_id column as null, so IS NULL would probably have been =''.

    Either way, if you got a working one, you got it working.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo