Removing & regenerating url titles?

Question

How could I go about removing and regenerating all URL titles for a specific channel? I did so once in the past, but unfortunately I have misplaced the query that was provided to me.

Answer 1

As variant you can just use MX Title Control and regenerate you url_ titles by any pattern;

by default this ext is repeated default EE logic and convert url_title into a low case.

if you want to change this logic, /mx_title_control/ext.mx_title_control.php, line 125:

replace

$url_title_name_out = $this->convert_accented_characters(strtolower($url_title_name_out));

with

$url_title_name_out = $this->convert_accented_characters($url_title_name_out);

p.s. maybe I will add a setting for this in next version ;)

Answer 2

Here's another wing at it, one that will probably be more successful. Note that if you have thousands and thousands of rows in your exp_channel_titles table you might have to up your PHP's mem_limit.

Again, back it up before proceeding.

First, create yourself a temporary template somewhere. Make sure only SuperAdmin can view it (it's in the Access settings for the individual template). Next, turn PHP on output for that template. Here we go!

<?php

// what channel id are we working?
$channel_id = '666';

// got multisite? if so add this
$site_id = '2';

// set our separator, either _ or -
$separator = '-';

// get all the titles and url_titles
$titles_array = ee()->db->select('title, url_title, entry_id')
                        ->from('channel_titles')
                        ->where('channel_id', $channel_id)
                        ->where('site_id', $site_id)
                        ->get()->result_array();

// now lets loop over our entries and actually do the replace
foreach($titles_array as $key => &$value) 
{
    // start with a shave and a haircut!
    $new_value = trim($value[$key]['title']);

    // replace spaces with our separator
    $new_value = preg_replace('/[\ ]/', $separator, $new_value);

    // now drop all non a-z characters, case insensitive, and not the _ or -
    $new_value = preg_replace('/[^a-z\-\_]/i', '', $new_value);

    // now lower case that puppy
    $new_value = strtolower($new_value);

    // stick it in the url_title spot
    $titles_array[$key]['url_title'] = $new_value;
}

// now update those rows!
ee()->db->update_batch('channel_titles', $titles_array, 'entry_id'); 
?>

Killer! Now this is untested, so again, backup the channel_titles table before doing anything. If there are any PHP errors coming from this, comment or edit your question and I'll correct them.

Edit

To be clear, you should do everything I said, and then visit (or curl) this template ONCE, then verify it did what you wanted. If not, immediately restore from the backup I've screamed you should have. If you're unsure what URI to hit, in general with EE, you can hit things like this

http(s)://www.your-domain.com/your_template_group/your_test_template

Answer 3

See my other answer for the better solution

Alright, finally, some fun!

Ha, not really. This is your answer in the terms of a MySQL call. However, I will warn you that I do not know the the exact list of what punctuation EE strips from the title when it generates the url_title. I'll look it up and try and update it shortly, but I wanted to get this answer in.

First

Back up your channel_titles table before continuing, and be prepared (know how) to restore it if things go awry. I can't emphasize this enough.

Here's a MySQL call that will get you started.

USE your_ee_schema;
UPDATE exp_channel_titles SET url_title = 
LOWER(
    REPLACE(
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(
                        REPLACE(TRIM(title), " ", "_"),
                    "!", ""),
                "?", ""),
            "~", ""),
        "'", ""),
    "\"" ,"")
)
WHERE channel_id = 'your channel id' AND
WHERE site_id = 'your site id';

Ok, so this chains MySQL REPLACE functions to cut out improper url_title characters. In this case, it starts with a TRIM to remove extra spaces at the end and beginning of the title. Then it first replaces all spaces with underscores (you may need to change this to replace it with dashes, depends on your EE setup). Then it chains out and removes these characters:

!?~'"

The most outer function is LOWER, which then sets the value to be all lowercase.

Like I said, back up your data, and this query definitely doesn't cover all of the characters EE removes when it generates url_title. You'll have to keep chaining REPLACE functions together to get all of 'em; I'll dig in the core and try and get you a list, but you'll have to modify the query to add all the extra chained REPLACE functions :p I can't do all the work for you!

So to be clear, this query took this title

Aren't Things healing Nicely?! 

and converted it and then inserted it to the url_title as this

arent_things_healing_nicely