Skip to content

Instantly share code, notes, and snippets.

@KariTrace

KariTrace/gist:3701904

Created September 11, 2012 20:46
Show Gist options
  • Save KariTrace/3701904 to your computer and use it in GitHub Desktop.
Save KariTrace/3701904 to your computer and use it in GitHub Desktop.
Download ZIP
Work in progress: Sanitize multiple phone fields in variable tables. I.E.: phone_work, fax_phone, etc.
Raw
gistfile1.txt
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Phone Number sanitization form</title>
<?PHP
class baseClass
{
/**
* @Comment: Object Global properties
*/
public $debug = TRUE;
public $execute = TRUE;
/**
* @Comment: Le-killer functions! No srsly, its a killer!
* @Param: callReferance, showDie int
*/
public function dieRequest($callReferance = NULL, $showDie = 0)
{
if($showDie != 0)
echo("Die requested by: " . $callReferance);
die;
}
/**
* @Comment: Debug function call./
* @Param: title string, response string, die int
*/
public function status($title = NULL, $response = NULL, $die = 0)
{
//no debug for you!
if($this->debug == FALSE)
return;
//easy full stop AND if debug is off and an error occures.
if($title == NULL)
$this->dieRequest("An error has occured. Please contact your administrator.", 1);
echo("<PRE><div color='black'>".$title.": ");
/**
* @Comment: should make this check for True or 1 as well to be a positive response.
*/
if ($response != NULL && is_numeric($response) == FALSE)
{
echo("<div style='color:red;font-weight:bolder;display:inline;'>");
print_r($response);
echo("</div>");
}
elseif ($response == NULL)
{
echo("<div style='color:green;display:inline;'>");
print_r("GOOD");
echo("</div>");
}
elseif (is_numeric($response) == TRUE)
{
echo("<div style='color:green;display:inline;'>");
print_r($response);
echo("</div>");
}
echo("</div></PRE>");
if($die != 0)
{
$this->dieRequest();
}
}
/**
* @Comment: Take a sql results and turn it into a PHP array.
* @Param: sqlResource SQL Resource, type constant, counter int
* @Return: arrayData array
*/
function enumerateSQLData($sqlResource = NULL, $type = MYSQL_ASSOC, $counter = 0)
{
if($sqlResource == NULL || $sqlResource == "") // improper resource passed in.
{
$this->status('baseClass->enumerateSQLData()->$sqlResource ', "IS NULL OR Malformed on ENTRY!", 1);
}
$this->status('baseClass->enumerateSQLData()->$sqlResource ', $sqlResource);
//container array
$arrayData = NULL;
//loop over sql execution
while ($row = mysql_fetch_array($sqlResource, $type))
{
$arrayData[$counter] = $row;
$counter++;
}
if($arrayData == NULL) //Need a way to return an empty index'd array if no results are found. Something end user easy.
{
$this->status('baseClass->enumerateSQLData()->$arrayData', "IS NULL!", 1);
}
unset($counter);
return $arrayData;
}
/**
* @Param: $location string [required], $user string [required], $password string [optinal], $database string [required]
* @Return: $mysqli object;
*/
public function createDBObject( $location = NULL, $user = NULL, $password = NULL, $database = NULL )
{
//Inbound var check
if( $location == NULL || $user == NULL || $database == NULL )
{
//public function status($title = NULL, $response = NULL, $die = 0)
$this->status("createConn missing params", "ERROR", 1);
}
//Connect me to the SugarCRM database
$mysqli = new mysqli( $location, $user, $password, $database );
//if connection fails
if ( $mysqli->connect_error )
$this->status('Could not connect to host','ERROR: ' . $mysqli->connect_error, 1);
return $mysqli;
}
}
/**
* @Author David E
* @Comment: Phone number manipulation
*/
class phoneClass
{
/**
* @Author David E
* @Comment Generic debug dump for this class. As most methods require very saimilar imput lets no repeat ourselves.
* COmmentt This is a terminal method. Execution will end.
* @Param $conn object [required], $tableName string [required], $debugClass object [optional], $referance string [required]
*/
private function connTableDeBugError($conn = NULL, $tableName = NULL, $debugClass = NULL, $referance = NULL )
{
if( $conn == NULL || $tableName == NULL )
{
if( $debugClass != NULL)
{
$debugClass->status($referance . "$phoneClass->getColumnNames() requires connection object and table name.", NULL, 1);
}else{
echo ( $referance ." $phoneClass->getColumnNames() requires connection object and table name.");
exit;
}
}
}
/**
* @Author David E
* @Comment Convert blank to NULL in the DB. this is mroe of a maintance function than an actual usable one.
* @Param $conn object [required], $tableName string [required], $debugClass object [optional]
* @Return $clmIDs array
*/
public function blankToNull($conn = NUUL, $tableName = NULL, $debugClass = NULL, $clmIDs = NULL)
{
//ensure we get the correct input
$this->connTableDeBugError($conn, $tableName, $debugClass, "blankToNull" );
if( $clmIDs != NULL && is_array($clmIDs) )
{
foreach( $clmIDs as $clmKey=>$clmValue )
{
$query = $conn->query("
UPDATE `". $tableName ."` SET `". $clmValue. "` IS NULL WHERE `". $clmValue ."` = '';
");
//execute update
$conn->query($query);
//freememory
$query->free();unset($query);
}
}else{
$debugClass->status("phoneClass->blankToNull()->$clmIDs must be array.", NULL, 1);
}
}
/**
* @Author David E
* @Comment Get Column Names with 'phone' int them
* @Param $conn object [required], $tableName string [required], $debugClass object [optional]
* @Return $clmIDs array
*/
public function getColumnNames($conn = NULL, $tableName = NULL, $debugClass = NULL)
{
//ensure we get the correct input
$this->connTableDeBugError($conn, $tableName, $debugClass, "getColumnNames" );
//create SQL query
$query = $conn->query("
SELECT column_name
FROM information_schema.columns
WHERE table_name = '". $tableName ."'
ORDER BY ordinal_position
");
//execute query
$clmNames = $query->fetch_all(MYSQLI_ASSOC);
//clear memory
$query->free();unset($query);
//setup new tracking vars
$clmIDs[$counter = 0] = array() ;
//compress returned array
foreach( $clmNames as $clmNamesKey=>$clmNamesValue )
{
foreach( $clmNamesValue as $clmNamesValueKey=>$clmNamesValueValue )
{
if( strstr($clmNamesValueValue, 'phone') == TRUE )
{
$clmIDs[$counter++] = $clmNamesValueValue;
}
}
}
//return data
return $clmIDs;
}
/**
* @Author David E
* @Comment Sanatize pohone numbers down to 10 numeric only digits
* @Param $conn object [required], $tableName string [required], $limit string [optional], $debugClass object [optional]
* @Return boolean
*/
public function sanatizePhoneData ($conn = NULL, $tableName = NULL, $debugClass = NULL, $limit = NULL )
{
//ensure we get the correct input
$this->connTableDeBugError($conn, $tableName, $debugClass, "sanatizePhoneData" );
//require the baseClass on this metho.
if( $debugClass == NULL )
{
echo("phoneClass->sanatizePhoneData()->$debugClass is required to be an object.");
exit;
}
//get column names :)
$clmNames = $this->getColumnNames($conn, $tableName);
//create a SQL column header string
$sqlString = "`id`,";
$counter = count($clmNames);
foreach ( $clmNames as $clmNameKey=>$clmNameValue )
{
$sqlString .= "`". $clmNameValue ."`";
if( $counter != 1)
$sqlString .= ",";
$counter--;
}
//select all the rows ID and ALL THE NAMES
//create SQL query
$query = $conn->query("
SELECT ". $sqlString ."
FROM ". $tableName ."
WHERE `id` IS NOT NULL
". $limit . "
");
//execute query
$clmData = $query->fetch_all(MYSQLI_ASSOC);
//clear memory
$query->free();unset($query);
//for item returned from the DB
foreach( $clmData as $clmDataKey=>$clmDataValue )
{
//foreach column per item returned
foreach( $clmDataValue as $clmDataValueKey=>$clmDataValueValue )
{
if( $clmDataValueValue != "" )
{
//echo( $clmDataValueValue . "<br />");
}elseif( $clmDataValueValue == "" )
{
//remove keys that have blank value. This reduces output dramaticly
unset($clmDataValue[$clmDataValueKey]);
}
//If data isnumeric == TRUE AND length == 10 UNSET this key as well.
if( $clmDataValueKey != "id" && is_numeric($clmDataValueValue) && strlen($clmDataValueValue) == 10 )
unset( $clmDataValue[$clmDataValueKey] );
}
//If only ID keys are left. remove the array from the parent array.
if ( count( $clmDataValue ) == 1 )
{
unset( $clmDataValue );
//else we have an ID that DOES HAVE an improperly formated phonen number
}else{
//Now we should have non empty, non well formated data with IDs
$itemID = NULL;
foreach( $clmDataValue as $clmDataValueKey=>$clmDataValueValue )
{
//only alter the phone number data
if($clmDataValueKey == "id")
{
$itemID = $clmDataValueValue;
}else{
//remove all but digits and save to array
$clmDataValue[$clmDataValueKey] = preg_replace('/\D/', "", $clmDataValueValue);
//now if number starts with a 1, remove it
if( stripos( $clmDataValue[$clmDataValueKey], "1" ) == 0 )
{
$clmDataValue[$clmDataValueKey] = substr($clmDataValue[$clmDataValueKey], 1, 10);
}
//now limit to 10 digits counting left to right
if( strlen( $clmDataValueValue ) > 10)
$clmDataValueValue = strrpos($clmDataValueValue, 0, 10);
$debugClass->Status("$clmDataValue", $clmDataValue);
//for debuging
/*
echo "table: " .$tableName . "<br />";//tbo name
echo "column: " . $clmDataValueKey . "<br />";//clm name
echo "number: " . $clmDataValue[$clmDataValueKey] . "<br />";//number
echo "id: " . $itemID . "<br />";
*/
//create the query
$query = $conn->query("
UPDATE `". $tableName ."`
SET `". $clmDataValueKey . "` = '". $clmDataValue[$clmDataValueKey] ."'
WHERE `id` = '". $clmDataValue['id'] ."';
");
//print query for debuggin
//$debugClass->status("$query", $query);
//execute update
try{
$conn->query($query);
echo "Executed \n";
}
catch (Exception $e)
{
//show query we failed on
$debugClass->status("$query", $query);
//show exception
$debugClass->status("$e", $e, NULL, 1);
//and just in case we over ride the EXIT from status
return false;
}
}
}
//clear item ID
unset($itemID);
}
}
return true;
}
}
?>
</head>
<body>
<!--// phone number sanitization form -->
<?PHP
//create objects
$baseClass = new baseClass;
$phoneClass = new phoneClass;
$dbName = "sugarcrm";
$tblName = "leads";
$DB = $baseClass->createDBObject( "localhost", "root", NULL, "". $dbName ."" );
$clmIDs= $phoneClass->sanatizePhoneData( $DB, 'leads', $baseClass );
?>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment