PHPize Online / SQLize Online  /  SQLtest Online

<?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 ); ?>