/**
* what images have different mod times? - check images modtime agianst my modtime
*/
public function fun_getDifferentModTimesQuery()
{
//this one looks checks my files agiast the nwmls images and shows the nwmls files that are newer
$query = "SELECT ID, LN, Name, FROM_UNIXTIME(modtime) as My_ModTime, \t\t\t\n\t\t\tif (i.modtime < (SELECT LASTMODIFIEDDATETIME FROM nwmls.image WHERE (PICTUREFILENAME = i.Name)), 'NEWER', 'OLDER') AS NWMLS_Age\t\n\t\t\tFROM nwmls.image_index i\n\t\t\tHAVING NWMLS_Age = 'NEWER'\n\t\t\tORDER BY ID DESC ";
$field = "Name";
$arFileNames = fun_SQLQuery_Array($query, $field, $OtherDBConn = "");
return $arFileNames;
//good for figuring out the mod times 1
$debugQuery = "SELECT PICTUREFILENAME, FROM_UNIXTIME(LASTMODIFIEDDATETIME) as nwmls_mt, \n\t\t\t\tFROM_UNIXTIME((SELECT modtime FROM image_index WHERE image_index.Name = image.PICTUREFILENAME)) as my_mt \n\t\t\t\tFROM image WHERE LASTMODIFIEDDATETIME > (SELECT modtime FROM image_index WHERE Name = image.PICTUREFILENAME) ORDER BY LASTMODIFIEDDATETIME DESC limit 1000,1000";
//good for figuring our the modtimes 1 - great for debugging the output of changed files
$debugQuery2 = "SELECT ID, LN, Name, FROM_UNIXTIME(modtime) as My_ModTime, \n\t\t\t\tFROM_UNIXTIME((SELECT UPLOADEDDATETIME FROM image WHERE (PICTUREFILENAME = i.Name))) AS NWMLS_UploadTime,\n\t\t\t\tFROM_UNIXTIME((SELECT LASTMODIFIEDDATETIME FROM image WHERE (PICTUREFILENAME = i.Name))) AS NWMLS_ModTime,\t\n\t\t\t\tif (i.modtime < (SELECT LASTMODIFIEDDATETIME FROM image WHERE (PICTUREFILENAME = i.Name)), 'NEWER', 'OLDER') AS NWMLS_Age\n\t\t\t\tFROM image_index i \n\t\t\t\tHAVING NWMLS_Age = 'NEWER'\n\t\t\t\tORDER BY ID DESC ";
}
function fun_Mysql_Optimize_Columns($DB, $Sample_Table, $Test_HowMany_Rows = 500, $New_Table = "", $New_Table_ID = "")
{
global $conn;
echo "\n\n<pre>\n";
echo "\n\n\n************************************* Starting Mysql Optimize *************************************\n\n";
//these didn't work, b/c sometimes I need it to skip and others dont need it
//1. problem 1 - no records to optimize - fixed
//2. problem 2 - not enough records to optimize correctly, will fix in mysql insert
// if ($Test_HowMany_Rows < 100)
// {
// $Test_HowMany_Rows = 300;
// }
// if ($rsTotal < $Test_HowMany_Rows)
// {
// $str = "Skipped rsTotal < TestHowManyRows($Test_HowMany_Rows)\n";
// echo "$str";
// return $str;
// }
//got to possible problems with this, on optimizing a small table that will possibly change the fields to being to small or the wrong type
//skip if no records to optimize
$query = "SELECT COUNT(*) as Total FROM `{$DB}`.`{$Sample_Table}`;";
$field = "Total";
$rsTotal = fun_SQLQuery2($query, $field);
echo "Enough rows to optimize???? - rowsTotal=({$rsTotal})\n";
echo "sample table query: {$query}\n";
if ($rsTotal < 1) {
$str = "Skipping Optimize\n**** Optimize END **** \n";
echo "{$str}";
return FALSE;
}
//debug
//die("\n\nin opto\n\n");
//get fields from db - SHOW COLUMNS FROM mytable FROM mydb;
$query = "SHOW COLUMNS FROM `{$DB}`.`{$Sample_Table}` FROM `{$DB}`;";
echo "{$query}\n";
$field = "Field";
$arColumns = fun_SQLQuery_Array($query, $field);
//get mysql info
$query = "SELECT * FROM `{$DB}` . `{$Sample_Table}` ORDER BY RAND() LIMIT 0,{$Test_HowMany_Rows}";
$result = mysqli_query($conn, $query) or die("Select Query failed : " . mysqli_error() . " :<br>\r\n {$query}\n");
$i = 0;
while ($row = mysqli_fetch_object($result)) {
foreach ($arColumns as $Column) {
$rsData = $row->{$Column};
//save the vars to array
if ($rsData) {
$arRow[$i][$Column]['Data'] = $rsData;
$arRow[$i][$Column]['DataType'] = fun_Mysql_Sample_Field_Data_Type($rsData, $rtnDataLength, $rtnZeroFill, $rtnLenth_After_Decimal);
$arRow[$i][$Column]['DataLenth'] = $rtnDataLength;
}
if ($rtnZeroFill != "") {
$arRow[$i][$Column]['ZeroFill'] = $rtnZeroFill;
}
if ($arRow[$i][$Column]['DataType'] == "Decimal") {
$arRow[$i][$Column]['Chars_After_Dec'] = $rtnLenth_After_Decimal;
}
}
$i++;
}
//end queryable rs
//debug
//print_r($arRow);
//move through columns
$NewDataType = "";
$NewDataLength = 0;
foreach ($arColumns as $Column) {
echo "\n\n\nColumn:{$Column}\n";
//move through all the test rows
$NewDataLengthTimes = 0;
for ($i = 0; $i < count($arRow); $i++) {
$cData = $arRow[$i][$Column]['Data'];
$cDataType = $arRow[$i][$Column]['DataType'];
$cDataLenth = $arRow[$i][$Column]['DataLenth'];
$cZeroFill = $arRow[$i][$Column]['ZeroFill'];
$cCharsADec = $arRow[$i][$Column]['Chars_After_Dec'];
if ($cDataType and $NewDataType != "Varchar") {
$NewDataType = $cDataType;
}
if ($NewDataType == "Varchar") {
$NewDataType = "Varchar";
}
if ($NewDataType == "Decimal") {
$NewDataType = "Decimal";
}
if ($NewCharsADec < $cCharsADec && $cCharsADec != "") {
$NewCharsADec = $cCharsADec;
}
if ($cDataType == "Integer" && $cZeroFill == 1) {
$NewZeroFill = 1;
}
if ($NewDataLength < $cDataLenth) {
$NewDataLength = $cDataLenth;
$NewDataLengthTimes = $NewDataLengthTimes + 1;
//singal that it could be a character(DataLength)
}
echo "{$i}:DataType:{$cDataType} DataLength:{$cDataLenth} ZeroFill:{$cZeroFill} CharAfterDec:{$cCharsADec} Char:{$NewDataLengthTimes} {$cData}\n";
}
//for each row for that column
if ($NewDataType == "Integer" && $NewCharsADec > 0) {
$NewDataType = "Decimal";
}
if ($NewDataType == "Varchar" && $NewDataLength > 250) {
$NewDataType = "Text";
}
if ($NewDataType == "") {
$NewDataType = "Varchar";
}
$ColumnType = fun_Mysql_Query_Column_Setup($NewDataType, $NewDataLength, $NewZeroFill, $NewCharsADec, $NewDataLengthTimes);
//$arSummary[$Column]['Data'] = "Summary";
//$arSummary[$Column]['DataType'] = $NewDataType;
//$arSummary[$Column]['DataLenth'] = $NewDataLength;
//$arSummary[$Column]['ZeroFill'] = $NewZeroFill;
//$arSummary[$Column]['Chars_After_Dec'] = $NewCharsADec;
$arSummary[$Column] = $ColumnType;
echo "00:DataType:{$NewDataType} DataLength:{$NewDataLength} ZeroFill:{$NewZeroFill} CharAfterDec:{$NewCharsADec} Char:{$NewDataLengthTimes} - {$ColumnType}\n";
$NewDataType = "";
$NewDataLength = "";
$NewZeroFill = "";
$NewCharsADec = "";
}
//end for each columns
echo "\n\n";
echo "Optimization Summary\n";
//Mysql Column Summary of what to do and how to optimize them
print_r($arSummary);
//1. Get primary key for skip
$query = "DESCRIBE `{$DB}`.`{$Sample_Table}`";
$result = mysqli_query($conn, $query) or die("Select Query failed : {$query}<br>\n" . mysqli_error() . " :<br>\n");
while ($row = mysqli_fetch_object($result)) {
$rsField = $row->Field;
$rsKey = $row->Key;
if (preg_match("/pri/i", $rsKey)) {
//get rid of the primary key
unset($arSummary[$rsField]);
}
}
//2.DATE -> UPDATE hostdb.import_sc_sales SET test=UNIX_TIMESTAMP(STR_TO_DATE(test, "%m/%d/%Y")) //2007-08-15 15:00:16
//update date first to integer format -before altering to mysql integer column type
foreach ($arSummary as $Column => $ColumnType) {
if (preg_match("/Date/", $ColumnType)) {
//$query = "UPDATE `$DB`.`$Sample_Table` SET $Column=UNIX_TIMESTAMP(STR_TO_DATE($Column, '%m/%d/%Y'))";
//echo "DatesToInteger:$Column -> $query\n";
//$result = mysqli_query($conn, $query) or die("Select Query failed : $query<br>\n" . mysqli_error() . " :<br>\n");
//using php to transform string to unixtimestamp
fun_Mysql_Optimize_Date($DB, $Sample_Table, $New_Table_ID, $Column, $OtherDBConn);
$arSummary[$Column] = "INTEGER NOT NULL DEFAULT 0";
}
}
//3.Setup Query
foreach ($arSummary as $field => $ColumnType) {
$arMysql[] = "MODIFY COLUMN `{$field}` {$ColumnType}";
}
print_r($arMysql);
// //are we working on current table or figuring out what table to make or fix
// if ($New_Table)
// {
// $Check_Table = $New_Table;
// }
// else
// {
// $Check_Table = $Sample_Table; //working same table as we are optimizing
// }
//
// //Are we working on a live table??
// $query = "SHOW TABLE STATUS FROM $DB WHERE (Name = '$Check_Table')";
// $field = "Name";
// $IsTable = fun_SQLQuery2($query, $field);
//
// if (!$IsTable) //make table if need be
// {
// //make table
// $query = "CREATE TABLE `$DB`.`$New_Table` ( `$New_Table_ID` int NOT NULL AUTO_INCREMENT, PRIMARY KEY(`$New_Table_ID`)) ENGINE = MYISAM;";
// echo "$query\n";
// mysqli_query($conn1, $query) or die("Select Query failed : " . mysqli_error() . " :<br>\r\n $query");
// }
//setup mysql query for altering current table
$csvColumns = implode(",", $arMysql);
echo "csvColumns: {$csvColumns}\n\n\n";
$query = "ALTER TABLE `{$DB}`.`{$Sample_Table}` {$csvColumns}";
echo "Modify:{$query}\n";
$result = mysqli_query($conn, $query) or die("Select Query failed : {$query}<br>\n" . mysqli_error() . " :<br>\n");
//print_r($arRow);
echo "************************************* The End - Mysql Optimize *************************************";
echo "</pre>\n";
//DEBUG
//die("\n die in opto \n");
return TRUE;
}
/**
* get columns from mysql table
* @param string $Table get columns from this table
* @param string $DB database
* @return mixed array or not of columns from table
*/
function fun_Mysql_getColumns($Table, $DB)
{
$query = "SHOW COLUMNS FROM `{$Table}` FROM `{$DB}`;";
$field = "Field";
$arColumns = fun_SQLQuery_Array($query, $field);
return $arColumns;
}
