Supertex Posted December 26, 2014 Share Posted December 26, 2014 I have a neat little script that I call in a browser to unify naming on my products: <?php include ("dbglobal.php"); $mysqli = new mysqli($DBHost, $DBUser, $DBPasw, $DBName); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: (" . $mysqli->connect_errno . ") " . $mysqli->connect_error; } $res = $mysqli->query("SELECT products_id, products_name FROM products_description WHERE language_id = 1"); while ($row = $res->fetch_assoc()) { $new_name = preg_replace('/°/','Deg', $row['products_name']); if (!$mysqli->query("UPDATE products_description SET products_name = '". $new_name ."' WHERE products_id = '". $row['products_id'] ."' LIMIT 1")) { echo "MySQL Error: (" . $mysqli->errno . ") " . $mysqli->error; } echo "Updated Product #: <b>". $row['products_id'] ."</b><br />\n\tOld Name: ". $row['products_name'] ."<br />\n\tNew Name: ". $new_name ."<br />\n"; } echo '<center><--End Operations--></center>'; ?> I was in the process of putting a 'search engine URL' mod in, when I figured out that some of my special symbols (like 'degree') caused issues with the mod. So I thought I'd just use the above file to rename appropriately. Well...it worked for the first 2-3 passes. After that...it just simply doesn't work anymore, and I can't for the LIFE of me, understand why. It echoes like it should, but it's no longer changing the names. The only thing I've changed since it worked, is the 'find' and the 'replace'. I thought perhaps what I was searching for was causing an issue, but even when I revert the DB and re-ran prior (successful) operations...they no longer work either. Any ideas what I've done here? PHP version conflict....I'm at a loss. osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref Link to comment Share on other sites More sharing options...
MrPhil Posted December 26, 2014 Share Posted December 26, 2014 Your search pattern ° appears to be a two-byte Latin-1 representation of a single UTF-8 character. Any chance that you used to be Latin-1, but are now UTF-8? Has your host just upgraded PHP? Somewhere around PHP 5.3 or 5.4, the default for character functions changed from Latin-1 to UTF-8. Maybe your search pattern is now two UTF-8 characters (4 bytes) instead of one. How are you editing this script? Link to comment Share on other sites More sharing options...
Bob Terveuren Posted December 27, 2014 Share Posted December 27, 2014 Hi You could try wrapping the whole loop' while ($row = $res->fetch_assoc()) {' in a try/catch pair and/or insert a PHP call to return all errors error_reporting(E_ALL); ini_set('display_errors', '1'); at the start of the loop - one of those may throw up the fatal error for you. Following on Phil's post above - try adding the 'u' (pcre-utf8) modifier to the preg_replace - that may flash up an error report for you rather than a fatal error Link to comment Share on other sites More sharing options...
tgely Posted December 27, 2014 Share Posted December 27, 2014 @@Supertex you can find my solution for oscommerce https://github.com/Gergely/oscommerce2-1/archive/character_convert.zip Special character or text conversion adapted for tablesYou can build conversion pairs in text files. This addon extends the conversion work. osCommerce based shop owner with minimal design and focused on background works. When the less is more.Email managment with tracking pixel, package managment for shipping, stock management, warehouse managment with bar code reader, parcel shops management on 3000 pickup points without local store. Link to comment Share on other sites More sharing options...
Supertex Posted December 27, 2014 Author Share Posted December 27, 2014 Thanks for the replies. Seems it's the same handful of guys over and over that respond. Perhaps at some point I'll be able to help answer some of the many questions that get asked. My understanding of encoding is, at best, obscure. I can tell you that my server connection collation is UTF8, as is the server charset. Tables' collation is Latin. My php ver is 5.3, and no upgrades that I know of in recent past. - However, when I mentioned in the OP, that I'd reloaded the DB and edits that had "just worked" now didn't, I mean within the span of an hour. This, as in, I made passes to replace one special char (by copying it from a db echo and pasting it directly into the preg_ statement in ultra-edit, then saving it via ftp, and calling the file in a browser) successfully, switched to a different pass, to replace a different special char, and it failed. When it failed, it didn't error or produce a blank screen....it echoed every product 'old' and 'new' but 'new' was the same as 'old' - it didnt actually do anything. So I exported the original DB, imported it to the DB I was working with, and tried to re-run what was previously (in minutes past) successful...nada. It's like the webhost just told the server to ignore preg-replace, or I'd broken the script. I suspected the latter, so...the script exists in the live site, and all my dev directories are exact copies of live - except for the config files, obviously. So when I think I've broken something, I can always jump over to another dev site and the unaltered file is available there....and it didnt work there either. Also worth mention, is at the start of this, I'd just done the exact same export->import so that what I started working with was as close to the live site as possible. Made me think there was some sort of cache somewhere screwing things up...like the first char to cause failure was having a persistent effect. I ended up just doing it all in phpmyadmin, so I no longer have special chars anywhere but the descriptions. It was a dumb idea for me to use them in the first place, as most people can't (or wont) use them in search engines anyway. It just baffled me how that script just simply stopped working. I've used it over and over and over with no issue for about 3 years. I'm quite certain that it's something I've done, I just don't know what. In this case, ignorance is not bliss. =P osC v2.3.1 MySQL v8.0.32 PHP v5.6.40 Installed addons: . Attribute Sets Plus .. Create Account & Manual Order Maker .. Customer Testimonials 2.3.4 .. Customer Blacklist .. Dynamic Info Pages .. FedEx Web Svcs v9 .. Filtered Sales Report .. Generic Box .. Google XML Sitemap SEO .. Maximum Order Value .. Modular Front Page .. Monthly Sales & Tax Report .. Multiple Products Manager .. Must Accept Terms & Conditions .. Order Editior .. PDF Customer Invoice .. Price in Cart Only .. Product Sort/Order .. Product Sort in Cart .. Quantity Discounts .. Restrict Delivery Methods .. SEO Header Tags - Reloaded .. Separate Pricing Per Customer .. Simpler Admin Session Length Control .. Sitemap SEO .. Show Free Ship + Modules .. Specials by Category for SPPC .. Store Mode (open|closed|maintenance) .. Store Pickup Shipping .. Theme Switcher .. Ultimate SEO URLs 5 Pro .. UPS XML Rates & Svcs 1.4 .. USPS methods 7.3.1 .. Who's Online Dashboard . Fixes: Add to cart -> 'product not found' : FIX Login issues with IE 11 : FIX Tools: Incredibly Handy: osC Xref Link to comment Share on other sites More sharing options...
♥kymation Posted December 27, 2014 Share Posted December 27, 2014 <snipped> I can tell you that my server connection collation is UTF8, as is the server charset. Tables' collation is Latin. This is a problem. You're telling the browser that all characters are represented as UTF-8, the server that all characters are sent and received as UTF-8, and the database that those same characters are stored as Latin-1. I'm not sure what is going to happen to those characters, and I don't believe that the result is even defined anywhere. If it is, I'd certainly like to know. The most likely result would be that the database does the conversion and stores the characters as Latin-1. Then it spits those same characters out again (still as Latin-1) when read, and your server sends them out believing them to be UTF-8. This will result in broken characters in the browser. Were you replacing characters that did not display correctly? It's possible that you were actually replacing the characters, but the difference in the stored collation and the displayed collation meant that they are still broken. I have no idea why this would work at one time and not another, unless the MySQL database version was updated and the default changed. The only solution to this mess is to make all of your collations the same. Either use UTF-8 throughout or Latin-1 throughout. UTF-8 is the best choice for a number of reasons, but it could mean that you'll have to do all of those conversions again. There's a conversion tool in osCommerce 2.3.4 that can help. Regards Jim See my profile for a list of my addons and ways to get support. Link to comment Share on other sites More sharing options...
MrPhil Posted December 28, 2014 Share Posted December 28, 2014 Just a nit here, but encoding (character set) and collation are two different things. You have an encoding which is the binary representation of characters, such as Latin-1 (single byte, limited to 256 characters) or UTF-8 (1 to 4 bytes per character, with at least 64k characters available). Given an encoding, you then have different collations you can specify for sorting purposes. It might be a very generic (binary) sort, it might be language-specific (German, French, etc. accented letters and special letters), it might be a telephone book or dictionary sort, it might be case-independent, etc. For osC's purposes, the specific collation used is generally not that important, although you should have consistent collation throughout your database, lest you get error messages from MySQL. The encoding is always important, unless you restrict your character set to 7-bit ASCII and never have any accented characters or odd punctuation (including MS Smart Quotes!), in which case the encodings are interchangeable. MySQL and phpMyAdmin frequently mix encoding and collation together under the title "collation", but note that normally they have combined the two, e.g., utf8_general_ci (UTF-8 encoding, more or less binary sorting except that A and a are the same). That is a bit unfortunate, as it leads people to refer to collation when they mean encoding. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.