Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions

Reply
 
Thread Tools Display Modes
  #1  
Old 04-15-2009, 07:16 PM
gabeanderson gabeanderson is offline
 
Join Date: Feb 2006
Posts: 21
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default query to find, then update characters in post titles & body

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).

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.
Attached Images
File Type: png SNAG-2123.png (4.7 KB, 0 views)
Reply With Quote
  #2  
Old 04-16-2009, 06:07 AM
vbplusme vbplusme is offline
 
Join Date: Sep 2008
Location: CyberSpace
Posts: 332
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Are you using phpMyAdmin to run your queries?
Reply With Quote
  #3  
Old 04-16-2009, 12:20 PM
gabeanderson gabeanderson is offline
 
Join Date: Feb 2006
Posts: 21
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I'm using the "Execute SQL Query" feature via Admin CP.
Reply With Quote
  #4  
Old 04-20-2009, 01:11 PM
gabeanderson gabeanderson is offline
 
Join Date: Feb 2006
Posts: 21
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Anyone? Anyone?

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

Code:
select * from posts
where title like 'funky-character';
And this:

Code:
select * from posts
where body-text like 'funky-character';
Thanks!
Reply With Quote
  #5  
Old 04-22-2009, 02:09 PM
gabeanderson gabeanderson is offline
 
Join Date: Feb 2006
Posts: 21
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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

Any pointers, please?

Thanks!
Reply With Quote
  #6  
Old 04-22-2009, 03:11 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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:
PHP Code:
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".
Reply With Quote
  #7  
Old 04-22-2009, 06:32 PM
gabeanderson gabeanderson is offline
 
Join Date: Feb 2006
Posts: 21
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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 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:

Code:
select * from post
where pagetext like '%raquo%'
Any other ideas?

Thanks!
Attached Images
File Type: png SNAG-2156.png (32.6 KB, 0 views)
Reply With Quote
  #8  
Old 04-22-2009, 06:42 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #9  
Old 04-22-2009, 06:54 PM
gabeanderson gabeanderson is offline
 
Join Date: Feb 2006
Posts: 21
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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.
Reply With Quote
  #10  
Old 04-22-2009, 07:11 PM
Lynne's Avatar
Lynne Lynne is offline
 
Join Date: Sep 2004
Location: California/Idaho
Posts: 41,180
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

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?
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 12:52 AM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04374 seconds
  • Memory Usage 2,265KB
  • Queries Executed 14 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (3)bbcode_code
  • (1)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (10)post_thanks_box
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (2)postbit_attachment
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete