Thread

I have a weird one, one I know I can solve with even a one time sql statement in PHPmyAdmin but I want to be sure I format the sql query right or I risk totally futzing my data…

I need to clone all the existing entry_date data in my channel entries (all few thousand!) in this one channel into a custom field in that channel. So basically, I just want to copy the data from the entry_date to the custom_date_field I created.

The reason is, this was an existing set of entries (imported from an old WP install, as an aside). I will in the future be forcing a now() date into the custom field into every entry that is published to this channel from here on in, and then allowing mods to “bump” an entry when something is “promoted”. But I need to be able to sort by the custom entry date (I’m using Solspace’s Date Field Filter) for pretty much every place I output these entries for the EE version of my blog.

If that makes sense?

There shouldn’t be any issues here so long as your new custom field is a date field. I would set up a template as below replacing the field_ids with the correct field_ids corresponding to your new field - you will need to turn php on for the template and set to input.

Couple of caveats, backup your database and depending on how many entries you have you may hit a timeout running this script (if so use the set_time_limit function that is commented out below). I would also on the first run, add LIMIT 1 to the SELECT statement to just update 1 entry to check it is all working as you wish…

<?php
//Depending on how many entries and host configuration may need to uncomment this next line
//set_time_limit(0);

$sql = "
  SELECT
  exp_channel_titles.entry_id,
  exp_channel_titles.entry_date
  FROM
  exp_channel_titles
        ";
  
//Add in LIMIT 1 on first run
                
$query = $this->EE->db->query($sql);

if ($query->num_rows() > 0)
{
 foreach($query->result_array() as $row)
    {
  $entry_id = $row['entry_id'];
  $entry_date = $row['entry_date'];
  
  //Need to replace field_id number with the correct field id
  //UTC means the date field is 'fixed' if want localised just remove UTC and leave blank
  $data = array('field_id_1' => $entry_date, 'field_ft_1' => 'none', 'field_dt_1' => 'UTC');
  $update_sql = $this->EE->db->update_string('exp_channel_data', $data, "entry_id = '$entry_id'");
  
  //Perform the update
  $this->EE->db->query($sql);
 }
}

//Just output something to say we've finished
echo "That's all folks…";
?>

This is super mega awesome…thanks. I know I would have pooched that if I did it myself.

I’ve already changed the php timeouts because *importing* this database from WP was a right pain, which had to be done in 1.7x with the Movable Type import function, then I had to upgrade to 2.x, and that was where I was hitting big snags with timeouts (in particular, with the comments tables), so I should be able to run this script all right I think.

Thanks again!!