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);
<?php
// Prepare the statement
$q = mysqli_prepare($mysqli, "SHOW COLUMNS FROM `user`");
// Execute the query
$q->execute();
// Get the result
$result = $q->get_result();
// Fetch and display column names
if ($result) {
echo "Columns in `user` table:\n";
while ($row = $result->fetch_assoc()) {
echo "- " . $row['Field'] . "\n";
}
} else {
echo "Error executing SHOW COLUMNS query.";
}
<?php
$error = false;
$errortext = '';
$stage = 'prod';
if ('development' !== $stage) {
// Check connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Prepare the ALTER TABLE statement
$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`";
// Execute the query
$q = mysqli_prepare($mysqli, $sql);
// Execute the query
$q->execute();
// Get the result
$result = $q->get_result();
if (is_null($result)) {
$error = true;
$errortext = "Error in SQL ALTER TABLE statement for user table: " . $mysqli->error;
}
// Validation: Check if the changes were applied successfully
if (!$error) {
$sql = "SHOW COLUMNS FROM `user`";
$stmt = $mysqli->prepare($sql);
if ($stmt) {
$stmt->execute();
$result = $stmt->get_result();
$existing_columns = [];
while ($row = $result->fetch_assoc()) {
$existing_columns[] = $row['Field'];
}
// 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);
}
$stmt->close();
} else {
$error = true;
$errortext = "Error: Unable to fetch column list from the user table.";
}
}
// Close the database connection
$mysqli->close();
}
// Return result
return array(
'error' => $error,
'errortext' => $errortext
);
?>