0

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.

2
  • Well while looking into your question you mentioned that: "Each member would have one entry each in this channel and I need to pull their username into the channel before deleting the member". Does it mean that you would like to put channel entries author's username into a custom field. as later you are going to delete all these member (of specific field group). Is this correct? Jan 27, 2013 at 18:36
  • Yes I need to move all the entries to a 'Archive' channel and then delete the members Jan 27, 2013 at 21:57

1 Answer 1

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.

3
  • Definitely not familiar! So much so I said 'syntax' when I meant 'code' ;) You suggest using your code in a php template where I was going to use the database query form and input a sql query (of some sort). Thanks for your help Derek. Jan 27, 2013 at 17:10
  • OK, added more code for you! Jan 27, 2013 at 21:50
  • Derek. Thanks so much. I'll give that a try and will definitely be backing up database first. Thanks for info also. Jan 27, 2013 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.