Tagsystem mit PHP
von pathologicalplay am
Anbei ein einfaches Tagsystem, das ich mit PHP und MySQLi umgesetzt habe.
CREATE TABLE `posts` (
`post_id` int(10) NOT NULL AUTO_INCREMENT,
`post_title` varchar(150) NOT NULL,
`post` text NOT NULL,
PRIMARY KEY (`post_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
CREATE TABLE `tags` (
`tag_id` int(10) NOT NULL AUTO_INCREMENT,
`tag_name` varchar(150) NOT NULL,
PRIMARY KEY (`tag_id`),
UNIQUE KEY `tag_name` (`tag_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
CREATE TABLE `post_tags` (
`post_tags_id` int(10) NOT NULL AUTO_INCREMENT,
`post_id` int(10) NOT NULL,
`tag_id` int(10) NOT NULL,
PRIMARY KEY (`post_tags_id`),
KEY `post_id` (`post_id`),
KEY `tag_id` (`tag_id`),
CONSTRAINT `post_tags_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `posts` (`post_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `post_tags_ibfk_2` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`tag_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Ausgabe der Beiträge mit den jeweils dazugehörigen Tags.
$show_posts = $db->query("SELECT post_id, post_title, post FROM posts");
while ($content = $show_posts->fetch_object()) {
echo htmlentities($content->post_title);
echo htmlentities($content->post);
$post_id = htmlentities($content->post_id);
$show_tags = $db->prepare("SELECT tags.tag_name FROM tags
INNER JOIN post_tags ON post_tags.tag_id = tags.tag_id WHERE post_tags.post_id = ?");
$show_tags->bind_param('i', $post_id);
$show_tags->execute();
$show_tags->store_result();
$show_tags->bind_result($tag_name);
if ($show_tags->num_rows != 0) {
while ($show_tags->fetch()) {
echo '<a href="view_tag.php?tag='. htmlentities($tag_name).'">'. htmlentities($tag_name). '</a>';
}
}
}
Ausgabe der Beiträge, die einem Tag zugeordnet worden sind.
$tag_name = '';
if (isset($_GET['tag'])) {
$tag_name = $_GET['tag'];
}
$stmt = $db->prepare("SELECT posts.post_title, posts.post FROM posts
JOIN post_tags ON posts.post_id = post_tags.post_id JOIN tags ON post_tags.tag_id = tags.tag_id WHERE tags.tag_name = ?");
$stmt->bind_param('s', $tag_name);
$stmt->execute();
$stmt->bind_result($post_title, $post);
while ($stmt->fetch()) {
echo htmlentities($post_title);
echo htmlentities($post);
}
Speichern eines Beitrags mit der Möglichkeit, dem Beitrag mehrere Tags hinzuzufügen. Tags müssen bei der Eingabe durch Kommata getrennt sein.
if (isset($_POST['submit'])) {
$post_title = '';
if (isset($_POST['post_title'])) {
$post_title = trim($_POST['post_title']);
}
$post = '';
if (isset($_POST['post'])) {
$post = trim($_POST['post']);
}
$separate = explode (",", $_POST['tags']);
$tags = (array)$separate;
$save_post = $db->prepare("INSERT INTO posts(post_title, post) VALUES(?, ?)");
$save_post->bind_param('ss', $post_title, $post);
if ($save_post->execute()) {
$post_id = $save_post->insert_id;
}
foreach ($tags as $tag) {
$save_tags = $db->prepare("INSERT INTO tags(tag_name) VALUES(?) ON DUPLICATE KEY UPDATE tag_id = tag_id");
$save_tags->bind_param('s', $tag);
$save_tags->execute();
$get_tag_ids = $db->prepare("SELECT tag_id FROM tags WHERE tag_name = ?");
$get_tag_ids->bind_param('s', $tag);
$get_tag_ids->execute();
$get_tag_ids->bind_result($tag_id);
$get_tag_ids->fetch();
$get_tag_ids->close();
$save_post_tags = $db->prepare("INSERT INTO post_tags(post_id, tag_id) VALUES(?, ?)");
$save_post_tags->bind_param('ii', $post_id, $tag_id);
$save_post_tags->execute();
}
}