Show sourcecode
The following files exists in this folder. Click to view.
webbsrvprg/projects/slutprojekt/tabeller/
skapa-tabeller.php
82 lines ASCII Windows (CRLF)
12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182
<?php
include('../../../dbconnection.php');
try {
#users
$sql = "CREATE TABLE users (
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
email VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
admin TINYINT(1) DEFAULT 0,
is_verified TINYINT(1) DEFAULT 0
)";
$dbconn->exec($sql);
echo "users Table created successfully<br>";
#categories
$sql = "CREATE TABLE categories (
category_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
)";
$dbconn->exec($sql);
echo "categories Table created successfully<br>";
#recipes
$sql = "CREATE TABLE recipes (
recipe_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
ingredients TEXT NOT NULL,
instructions TEXT NOT NULL,
image VARCHAR(255),
user_id INT UNSIGNED NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)";
$dbconn->exec($sql);
echo "recipes Table created successfully<br>";
#recepie_categories
$sql = "CREATE TABLE recepie_categories (
recipe_id INT UNSIGNED NOT NULL,
category_id INT UNSIGNED NOT NULL,
PRIMARY KEY (recipe_id, category_id),
FOREIGN KEY (recipe_id) REFERENCES recipes(recipe_id) ON DELETE CASCADE,
FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE CASCADE
)";
$dbconn->exec($sql);
echo "recepie_categories Table created successfully<br>";
#comments
$sql = "CREATE TABLE comments (
comment_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
recipe_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
comment TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (recipe_id) REFERENCES recipes(recipe_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)";
$dbconn->exec($sql);
echo "comments Table created successfully<br>";
#ratings
$sql = "CREATE TABLE ratings (
rating_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
recipe_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
rating TINYINT UNSIGNED NOT NULL,
FOREIGN KEY (recipe_id) REFERENCES recipes(recipe_id) ON DELETE CASCADE,
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
)";
$dbconn->exec($sql);
echo "ratings Table created successfully<br>";
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
}
$dbconn = null;
?>