Onlinesystem mit PHP
von pathologicalplay am
Mit dem PHP-Skript könnt ihr überprüfen, ob ein Nutzer online oder offline ist.
CREATE TABLE `users` (
`user_id` int(10) NOT NULL AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`user_password` char(255) NOT NULL,
`user_level` varchar(50) NOT NULL,
`last_activity` datetime NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `username_unique` (`username`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
Aktualisieren der DATETIME.
function update_last_activity($db) {
if (isset($_SESSION['logged_in']['user_level'])) {
if (isset($_SESSION['logged_in']['user_id'])) {
$user_id = (INT) $_SESSION['logged_in']['user_id'];
} else {
return false;
}
$update_last_activity = $db->prepare("UPDATE users SET last_activity = NOW() WHERE user_id = ? LIMIT 1");
$update_last_activity->bind_param('i', $user_id);
if ($update_last_activity->execute()) {
return true;
} else {
return false;
}
} else {
return false;
}
}
Ausgabe der Nutzer, die online oder offline sind.
$get_online_users = $db->query("SELECT user_id, username, last_activity
FROM users WHERE TIMESTAMPDIFF(MINUTE,last_activity,NOW()) <= 5 LIMIT 10");
while ($online_users = $get_online_users->fetch_object()) {
echo htmlentities($online_users->username);
}
$get_users = $db->query("SELECT user_id, username, last_activity,
TIMESTAMPDIFF(MINUTE,last_activity,NOW()) AS last_activity_minutes FROM users");
while ($users = $get_users->fetch_object()) {
if ($users->last_activity_minutes < 5) {
echo "online";
} else {
echo "offline";
}
}
Aufrufen der Funktion in anderen Dateien.
require_once 'includes/functions.php';
update_last_activity($db);
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();
}
}
