CREATE TABLE `user` (
`uid` int(11) NOT NULL AUTO_INCREMENT,
`uuid` varchar(50) NOT NULL,
`email` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`pwd` varchar(255) NOT NULL,
`data` longtext DEFAULT NULL,
`geloescht` tinyint(4) NOT NULL DEFAULT 0,
`role` varchar(10) NOT NULL,
`allowed_pet_owner_uuid` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`reset_password` longtext DEFAULT NULL,
`temp_token` varchar(255) DEFAULT NULL,
`reset_password_uuid` varchar(100) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_german2_ci;
INSERT INTO `user` (`uuid`, `email`, `pwd`, `data`, `geloescht`, `role`, `allowed_pet_owner_uuid`, `reset_password`, `temp_token`, `reset_password_uuid`)
VALUES
('123e4567-e89b-12d3-a456-426614174000', 'user@example.com', 'hashed_password_here', NULL, 0, 'admin', NULL, NULL, NULL, NULL);
UPDATE `user`
SET `email` = 'default@example.com'
WHERE `email` IS NULL;
UPDATE `user`
SET `allowed_pet_owner_uuid` = ''
WHERE `allowed_pet_owner_uuid` IS NULL;
SELECT * FROM user;
<?php
global $pdo; // Use the predefined PDO connection
$error = false;
$errortext = '';
$stage = 'prod';
if ('development' !== $stage) {
try {
// Execute the ALTER TABLE query
$sql = "ALTER TABLE `user`
MODIFY COLUMN `email` VARCHAR(150) NOT NULL,
MODIFY COLUMN `allowed_pet_owner_uuid` VARCHAR(255) NOT NULL,
ADD COLUMN `pin` VARCHAR(50) DEFAULT NULL,
ADD COLUMN `registration_code` LONGTEXT DEFAULT NULL";
echo 'exe';
$pdo->exec($sql);
// Validation: Check if columns were correctly modified
$sql = "SHOW COLUMNS FROM `user`";
$stmt = $pdo->query($sql);
$existing_columns = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
// Define expected columns
$expected_columns = ['email',
'allowed_pet_owner_uuid',
'pin',
'registration_code'];
$dropped_columns = ['reset_password_uuid'];
// Check if all expected columns exist
$missing_columns = array_diff($expected_columns, $existing_columns);
$remaining_dropped_columns = array_intersect($dropped_columns, $existing_columns);
if (!empty($missing_columns)) {
$error = true;
$errortext = "Error: Missing columns after ALTER TABLE: " . implode(', ', $missing_columns);
}
var_dump( $remaining_dropped_columns);
if (!empty($remaining_dropped_columns )) {
echo 'trufasdae';
$error = true;
$errortext .= " Error: Columns that should be dropped still exist: " . implode(', ', $remaining_dropped_columns);
}
$sql = "SELECT COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user'";
$result = $mysqli->query($sql);
if ($result) {
echo "Column Character Set and Collation:\n";
while ($row = $result->fetch_assoc()) {
echo "{$row['COLUMN_NAME']} - Charset: {$row['CHARACTER_SET_NAME']} - Collation: {$row['COLLATION_NAME']}\n";
}
$result->close();
} else {
echo "Error in query: " . $mysqli->error;
}
} catch (PDOException $e) {
echo $e;
$error = true;
$errortext = "Database error: " . $e->getMessage();
}
}
?>