I’ve recently started programming an international traveling website that accepts input from users all over the world. Since it’s likely that users will type in their native language, the functionality to accept accented characters became an absolute necessity. I’ve recently incorporated some of this new functionality into the MySQLGoodies class for PHP.
This class is available for download at the link below:
http://files.endseven.net/?091411-PHP-MySQLGoodies-1.1.zip
Onward, to the changes!
A debug variable called $bDebugMode has been added. The previous version would output every MySQL error it encountered. In this version, if $bDebugMode is set to false, only the error message you provide will be output. Otherwise, it will continue to output the latest value specified by mysql_error(). Debug mode is particularly useful while developing a web application, but should be turned off once the application goes live.
A new function called jSetNamesUTF8() has been added. This function helps the database store accented characters and should be called immediately after connecting to the database. For more information, please read this article: Configuring the Character Set and Collation for Applications.
A new function called jMySQLQuery() has been added. This is a wrapper function and acts almost identically to the built-in mysql_query() function. This function, however, utilizes the new $bDebugMode variable to control the output of error messages.
A new function called jMySQLError() has been added. This is also a wrapper function and acts almost identically to the built-in mysql_error() function. This function also utilizes the new $bDebugMode variable to control the output of error messages.
A new function called jMySQLGenerateHash() has been added. This function is included simply as a personal preference. In my MySQL tables, I like to have not only an auto-incrementing “ID” field, but also a corresponding “Hash” field that contains the md5 value of the “ID” field. This is useful when you want to pass the “ID” through a URL, but don’t want the value displayed to the user plain as day. Besides, hash values are cool.
The jMySQLSafe() function has been updated to provide more flexibility. Passing an accented character through the built-in htmlentities() function causes character conversion and storage problems, so optional variables have been added to make the function more versatile.
An English locale has been added to the jMySQLClearUTF() function using the built-in setlocale() function. This value is set to English by default, but can be changed to any supported locale.
<?php
class MySQLGoodies {
/*
*
* Class Name: MySQLGoodies
* Author: Jimmy K.
* Website: http://www.endseven.net/
* Version: 1.1
*
* I wrote this class for convenience when programming my projects. If you
* disagree with any of it's contents, don't use it. ;)
*
*/
var $bDebugMode = true; // whether or not we're debugging..
var $oConnection; // store the connection..
var $oDatabase; // store the selected database..
/* connect to the mysql database. */
public function jMySQLConnect($sHost, $sUsername, $sPassword, $sDatabase) {
$this->oConnection = mysql_connect($sHost, $sUsername, $sPassword) or die("Unable to connect to database! " . $this->jMySQLError());
$this->oDatabase = mysql_select_db($sDatabase, $this->oConnection) or die("Unable to select database! " . $this->jMySQLError());
return $this->oConnection;
}
/* disconnect from the mysql database. */
public function jMySQLDisconnect() {
@mysql_close($this->oConnection) or die("Unable to disconnect from the database! Is that even possible?! " . $this->jMySQLError());
}
/* set the names for accented characters. */
public function jSetNamesUTF8() {
$this->jMySQLQuery("SET NAMES 'utf8' COLLATE 'utf8_general_ci';", "Unable to set collation!");
}
/* execute a simple select query. */
public function jMySQLSelect($sFromField, $sFromTable, $sTargetField, $sTargetValue) {
$sQuery = "SELECT `" . $sFromField . "` FROM `" . $sFromTable . "` WHERE `" . $sTargetField . "` = '" . $sTargetValue . "'";
$oQuery = mysql_query($sQuery, $this->oConnection) or die("Unable to select `" . $sFromField . "`! " . $this->jMySQLError());
return @mysql_result($oQuery, 0);
}
/* execute a query. */
public function jMySQLQuery($sQuery, $sErrorMessage = "Unable to perform query!") {
$oQuery = mysql_query($sQuery) or die($sErrorMessage . " " . $this->jMySQLError());
return $oQuery;
}
/* get the current mysql error. */
public function jMySQLError() {
return $this->bDebugMode ? mysql_error() : "";
}
/* generate a mysql hash. */
public function jMySQLGenerateHash($sTable, $sTargetValue, $sTargetField = "ID", $sHashField = "Hash") {
$sHash = md5($sTargetValue);
$sQuery = "UPDATE `" . $sTable . "` SET `" . $sHashField . "` = '" . $sHash . "' WHERE `" . $sTargetField . "` = '" . $sTargetValue . "';";
$oQuery = mysql_query($sQuery) or die("Unable to update hash! " . $this->jMySQLError());
return $sHash;
}
/* make the specified value(s) safe for mysql. */
public function jMySQLSafe($sValue, $bConvertEntities = true, $bClearMicrosoft = true, $bClearUTF = true) {
if (is_array($sValue)) {
$aReturn = array(); foreach ($sValue as $oKey => $oValue) {
$aReturn[$oKey] = $oValue; // set the return value..
if ($bClearUTF) $aReturn[$oKey] = $this->jMySQLClearUTF($aReturn[$oKey]); // clear utf chars..
if ($bClearMicrosoft) $aReturn[$oKey] = $this->jMySQLClearMicrosoft($aReturn[$oKey]); // clear microsoft chars..
$aReturn[$oKey] = stripslashes($aReturn[$oKey]); // strip slashes..
if ($bConvertEntities) $aReturn[$oKey] = htmlentities($aReturn[$oKey]); // convert html entities..
$aReturn[$oKey] = trim(mysql_real_escape_string($aReturn[$oKey])); // trim and escape..
}
} else {
$aReturn = $sValue; // set the return value..
if ($bClearUTF) $aReturn = $this->jMySQLClearUTF($aReturn); // clear utf chars..
if ($bClearMicrosoft) $aReturn = $this->jMySQLClearMicrosoft($aReturn); // clear microsoft chars..
$aReturn = stripslashes($aReturn); // strip slashes..
if ($bConvertEntities) $aReturn = htmlentities($aReturn); // convert html entities..
$aReturn = trim(mysql_real_escape_string($aReturn)); // trim and escape..
}
return $aReturn;
}
/* convert non-utf characters. */
public function jMySQLClearUTF($sValue) {
if (function_exists("iconv")) {
setlocale(LC_ALL, "en_US");
$sReplacement = iconv("UTF-8", "ASCII//TRANSLIT", $sValue);
$sReturn = "";
for ($i = 0; $i < strlen($sReplacement); $i++) {
$sChar1 = $sReplacement[$i]; $sChar2 = mb_substr($sValue, $i, 1);
$sReturn .= $sChar1 == "?" ? $sChar2 : $sChar1;
}
return $sReturn;
}
return $sValue;
}
/* convert microsoft characters. */
public function jMySQLClearMicrosoft($sValue) {
$aSearch = array(chr(145), chr(146), chr(147), chr(148), chr(151), "–", "…");
$aReplacments = array("'", "'", "\"", "\"", "-", "-", "...");
return str_replace($aSearch, $aReplacments, $sValue);
}
}
?>
Tags: Class, Microsoft, MySQL, PHP, UTF
