107 lines
4.2 KiB
PHP
107 lines
4.2 KiB
PHP
<?php
|
|
include "db.php";
|
|
|
|
$charSet = "utf8";
|
|
$collation = "utf8_general_ci";
|
|
|
|
$databases = array(
|
|
"hotlunch",
|
|
);
|
|
|
|
|
|
$Commands = array();
|
|
$fp = fopen("fix.sql","r");
|
|
while ($q = trim(fgets($fp,1000))) {
|
|
$Commands[] = $q;
|
|
}
|
|
|
|
|
|
|
|
for ($i = 0; $i < count($databases); $i++) {
|
|
$database = $databases[$i];
|
|
db_query("USE $database");
|
|
echo("*** $database ***\n");
|
|
|
|
$tables = db_query("SHOW TABLES");
|
|
while ($row = db_fetch_array($tables)) {
|
|
$tableName = $row[0];
|
|
echo("Table: $tableName\n");
|
|
|
|
$columns = db_query("SHOW COLUMNS FROM $tableName");
|
|
while ($row = db_fetch_array($columns)) {
|
|
$columnName = $row['Field'];
|
|
$columnType = $row['Type'];
|
|
$columnNull = $row['Null'];
|
|
|
|
if ($columnType == "date") {
|
|
echo("$columnName: $columnType\n");
|
|
$count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName < '1900-01-01'");
|
|
if ($count > 0) {
|
|
echo(" $tableName\n");
|
|
echo(" - $columnName $columnType ($count)\n");
|
|
echo("\n");
|
|
if ($columnNull == 'YES') {
|
|
echo(" UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01'\n");
|
|
db_query("UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01'");
|
|
}
|
|
else {
|
|
echo(" UPDATE $tableName SET $columnName = '2020-01-01' WHERE $columnName < '1900-01-01'\n");
|
|
db_query("UPDATE $tableName SET $columnName = '2020-01-01' WHERE $columnName < '1900-01-01'");
|
|
}
|
|
}
|
|
|
|
$count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName LIKE '%-00%'");
|
|
if ($count > 0) {
|
|
echo(" $tableName\n");
|
|
echo(" - $columnName $columnType ($count)\n");
|
|
echo("\n");
|
|
$results = db_query("SELECT * FROM $tableName WHERE $columnName LIKE '%-00%'");
|
|
while ($row = db_fetch_assoc($results)) {
|
|
$rnum = $row['record_num'];
|
|
$date = $row[$columnName];
|
|
$date = str_replace("-00","-01",$date);
|
|
$row[$columnName] = $date;
|
|
echo(" UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum\n");
|
|
db_query("UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum");
|
|
}
|
|
}
|
|
}
|
|
|
|
if ($columnType == "datetime") {
|
|
$count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName < '1900-01-01 00:00:00'");
|
|
if ($count > 0) {
|
|
echo(" $tableName\n");
|
|
echo(" - $columnName $columnType ($count)\n");
|
|
echo("\n");
|
|
if ($columnNull == 'YES') {
|
|
echo(" UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01 00:00:00'\n");
|
|
db_query("UPDATE $tableName SET $columnName = NULL WHERE $columnName < '1900-01-01 00:00:00'");
|
|
}
|
|
else {
|
|
echo(" UPDATE $tableName SET $columnName = '2020-01-01 00:00:00' WHERE $columnName < '1900-01-01 00:00:00'\n");
|
|
db_query("UPDATE $tableName SET $columnName = '2020-01-01 00:00:00' WHERE $columnName < '1900-01-01 00:00:00'");
|
|
}
|
|
}
|
|
|
|
$count = db_getVal("SELECT count(*) FROM $tableName WHERE $columnName LIKE '%-00%'");
|
|
if ($count > 0) {
|
|
echo(" $tableName\n");
|
|
echo(" - $columnName $columnType ($count)\n");
|
|
echo("\n");
|
|
$results = db_query("SELECT * FROM $tableName WHERE $columnName LIKE '%-00%'");
|
|
while ($row = db_fetch_assoc($results)) {
|
|
$rnum = $row['record_num'];
|
|
$date = $row[$columnName];
|
|
$date = str_replace("-00","-01",$date);
|
|
$row[$columnName] = $date;
|
|
echo(" UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum\n");
|
|
db_query("UPDATE $tableName SET $columnName = '$date' WHERE record_num = $rnum");
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
}
|
|
|
|
?>
|