mysqli script stops working...?

[Supertex]

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.

[MrPhil]

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?

[Bob Terveuren]

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

[tgely]

@@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 tables
You can build conversion pairs in text files. This addon extends the conversion work.

[Supertex]

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

[♥kymation]

<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

[MrPhil]

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.