XF 1.1 Way to Remove html from Custom Titles

[MRaburn]

We just imported over from VB and we have custom titles and some of them have html code in them.

I was wondering if anyone had done this and knows of a query to strip them.

The Table is the xf_user and the cell is custom_title.

Would need a sql query for SELECT REPLACE with regex to remove the the html.

<>title<>

Would need the brackets and any code in between stripped and end up with just the 'title'

Thanks for any help!

Mike

 

[Jake Bunce]

I don't know of a generic query to strip HTML. But you can run a simple string replacement to remove specific tags. For example, these two queries will remove bold tags:

UPDATE xf_user
SET custom_title = REPLACE(custom_title, '<b>', '');

UPDATE xf_user
SET custom_title = REPLACE(custom_title, '</b>', '');

 

[Donnie La Curan]

You could write a quick PHP script that looped through each user, pulled their title and ran strip_tags on it, update record and move on. Ill write something when I get to a computer.

 

[Floris]

Why go through each user? It has nothing to do with the user, it's custom_title, you can approach the field directly and take it all out with one regex query for every row matching <*> and </*>

 

[Donnie La Curan]

I haven't looked yet, but if you set a custom title for a user wouldn't it be in the xf_user table? It's for a specific user. It was my first guess at what it might be.

edit: I see what you're saying now, just run the query on that field without needing to grab everything.

That and regex isn't my friend ... though I probably should learn it.

Also, just curious. But the user_title in xf_user_group is varchar(100) while the custom_title in xf_user is varchar(50). Aren't these the same fields, just one is applied to an entire group and one is applied to a user? Just curious about the length.

 

[Jake Bunce]

Also, just curious. But the user_title in xf_user_group is varchar(100) while the custom_title in xf_user is varchar(50). Aren't these the same fields, just one is applied to an entire group and one is applied to a user? Just curious about the length.

Group titles can have HTML. That is probably why one is longer.

 

[Donnie La Curan]

Group titles can have HTML. That is probably why one is longer.

Makes sense.

 

[Donnie La Curan]

Well just for fun here is the script I wrote. It's quick, it's dirty, I ran it on a test install and it worked. There is no error checking or reporting. All the script does is loop through the users and strip the tags from the custom_html field and update that record with the new value. I take no responsibility if it creates a black hole and sucks the universe into it. As always, make a backup of your database first.

I'm sure there is better way of doing this, but my guess is this is probably only going to be run one time after an import to strip the html out of custom titles for users so I didn't see the need for something pretty or fast ... just something to get the job done.

<?php
 
$user = '';
$pass = '';
$host = 'localhost';
$db = '';
 
$link = mysql_connect($host, $user, $pass);
mysql_select_db($db, $link);
 
$query = 'select user_id, custom_title from xf_user';
$result = mysql_query($query, $link);
 
while ($row = mysql_fetch_assoc($result)) {
    $id = $row['user_id'];
    $new_title = strip_tags($row['custom_title']);
    $new_query = "update xf_user set custom_title = '$new_title' where user_id = $id";
    mysql_query($new_query, $link);
}
 
echo 'done';
 
?>

BTW, some might have to be hand edited after you run this script if the HTML tags were broken.

SELECT *
FROM  `xf_user`
WHERE  `custom_title` LIKE  '%<%'

That should get you the users who have some HTML ... you might try running again with > instead of <. See if anything snuck by.

 

[MRaburn]

I ran Donnie's script on our 150k users. It did the trick nicely. Like he said though do a quick search to find the broken tags because you will have to hand edit them.

I used PhpMyadmin to find them and edited them inline. I had 8 that were broken html tags.

Easy peasy.

Thanks guys for your input!