Page 1 of 1

Removing smart quotes from sql.

Posted: Sun Oct 09, 2011 5:10 am
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

Re: Removing smart quotes from sql.

Posted: Sun Oct 09, 2011 9:20 am
by social_experiment
Pardon my ignorance but what does smart quotes look like, it's the first time i've heard the term :|

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 2:24 am
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.

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 7:00 am
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?

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 7:34 am
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).

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 8:05 am
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

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 9:21 am
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 :)

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 9:31 am
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);

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 9:42 am
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)

Re: Removing smart quotes from sql.

Posted: Mon Oct 10, 2011 10:49 am
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.