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.

  • 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? – Bhashkar Yadav Jan 27 '13 at 18:36
  • Yes I need to move all the entries to a 'Archive' channel and then delete the members – Micky Kelleher Jan 27 '13 at 21:57
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.

| improve this answer | |
  • 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. – Micky Kelleher Jan 27 '13 at 17:10
  • OK, added more code for you! – Derek Hogue Jan 27 '13 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. – Micky Kelleher Jan 27 '13 at 22:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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