Removing smart quotes from sql.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Removing smart quotes from sql.

Post by aravona »

I have a database which has products in, the names of the products are displayed as normal quotations in the database ' ... however on the website they get reverted to smart quotations.

I'm using a function that comes with one of the wordpress plugins to call price based on name of product.

The problem is the MySQL cannot read the smart quotes.

I found a bit of code, which is supposed to remove these smart quotes.

Code: Select all

function convert_smart_quotes($string) 
{ 
    $search = array(chr(145), 
                    chr(146), 
                    chr(147), 
                    chr(148), 
                    chr(151)); 
 
    $replace = array("'", 
                     "'", 
                     '"', 
                     '"', 
                     '-'); 
 
    return str_replace($search, $replace, $string); 
} 
Which I then use as follows:

Code: Select all

$query = 'SELECT wp_wpsc_product_list.price FROM `wp_wpsc_product_list` WHERE wp_wpsc_product_list.name = "' . wpsc_the_product_title() . '"';	
									$query2 = convert_smart_quotes($query);
But it doesnt seem to work.

Any ideas what I'm doing wrong?

Thanks,

Aravona
User avatar
social_experiment
DevNet Master
Posts: 2793
Joined: Sun Feb 15, 2009 11:08 am
Location: .za

Re: Removing smart quotes from sql.

Post by social_experiment »

Pardon my ignorance but what does smart quotes look like, it's the first time i've heard the term :|
“Don’t worry if it doesn’t work right. If everything did, you’d be out of a job.” - Mosher’s Law of Software Engineering
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Removing smart quotes from sql.

Post by aravona »

They are like, the curly looking quotes - systems such as wordpress put them in, as well as cuting and pasting from word will put them in to.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Removing smart quotes from sql.

Post by aravona »

Basically, if I run a MySQL query in phpmy admin such as:

SELECT * FROM table WHERE name = 'the-product-name'

If the product has a ' in the name then it shows up as a empty set - however if I delete this quotation mark in phpmyadmin and type it in again, it shows up (clearly there is something wrong with the ones being generated)

Any ideas how to clear this up so the MySQL query in my code works?
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Removing smart quotes from sql.

Post by Apollo »

Oh, the dreaded Word characters :( For some obscure reason, Word prefers ‘this’ over 'that', and “this” over "that".
Not to mention other freaky characters, like this low single quote: ‚ instead of comma, also as double variant: „ and backwards single and double quotes ‛ and ‟ and how about ′ or ″ or ‵ or ‶ :(
As for dashes, take your pick from - or ‐ or ‑ or ‒ or – or — or ― (yep, they're all different...)

Anyway, this is not SQL related. You just need to fix the strings themselves before using them in a query (or outputting them as html, or anything you do with them). For the strings that are already in your database, perhaps you can run a one-time script to read them, convert them, write them back?

It's got nothing to do with converting or fixing queries, that's for sure. Besides, if you convert a query instead of a string, any mysql_escaped string arguments may become corrupted after you convert funky quotes (which mysql_real_escape_string leaves untouched) to regular quotes (which should be escaped).

The convert_smart_quotes function in your first post is incorrect, or at least incomplete. It doesn't take encoding into account whatsoever, so for example it won't work on iso-8859-1 or utf-8 strings (and it's very likely your strings are one of those).
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Removing smart quotes from sql.

Post by aravona »

Hi thanks for the response.

Ok no worries I'll scrap that function its just something I found via google that people said worked (clearly it doesnt).

I'm not 100% sure how to move on then, as the database has 1500+ entries and only some of them have extra quotations in, and I only really need to clean up a few but theres the option for more entries to have this issue.

So what is the best way for me to clean up the entry names. Is it possible for me to remove the quotations via php then just use LIKE instead? I was playing around with this idea but didn't really get it working.

Thanks,

Aravona
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Removing smart quotes from sql.

Post by Apollo »

You'll have to know about the character encoding of your data first, otherwise there's no proper way to fix this.

If it's utf-8 encoded, try this:

Code: Select all

$s = str_replace(array("\xE2\x80\x98","\xE2\x80\x99","\xE2\x80\x9C","\xE2\x80\x9D","\xE2\x80\x93","\xE2\x80\x94"),array("\x27","\x27","\x22","\x22","\x2D","\x2D"),$s);
If it's windows-1252 encoded, try this:

Code: Select all

$s = str_replace(array("\x91","\x92","\x93","\x94","\x96","\x97"),array("\x27","\x27","\x22","\x22","\x2D","\x2D"),$s);
You can also try to do it in MySQL (phpMyAdmin), if your data is utf-8 encoded:

Code: Select all

UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(226,128,152), CHAR(39) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(226,128,153), CHAR(39) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(226,128,156), CHAR(34) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(226,128,157), CHAR(34) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(226,128,147), CHAR(45) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(226,128,148), CHAR(45) );
If it's windows-1252 encoded:

Code: Select all

UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(145), CHAR(39) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(146), CHAR(39) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(147), CHAR(34) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(148), CHAR(34) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(150), CHAR(45) );
UPDATE yourTable SET yourColumn = REPLACE( yourColumn, CHAR(151), CHAR(45) );
But since you're probably not 100% sure what encoding you're using, don't forget to make backups first :)
Last edited by Apollo on Mon Oct 10, 2011 9:44 am, edited 3 times in total.
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Removing smart quotes from sql.

Post by aravona »

I tried both, but neither seemed to work (though I have a feeling its my use of them that isn't right)

Code: Select all

 function changequote($s) 
{ 
    $s = str_replace(array("\x91","\x92","\x93","\x94","\x96","\x97"),array("\x27","\x27","\x22","\x22","\x2D","\x2D"),$s); 
	
	return $s;
} 
And then: $query2 = changequote($query);
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Removing smart quotes from sql.

Post by Apollo »

aravona wrote:And then: $query2 = changequote($query);
Wrong,
Apollo wrote:You just need to fix the strings themselves before using them in a query (or outputting them as html, or anything you do with them).
(...)
Besides, if you convert a query instead of a string, any mysql_escaped string arguments may become corrupted after you convert funky quotes (which mysql_real_escape_string leaves untouched) to regular quotes (which should be escaped).
Convert the string (e.g. the $productName or whatever it is), then mysql_real_escape_string it (like you should do with any string that you insert in a query), then build the $query.

(PS. I adjusted the example MySQL queries above, so if you tried those, try again)
aravona
Forum Contributor
Posts: 347
Joined: Sat Jun 13, 2009 3:59 pm
Location: England

Re: Removing smart quotes from sql.

Post by aravona »

Ok the first MySQL actually worked and updated the dodgey quotes. So if I echo the $query I have for the sql, and cut+paste it into phpmyadmin its actually working now instead of coming up blank.

Now its just I think my php because the prices are still not echoing on the main page.
Post Reply