Populate column

[SkyRanger]

I am trying to figure out how to populate a column that was added after the fact with this:

function randkey($j = 10){
    $string = "";
    for($i=0; $i < $j; $i++){
        $x = mt_rand(0, 2);
        switch($x){
            case 0: $string.= chr(mt_rand(97,122));break;
            case 1: $string.= chr(mt_rand(65,90));break;
            case 2: $string.= chr(mt_rand(48,57));break;
        }
    }
    return $string;
}

This is what I currently have and you can guess how this turns out (same key in all rows):

$randomkey =  randkey();
$status = '1';


        $wpdb->update(
            $sqltable,
            array(
                'randkey' => $kudoomkey,
                 ),
           array('status' => $status),
           array(
                '(%s)'
                 ),
            array( '%d' )
                 );

[Barand]

If you do an update without a WHERE clause ...

UPDATE atable SET  acol = 'X'

then every row gets the same update.

Here's how I'd do it, selecting the ids of the records and allocating a key to each in an array. Then updating the records with their new values from the array

$data = [];
$res = $db->query("SELECT customer_id FROM customer ORDER BY customer_id");
foreach ($res as $r) {
    $data[] = sprintf("(%d, '%s')", $r['customer_id'], randkey()) ;
}

/*
WE NOW HAVE AN ARRAY OF RECORDS WITH EACH ID AND ITS ASSOCIATED RANDKEY
WHICH WE CAN USE TO UPDATE THE TABLE.
E.G.
        Array
        (
            [0] => (1, '8abNT88U4c')
            [1] => (2, 'JZ0lIh958E')
            [2] => (3, '1s62XVkZQX')
            [3] => (4, '1j0SBRCOey')
            [4] => (5, 'DIvq8eBkL7')
            [5] => (6, 'IJWG8Vdevz')
            [6] => (7, 'Vv7OrJPw9t')
            [7] => (8, 'AwG8l87PcQ')
            [8] => (9, 'BG0cQLuybK')
            [9] => (10, 'Mv27T3oajx')
        )
*/

// UPDATE ALL RECORDS WITH SINGLE QUERY

$db->exec("INSERT INTO customer (customer_id, my_new_col)
           VALUES " . join(',', $data) .
           " ON DUPLICATE KEY 
             UPDATE my_new_col = VALUES(my_new_col)
          ");

 

[SkyRanger]

Thank you Barand

[Barand]

Here's a shorter and faster version of the randkey() function

function randkey2($j=10)
{
    $bag = array_merge(range('a','z'), range('A', 'Z'), range(1,9));  // put chars in a bag
    shuffle($bag);                                                    // shake the bag
    return join('', array_slice($bag,0,$j));                          // pull out $j chars
}

Profiler results calling both functions (yours and mine) for each of 158 records showing total time spent in each function

[SkyRanger]

wow. thank you.

[benanamen]

Not sure what you are doing with this, but you can easily generate a CSPRNG (Cryptographically secure pseudorandom number generator) in Php7 with minimal code.

 

<?php
$bytes = random_bytes(5);
var_dump(bin2hex($bytes));

 

[SkyRanger]

Hi benanamen  what I am doing is when an entry is submitted a random key is entered also for each entry

[Barand]

On 9/9/2019 at 6:03 PM, benanamen said:

<?php
$bytes = random_bytes(5);
var_dump(bin2hex($bytes));

 

If you are happy with only the characters [0-9][a-f]

The original uses the character set [0-9][a-z][A-Z]

[SkyRanger]

Thanks Barand, I actually got it to work perfectly from the Sept 9th post you helped me with but been away for a bit and first time back to comment on benanamen post and try to solve another problem I posted in a different through with pulling random posts that I know I have done dozens of times but having a brain meltdown...lol