Jump to content

Populate column


SkyRanger

Recommended Posts

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' )
                 );
Link to comment
Share on other sites

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)
          ");

 

Link to comment
Share on other sites

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

image.png.0fbac360d71407f0a619af3fc6cb1cd2.png

Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.