query($checkTableQuery)->num_rows > 0;
if ($tableExists) {
// Drop the existing table if needed
$dropTableQuery = "DROP TABLE domain_descriptions";
if ($conn->query($dropTableQuery) === TRUE) {
echo "Existing domain_descriptions table dropped successfully.
";
} else {
echo "Error dropping table: " . $conn->error . "
";
exit;
}
}
// Create domain_descriptions table
$createTableQuery = "CREATE TABLE domain_descriptions (
id INT AUTO_INCREMENT PRIMARY KEY,
Domain_ID VARCHAR(10) NOT NULL UNIQUE,
Description VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)";
if ($conn->query($createTableQuery) === TRUE) {
echo "Domain descriptions table created successfully.
";
} else {
echo "Error creating table: " . $conn->error . "
";
exit;
}
// Default descriptions for all domains
$defaultDescriptions = [
'1' => 'Agriculture',
'2' => 'Energy',
'3' => 'IoT & Automation',
'4' => 'Healthcare',
'5' => 'Transportation',
'6' => 'Smart City',
'7' => 'Education',
'8' => 'Environment',
'9' => 'Cybersecurity',
'10' => 'Fintech',
'11' => 'E-Commerce',
'12' => 'Other'
];
// Get all domain tables
$domainTables = getDomainTables($conn);
$insertedDomains = [];
// Get domain IDs from existing tables
foreach ($domainTables as $domainTable) {
$domainNumber = str_replace('domain_', '', $domainTable);
$domainIdQuery = "SELECT DISTINCT Domain_ID FROM $domainTable LIMIT 1";
$domainIdResult = $conn->query($domainIdQuery);
if ($domainIdResult && $domainIdResult->num_rows > 0) {
$domainId = $domainIdResult->fetch_assoc()['Domain_ID'];
$description = isset($defaultDescriptions[$domainNumber]) ? $defaultDescriptions[$domainNumber] : 'Domain ' . $domainNumber;
// Insert into domain_descriptions table
$insertQuery = "INSERT INTO domain_descriptions (Domain_ID, Description) VALUES (?, ?)";
$stmt = $conn->prepare($insertQuery);
$stmt->bind_param("ss", $domainId, $description);
if ($stmt->execute()) {
echo "Added description for $domainId: $description
";
$insertedDomains[] = $domainId;
} else {
echo "Error adding description for $domainId: " . $stmt->error . "
";
}
$stmt->close();
}
}
// Insert any missing domains from our default list
foreach ($defaultDescriptions as $domainNumber => $description) {
$domainId = "D-" . str_pad($domainNumber, 2, '0', STR_PAD_LEFT);
if (!in_array($domainId, $insertedDomains)) {
$insertQuery = "INSERT INTO domain_descriptions (Domain_ID, Description) VALUES (?, ?)";
$stmt = $conn->prepare($insertQuery);
$stmt->bind_param("ss", $domainId, $description);
if ($stmt->execute()) {
echo "Added description for $domainId: $description (from defaults)
";
} else {
echo "Error adding description for $domainId: " . $stmt->error . "
";
}
$stmt->close();
}
}
echo "
Domain descriptions setup complete.";
// Close connection
$conn->close();
?>