SQL Query to write member username to custom channel field

Question

Can anyone help with the syntax to write ALL members 'Usernames' in a particular member Group to a custom_field in a channel.

Each member would have one entry each in this channel and I need to pull their username into the channel before deleting the member.

Answer 1

Here's some simplified code (you asked for "syntax" and not actual code, so i assume you're familiar enough to write the actual code yourself?). You'd have to roll this into a PHP-enabled template and replace some variables with their actual names (or array members).

To get all the usernames:

SELECT member_id, username FROM exp_members WHERE group_id = X

Then, loop through those results with a foreach statement, and grab the entry_id:

SELECT entry_id FROM exp_channel_titles WHERE member_id = $member_id AND channel_id = X LIMIT 1

Then update like so:

$this->EE->db->query(
    $this->EE->db->update_string(
        'exp_channel_data',
        array('field_id_X' => $username),
        'entry_id = $entry_id'
    )
);

UPDATE:

OK, here's the code. First, backup your database, then create a new template, enable PHP on it, then put this in it (replace the appropriate variables values at the top of the script) and load it in your browser.

<?php

$this->EE =& get_instance();

$member_group_id = XX;
$channel_id = XX;
$field_id = XX;

$members = $this->EE->db->query("
    SELECT member_id, username 
    FROM exp_members 
    WHERE group_id = $member_group_id
");
if($members->num_rows() > 0)
{
    foreach($members->result_array() as $member)
    {
        extract($member);
        $entries = $this->EE->db->query("
            SELECT entry_id 
            FROM exp_channel_titles 
            WHERE member_id = $member_id 
            AND channel_id = $channel_id
            LIMIT 1
        ");
        if($entries->num_rows() > 0)
        {
            $entry_id = $entries->row('entry_id');
            $this->EE->db->query(
                $this->EE->db->update_string(
                    "exp_channel_data",
                    array("field_id_$field_id" => $username),
                    "entry_id = $entry_id"
                )
            );
        }
    }
}

?>

I strongly suggest you read up on doing database queries in EE -- it's pretty straight forward and can make migration work like this a breeze.