PHPize Online / SQLize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
-- Create the Events table CREATE TABLE Events ( id INT AUTO_INCREMENT PRIMARY KEY, Author INT NOT NULL, Title VARCHAR(255) NOT NULL, Description TEXT NOT NULL, Start DATETIME NOT NULL, End DATETIME NOT NULL, URL VARCHAR(255) NOT NULL, Location VARCHAR(255) NOT NULL, Location_URL VARCHAR(255) NOT NULL, `Virtual` TINYINT NOT NULL, Multiple_dates TINYINT DEFAULT 0, Recurring TINYINT DEFAULT 0, On_Demand TINYINT DEFAULT 0 ) ENGINE=InnoDB; -- Create the Events_Vendors table CREATE TABLE Events_Vendors ( vid INT AUTO_INCREMENT PRIMARY KEY, Vendor VARCHAR(255) NOT NULL ) ENGINE=InnoDB; -- Create the Events_Vendors_Tags table CREATE TABLE Events_Vendors_Tags ( event_id INT NOT NULL, vendor_id INT NOT NULL, PRIMARY KEY (event_id, vendor_id), FOREIGN KEY (event_id) REFERENCES Events(id) ON DELETE CASCADE, FOREIGN KEY (vendor_id) REFERENCES Events_Vendors(vid) ON DELETE CASCADE ) ENGINE=InnoDB; -- Create the Events_Countries table CREATE TABLE Events_Countries ( cid INT AUTO_INCREMENT PRIMARY KEY, Country VARCHAR(255) NOT NULL ) ENGINE=InnoDB; -- Create the Events_Countries_Tags table CREATE TABLE Events_Countries_Tags ( event_id INT NOT NULL, country_id INT NOT NULL, PRIMARY KEY (event_id, country_id), FOREIGN KEY (event_id) REFERENCES Events(id) ON DELETE CASCADE, FOREIGN KEY (country_id) REFERENCES Events_Countries(cid) ON DELETE CASCADE ) ENGINE=InnoDB; -- Insert sample events INSERT INTO Events (Author, Title, Description, Start, End, URL, Location, Location_URL, `Virtual`, Multiple_dates, Recurring, On_Demand) VALUES (1, 'Event 1', 'Description for Event 1', '2024-01-01 10:00:00', '2024-01-01 12:00:00', 'https://event1.com', 'Location 1', 'https://location1.com', 1, 0, 0, 0), (2, 'Event 2', 'Description for Event 2', '2024-02-01 10:00:00', '2024-02-01 12:00:00', 'https://event2.com', 'Location 2', 'https://location2.com', 0, 0, 0, 0); -- Insert sample vendors INSERT INTO Events_Vendors (Vendor) VALUES ('Avaya'), ('Cisco'), ('Extreme'); -- Insert sample vendor-event relationships INSERT INTO Events_Vendors_Tags (event_id, vendor_id) VALUES (1, 1), (1, 2), (2, 3); -- Insert sample countries INSERT INTO Events_Countries (Country) VALUES ('Singapore'), ('UK'), ('Germany'); -- Insert sample country-event relationships INSERT INTO Events_Countries_Tags (event_id, country_id) VALUES (1, 1), (1, 2), (2, 3);
Copy Clear
Copy Format Clear
<?php use Carbon\Carbon; $jsonPayload = '{ "id": 1, "author": 456, "title": "Event Title", "desc": "Event Description", "start": "2024-01-01 10:00:00", "end": "2024-01-01 12:00:00", "url": "https://example.com", "loc": "Location Name", "locURL": "https://location.example.com", "virtual": 1, "multiple_dates": 0, "recurring": 0, "on_demand": 0, "vendorIDs": [1, 2, 3], "countryIDs": [5, 6, 7] }'; $input = json_decode($jsonPayload, true); if (!$input) { respond(null, ['code' => 400, 'message' => 'Invalid JSON payload']); } function respond($data, $error = null, $httpCode = 200) { http_response_code($httpCode); echo json_encode(['data' => $data, 'error' => $error]); exit; } function validateInput($input, $key, $required = true, $default = null) { printf('Checking',$input); if (!isset($input[$key])) { if ($required) { respond(null, ['code' => 400, 'message' => "$key is missing"]); } return $default; } return htmlspecialchars(trim($input[$key])); } // Validate event data $id = validateInput($input, 'id', true); $Author = validateInput($input, 'author', true); $Title = validateInput($input, 'title', true); $Description = validateInput($input, 'desc', true); $Start = validateInput($input, 'start', true); $End = validateInput($input, 'end', true); $URL = validateInput($input, 'url', true); $Location = validateInput($input, 'loc', true); $Location_URL = validateInput($input, 'locURL', true); $Virtual = validateInput($input, 'virtual', true); $Multiple_dates = validateInput($input, 'multiple_dates', false, 0); $Recurring = validateInput($input, 'recurring', false, 0); $On_Demand = validateInput($input, 'on_demand', false, 0); // Validate relationships $vendorIDs = $input['vendorIDs'] ?? []; $countryIDs = $input['countryIDs'] ?? []; // Start transaction $mysqli->begin_transaction(); try { // Insert or update event if ($id == 0) { // Create a new event $stmt = $mysqli->prepare(" INSERT INTO `Events` (`Author`, `Title`, `Description`, `Start`, `End`, `URL`, `Location`, `Location_URL`, `Virtual`, `Multiple_dates`, `Recurring`, `On_Demand`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) "); $stmt->bind_param( "issssssssiii", $Author, $Title, $Description, $Start, $End, $URL, $Location, $Location_URL, $Virtual, $Multiple_dates, $Recurring, $On_Demand ); if (!$stmt->execute()) { throw new Exception($stmt->error, 500); } $id = $stmt->insert_id; $stmt->close(); } else { // Update an existing event $stmt = $mysqli->prepare(" UPDATE `Events` SET `Author` = ?, `Title` = ?, `Description` = ?, `Start` = ?, `End` = ?, `URL` = ?, `Location` = ?, `Location_URL` = ?, `Virtual` = ?, `Multiple_dates` = ?, `Recurring` = ?, `On_Demand` = ? WHERE `id` = ? "); $stmt->bind_param( "issssssssiiii", $Author, $Title, $Description, $Start, $End, $URL, $Location, $Location_URL, $Virtual, $Multiple_dates, $Recurring, $On_Demand, $id ); if (!$stmt->execute()) { throw new Exception($stmt->error, 500); } $stmt->close(); } // ------------------------------ // Handle vendor relationships $currentVendors = []; $result = $mysqli->query("SELECT vendor_id FROM Events_Vendors_Tags WHERE event_id = $id"); while ($row = $result->fetch_assoc()) { $currentVendors[] = $row['vendor_id']; } $vendorsToAdd = array_diff($vendorIDs, $currentVendors); $vendorsToRemove = array_diff($currentVendors, $vendorIDs); if (!empty($vendorsToAdd)) { $stmt = $mysqli->prepare("INSERT INTO Events_Vendors_Tags (event_id, vendor_id) VALUES (?, ?)"); foreach ($vendorsToAdd as $vendorID) { $stmt->bind_param("ii", $id, $vendorID); $stmt->execute(); } $stmt->close(); } if (!empty($vendorsToRemove)) { $stmt = $mysqli->prepare("DELETE FROM Events_Vendors_Tags WHERE event_id = ? AND vendor_id = ?"); foreach ($vendorsToRemove as $vendorID) { $stmt->bind_param("ii", $id, $vendorID); $stmt->execute(); } $stmt->close(); } // ------------------------------ // Handle country relationships $currentCountries = []; $result = $mysqli->query("SELECT country_id FROM Events_Countries_Tags WHERE event_id = $id"); while ($row = $result->fetch_assoc()) { $currentCountries[] = $row['country_id']; } $countriesToAdd = array_diff($countryIDs, $currentCountries); $countriesToRemove = array_diff($currentCountries, $countryIDs); if (!empty($countriesToAdd)) { $stmt = $mysqli->prepare("INSERT INTO Events_Countries_Tags (event_id, country_id) VALUES (?, ?)"); foreach ($countriesToAdd as $countryID) { $stmt->bind_param("ii", $id, $countryID); $stmt->execute(); } $stmt->close(); } if (!empty($countriesToRemove)) { $stmt = $mysqli->prepare("DELETE FROM Events_Countries_Tags WHERE event_id = ? AND country_id = ?"); foreach ($countriesToRemove as $countryID) { $stmt->bind_param("ii", $id, $countryID); $stmt->execute(); } $stmt->close(); } // Commit transaction $mysqli->commit(); respond(['id' => $id], null); } catch (Exception $e) { $mysqli->rollback(); respond(null, ['code' => $e->getCode() ?: 500, 'message' => $e->getMessage()], 500); } ?>
Show:  
Copy Clear
Like the site? Share it!
Have a questions? Ask in our telegram chat!
© 2024 PHPize - format run and share PHP code with SQL queries online | About project | Disclaimer
PHP versions: PHP 5.6, PHP 7.4, PHP 8.0, PHP 8.1, PHP 8.2.13
RDBMS versions: MariaDB 11.4, MariaDB 11.5, MariaDB 10, MariaDB 10 Sakila (ReadOnly), MySQL 5.7, MySQL 5.7 Sakila (ReadOnly), MySQL 8.0, MySQL 8.0 Sakila (ReadOnly), SQLite 3, SQLite 3 Preloaded, PostgreSQL 10 Bookings (ReadOnly), PostgreSQL 11, PostgreSQL 12, PostgreSQL 13, PostgreSQL 14, PostgreSQL 15, MS SQL Server 2017, MS SQL Server 2019, MS SQL Server 2022, MS SQL Server 2022 AdventureWorks (ReadOnly), Firebird 4.0, Firebird 4.0 (Employee), Oracle Database 19c (HR), Oracle Database 21c, Oracle Database 23c Free, SOQOL