I-AM-OBODO Posted September 9, 2018 Share Posted September 9, 2018 Hi I have a table that is filled with values already and now i want to add another column associate_id How can i update the columns with a random number? What i have is giving all the columns same number thanks $associates_id = mt_rand(10000, 99999); $stmt = $pdo->prepare("UPDATE associates SET associates_id= $associates_id "); $stmt->execute(); $affected_rows = $stmt->rowCount(); if ($affected_rows){ echo "DONE"; }else{ echo "NOT DONE"; } Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2018 Share Posted September 9, 2018 "associates_id" should already be unique,and should remain thus. Changing them to random values does not guarantee uniqueness. And, yes, an update query without a WHERE clause will affect ALL rows. Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 5 minutes ago, Barand said: "associates_id" should already be unique,and should remain thus. Changing them to random values does not guarantee uniqueness. And, yes, an update query without a WHERE clause will affect ALL rows. Changing them to random values does not guarantee uniqueness. How/what then will guarantee uniqueness? "associates_id" should already be unique,and should remain thus was omitted cos of an oversight before and thats why its being populated now And, yes, an update query without a WHERE clause will affect ALL rows. So in this case, how does one add the WHERE clause when you want all the columns to be affected without doing it one by one? Thanks Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 So are you saying that your current table does NOT have a unique key in any other column? No primary (ie, unique) index at all? Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 1 minute ago, ginerjm said: So are you saying that your current table does NOT have a unique key in any other column? No primary (ie, unique) index at all? Nope. There's a primary key which is an id and its different to the associates_id. Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 I thought you wanted to put a random value in the new associates_id??? If you already have an associates_id, then why not use that value? How many rows are we talking here? Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 3 minutes ago, ginerjm said: I thought you wanted to put a random value in the new associates_id??? If you already have an associates_id, then why not use that value? I have an id already but i want to add associate_id and populate the column with unique value without doing it one after the other. The id column is different from the associate_id. The associate_id is the new column i want to add. Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 So you are creating a new associate_id column but you don't want to put your current id value in it? You are confusing me. Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 And once again - how many rows are in this table? Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 Ok - in the interest of progress, here is this: Write a query to dump all of your current primary keys to an array $q = 'select keycol from my_table'; $key_results = $pdo->query($q); $key_values = $key_results->fetchall(PDO::FETCH_NUM); Now write a prepared query something like this: $q = 'update my_table set associate_id = :assoc_id where keycol = :key_value'; $qst = $pdo->prepare($q); // Now write a loop to go thru the PK values and set up an array // with the two needed parameters and execute the query to process them all $parms = array(); foreach ($key_values as $k) { $parms['keycol'] = $k; $parms['assoc_id'] = mt_rand(1000,99999); // NOT A UNIQUE VALUE!!!! $qst->execute($parms); } Note: Code above is un-tested. This will update your table's 'associate_id' column with the values generated by the call(s) to mt_rand. As noted here and by Barand, it will not be unique. If you want something unique you'll have to solve that problem Of course you have said you 'have an id' already. Why are we not using that value instead of this new 'associate_id' column with a new almost-random value? Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 18 minutes ago, ginerjm said: And once again - how many rows are in this table? about 300 rows Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 7 minutes ago, ginerjm said: Ok - in the interest of progress, here is this: //Write a query to dump all of your current primary keys to an array $q = 'select keycol from my_table'; $key_results = $pdo->query($q); $key_values = $key_results->fetchall(PDO::FETCH_NUM); // Now write a prepared query something like this: Note: Code above is un-tested. This will update your table's 'associate_id' column with the values generated by the call(s) to mt_rand. As noted here and by Barand, it will not be unique. If you want something unique you'll have to solve that problem Of course you have said you 'have an id' already. Why are we not using that value instead of this new 'associate_id' column with a new almost-random value? Thanks. But it seems you don't understand what i want or trying to say. But i guess i will just have to start creating the table afresh. The only issue i have now is that if mt_rand doesn't guarantee uniqueness, how can one create/generate a unique id then outside the primary key? Thanks all the same Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 What did I miss now? I have updated your table with a set of random numbers in your new field. As unique as mt_rand will do it Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 3 minutes ago, ginerjm said: What did I miss now? I have updated your table with a set of random numbers in your new field. As unique as mt_rand will do it Thanks Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 If you want to ensure that you have a unique value in the new associate_id column, why not just assign the value of a counter instead of mt_rand? Easy change to my sample code. Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 Ps - I see that my posts are mangled up. One does NOT have all of the code in it which is the one you quoted. Please check the one that has all 3 code blocks in it. My cut and paste doesn't seem to transfer into the forum correctly. Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 9, 2018 Author Share Posted September 9, 2018 8 minutes ago, ginerjm said: If you want to ensure that you have a unique value in the new associate_id column, why not just assign the value of a counter instead of mt_rand? Easy change to my sample code. Thanks. will try that out. how do you mean assigning value of a counter? Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 Change the block of code that looks similar to be like this: $parms = array(); $i = 0; // initialize a counter foreach ($key_values as $k) { $parms['keycol'] = $k; $i++ $parms['assoc_id'] = $i; $qst->execute($parms); } That will place the value of $i in the new field instead of a random number. Of course you can initialize it to a larger value than 0 too. Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2018 Share Posted September 9, 2018 If you want to guarantee uniqueness, first define the column as unique ALTER TABLE associates ADD COLUMN `associates_id` INT NULL, ADD UNIQUE INDEX `associates_id` (`associates_id` ASC); Then UPDATE associates SET associates_id = 10000 + RAND()*900000; if it unluckily fails due to a duplicate, try again Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 Sorry about my last post. The forum really mangles things up as for formatting. Link to comment Share on other sites More sharing options...
ginerjm Posted September 9, 2018 Share Posted September 9, 2018 PS again. I have a typo in my code please change the references to $parms['keycol'] to be $parms['key_value'] Link to comment Share on other sites More sharing options...
Barand Posted September 9, 2018 Share Posted September 9, 2018 Oops! Too many zeros. My previous post should have RAND()*90000 and not RAND()*900000 Link to comment Share on other sites More sharing options...
I-AM-OBODO Posted September 10, 2018 Author Share Posted September 10, 2018 14 hours ago, Barand said: Oops! Too many zeros. My previous post should have RAND()*90000 and not RAND()*900000 Thanks. I have updated the column. But my question now is what will be best to generate a random number to avoid duplicate? or should something; if duplicate entry, regenerate a another code. something of that nature. just asking. Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2018 Share Posted September 10, 2018 Did you read my earlier post? Link to comment Share on other sites More sharing options...
Barand Posted September 10, 2018 Share Posted September 10, 2018 I have just tried this code with a table of over 10,000 records, setting the unique id column to nulls first. $k = 0; do { $db->exec("UPDATE IGNORE test_random SET id = 10000 + RAND()*90000 WHERE id IS NULL"); $res = $db->query("SELECT COUNT(*) FROM test_random WHERE id IS NULL"); $nulls = $res->fetchColumn(); $k++; echo $nulls . ' duplicates<br>'; } while ($nulls); echo "<br><br>That took $k iterations"; The output was 583 duplicates 67 duplicates 4 duplicates 0 duplicates That took 4 iterations EDIT: With only 300 records it took only the one iteration - all unique on first attempt. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.