Populate Playa field from values of another field

Question

I have a site that want to link blog posts to their author pages. Authors have their own entries in a staff channel. Currently they've just used a plain text select field which has had the options manually populated.

What they want to do is use a Playa field instead. So they want to dynamically update the Playa field based on the values of the plain select field.

So at the moment they've got:

{blog_channel}: {author_custom_field} = "Author Name" {staff_channel}: {title} = "Author Name" {url_title} = "author-name"

I need to write some SQL that will look at the value of the author_custom_field, check it against the staff_channel to see if there's any open entries, and if there is, update a new author_playa_custom_field with those values.

That will involve updating the relevant field_id column in exp_channel_data with values like [2256] [author-name] Author Name but also update exp_playa_relationships with parent_entry_id, parent_field_id and child_entry_id.

Has anyone done something similar before?

Answer 1

I came up with what I needed if anyone's interested:

UPDATE exp_channel_data cd

INNER JOIN exp_channel_titles ct
  ON ct.title = cd.field_id_14
  AND ct.channel_id = 8 
  AND ct.status = 'open'

SET cd.field_id_56 = CONCAT('[',ct.entry_id,'] [',ct.url_title,'] ',ct.title)

WHERE cd.channel_id = 2

followed by

INSERT INTO exp_playa_relationships (parent_entry_id, parent_field_id, parent_col_id, parent_row_id, parent_var_id, child_entry_id, rel_order)

SELECT cd.entry_id, 56, null, null, null, ct.entry_id, 0 

FROM exp_channel_data cd

INNER JOIN exp_channel_titles ct
  ON ct.title = cd.field_id_14
  AND ct.channel_id = 8 
  AND ct.status = 'open'

WHERE cd.channel_id = 2