Need query to strip character from titles

[Gadget_Guy]

Hi,

I am running into an issue where any thread titles that have a "*" in them are causing me to get errors.

I would like a way to go through all thread titles and replace any instance of "*" with either another character or a blank space.

(and by "*", I actually mean the asterisk character)

Ideally, I would like to run a query to first show me them first... then I could delete manually in the forums, if there are not too many.

Or... if there turns out to be a lot, a query to then perform the search and replace automatically.

Thanks,

D.

[Dave]

Find threads with * in the title:

PHP Code:

SELECT threadid, title
FROM `thread` 
WHERE title LIKE '%*%'
ORDER BY lastpost DESC 


Replace * with nothing in the title:

PHP Code:

UPDATE thread 
SET title = REPLACE(title, '*', '') 
WHERE title LIKE '%*%'; 


Create backup before executing the UPDATE query in case it goes wrong.

[nerbert]

Writing a file for this would be quite a job. You can use phpMyAdmin to find all such threads and then delete or edit as necessary.

Go to your thread table and click "Search" at the top of the page then in the page you get after that put in the criterion

LIKE %*%

for the title field.

EDIT: Once you get your results you can double click on the thread title to get a little edit box. Click outside the box to submit.

[Gadget_Guy]

This worked perfectly... and helped me find the REAL cause of the issue.

I see that threads with "*" in them are fine and only a few are what is throwing the error.

It looks like deleted users who I think weren't deleted properly and turned into "Guests" is the real issue.

The "Chris G" posting you see is not in the system any more... it was deleted as part of a cleanup of users who haven't visited the forums in a long time.

See the attached screen shot.

All the threads that throw the error have users like the one here.

Now... how can I properly clean up these users..... or... find threads with users like this and clean those out?

D.

[nerbert]

What does it show in the "postusername" field? "Guest" or the original name? And what is the value for "postuserid"?

--------------- Added [DATE]1417110248[/DATE] at [TIME]1417110248[/TIME] ---------------

You might not want to delete those from the thread table right away. You should also get rid of the posts associated with those threads and I'm not sure how to do that. Maybe someone else knows. If you delete those entries from the thread table you've lost the thread id values.