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);
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,
DROP COLUMN `reset_password_uuid`";
$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);
}
if (!empty($remaining_dropped_columns)) {
$error = true;
$errortext .= " Error: Columns that should be dropped still exist: " . implode(', ', $remaining_dropped_columns);
}
if (!$error) {
echo 'now describing user table';
$sql = "DESCRIBE `user`";
$result = $mysqli->query($sql);
if ($result) {
echo "Table Structure (`user`):\n";
while ($row = $result->fetch_assoc()) {
echo "{$row['Field']} - {$row['Type']} - {$row['Null']} - {$row['Key']} - {$row['Default']} - {$row['Extra']}\n";
}
$result->close();
} else {
$error = true;
$errortext = "Error: Unable to fetch table structure.";
}
}
} catch (PDOException $e) {
$error = true;
$errortext = "Database error: " . $e->getMessage();
}
}
// Return result
return array(
'error' => $error,
'errortext' => $errortext
);
?>