query to find, then update characters in post titles & body

[gabeanderson]

I changed the encoding of my forums from ISO-8859-1 to UTF-8. As a result, I'm finding a number of undesired characters in posts, such as "�" in place of a single quote (likely because it was initially input / copied as a right single quote).

Also, the HTML code for right single quote is displaying verbatim in some places rather than as a quote.

The attached screenshot shows an example of both instances (source post (http://www.articulate.com/forums/articulate-presenter/11205-ap-set-screen-show-4-3-what.html#post50303)).

I'd like to run a query first to find all instances where these two issues occur (in post titles and post text). Then update all.

I've done some test queries, but am not having luck getting any results back.

Suggestions on the queries?

Thanks.

[vbplusme]

Are you using phpMyAdmin to run your queries?

[gabeanderson]

I'm using the "Execute SQL Query" feature via Admin CP.

[gabeanderson]

Anyone? Anyone?

I'm just looking for something like this, but want to confirm the syntax:

select * from posts
where title like 'funky-character';

And this:

select * from posts
where body-text like 'funky-character';

Thanks!

[gabeanderson]

It doesn't seem like this should be too difficult... I'm sure I'm just missing something obvious.

Any pointers, please?

Thanks!

[Lynne]

You might want to use % in the like area (% is a wilcard). Also, the table is called "post" and the body-text is stored in a field called "pagetext". So, something more like:
select * from post
where pagetext like '%funky-character%'That will find the posts with the funky_character in them. (I think - I am no mysql guru!) Also, if you have a table prefix, you will have to put that in front of "post".

[gabeanderson]

Thanks, Lynne! That did it for basic searches. But I still can't seem to get my funky characters (like �) to show up in query results by pasting it in.

That's how some characters show up in query results, but I guess they're encoded in the database some other way...

Example query results for this post (http://www.articulate.com/forums/articulate-online/7123-paypal.html#post50736) is attached.

I know that character was originally "»" (and have since removed that from the page title of links like it), but trying a query like the below yields no results:

select * from post
where pagetext like '%raquo%'

Any other ideas?

Thanks!

[Lynne]

If you know it's in that exact post, have you tried looking at the pagetext for that postid and seeing exactly what is written in there?

Also, are you using page caching? If so, you'll want to turn that off (set cached posts lifespan to 0) for now.

[gabeanderson]

Hi Lynne- The above screenshot does show exactly what's in the pagetext for the post - for this example... but the point is that this character may exist in potentially hundreds of other posts since I changed the encoding to UTF-8, not just in this one.

So the problem is how I identify those posts since running the query to find the "�" character is not working.

[Lynne]

So does "�" show up in posttext or does "»" show up in the posttext (I'm talking about exactly what is in the field in the database)? Like if you did a select posttext from post where postid=50736, what do you get in there?

[gabeanderson]

According to the results I see when I use "Execute SQL Query" via vBulletin admin, I see "�," as shown in this screenshot (https://vborg.vbsupport.ru/attachment.php?attachmentid=98202&d=1240428602) (also attached above).

I assume that's an accurate representation of what's in the database.

I see the exact same character when I use this query, too:

select pagetext from post where postid=50736

[Lynne]

And can you do a search on that?

select * from post
where pagetext like '%�%'

[gabeanderson]

No, that's the problem. :)

[Lynne]

I'm afraid I don't know what to suggest then. Databases are just not my thing. Have you tried searching/posting over on vb.com?

[gabeanderson]

Thanks. Just posted there (http://www.vbulletin.com/forum/showthread.php?p=1725497#post1725497), too.

[briansol]

This is related to your browser (render) and the charset of the document.

the actual character (diamond) is NOT in your database. It's a symbol such as (C) for copyright instead of its proper html entitiy.

There's no good way to look for these.

I'd suggest going back to ISO-8859-1

[gabeanderson]

Thanks, Brian. I see what you mean now when I review in other browsers.

However, if I switch back to ISO-8859-1, then I'm back to the same issue I was originally trying to address when I changed to UTF-8 (see attached).

It's this curly / smart quote. If I switch back to UTF-8, any way to find that character via a query?

Or can I address the issue with the source content / links that are causing this? We need those smart quotes since it's part of our product branding.

Thanks.

[gabeanderson]

FYI- I ended up switching back to ISO-8859-1, and changing the source content / link titles so that we don't have any smart quotes in the title tags since that seemed to be the root of this issue in the first place.