Spaces:
Sleeping
Sleeping
File size: 3,774 Bytes
102fe5c | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 | <?php
/**
* Domain Descriptions Table Creation
*
* This script creates a domain_descriptions table and populates it with
* descriptions for each domain, fetching domain IDs from existing domain tables.
*/
// Include database connection
require_once 'includes/db_connect.php';
require_once 'includes/domain_utils.php';
// Check if table already exists
$checkTableQuery = "SHOW TABLES LIKE 'domain_descriptions'";
$tableExists = $conn->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.<br>";
} else {
echo "Error dropping table: " . $conn->error . "<br>";
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.<br>";
} else {
echo "Error creating table: " . $conn->error . "<br>";
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<br>";
$insertedDomains[] = $domainId;
} else {
echo "Error adding description for $domainId: " . $stmt->error . "<br>";
}
$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)<br>";
} else {
echo "Error adding description for $domainId: " . $stmt->error . "<br>";
}
$stmt->close();
}
}
echo "<br>Domain descriptions setup complete.";
// Close connection
$conn->close();
?> |