How can I create a communities feature where certain posts go to specific communities

Question

I can create a post and it is displayed in a certain area of the website. However, I want to not make all of the posts appear in one area but instead add a feature called communities where you create a community and then you can create posts in that specific community. Below is my code that has a working create a post feature and displaying the post. I have provided some images below to show what my site looks like. Thank you for the help in advance.

Show Post:

<?php

require 'header.php';
require 'includes/db_conn.php';






if (!isset($_SESSION['uid'])) {
    header("Location: ./index.php?error=please_log_in");
    exit();
} 

$postId = $_GET['postId'];
$sql = "UPDATE posts SET views=views+1 WHERE post_id=".$postId.";";
$stmt = mysqli_stmt_init($conn);
mysqli_stmt_prepare($stmt, $sql);
mysqli_stmt_execute($stmt);

$sql = "SELECT posts.post_id, posts.title, posts.content, posts.video, posts.datePosted, users.username FROM posts INNER JOIN users ON posts.uid=users.uid WHERE ".$postId."=posts.post_id;";
$stmt = mysqli_stmt_init($conn);

if(!mysqli_stmt_prepare($stmt, $sql)) {
    header("Location: ../forum.php?error=sqlerror");
    exit();
} else {
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);

    if ($row = mysqli_fetch_assoc($result)) {
        echo "<table class='table'>";
        echo    "<tr><th>Title</th><th>Posted By</th><th>Date Posted</th></tr>";
        echo    "<tr><td>".$row['title']."</td><td>".$row['username']."</td><td>".$row['datePosted']."</td></tr>";
        echo "</table>";

        echo "<table class='table'>";
        echo    "<tr><th>Content</th></tr>";
        echo    "<tr><td>".$row['content']."</td></tr>";
        echo "</table>";

        echo "<table class='table'>";
     
        echo "<div class='vidd'>";
        $text = preg_replace("#.*youtube\.com/watch\?v=#" , "", $row['video']);
        echo "<div class='vidd'>";
        $text = '<iframe width="1280" height="800" id="vidd" src="https://www.youtube.com/embed/'.$text.'" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>';
        echo "</div>";
        echo $text;
    
        echo "</table>";

        

        echo "<form action='includes/replyhandler.php?postId=".$postId."' method='post'>";
        echo    "<div class='form-group text-center'>";
        echo        "<label>Post a Comment!</label><br />";
        echo        "<textarea cols='80' rows='5' id='comment' name='comment'></textarea>";                 
        echo    "</div>";
        echo    "<div class='form-group text-center'>";
        echo        "<button class='btn btn-primary' type='submit' name='submit-reply'> Add Reply</button>";
        echo    "</div>";
        echo "</form>";
        
        $sql = "SELECT replies.comment, replies.datePosted, users.username FROM replies INNER JOIN users ON replies.uid=users.uid WHERE (".$postId." = replies.post_id)  ORDER BY reply_id DESC;";
        $stmt = mysqli_stmt_init($conn);

        if(!mysqli_stmt_prepare($stmt, $sql)) {
            header("Location: ../forum.php?error=sqlerror");
            exit();
        } else {
            mysqli_stmt_execute($stmt);
            $result = mysqli_stmt_get_result($stmt);
            echo "<table class='table'>";
            echo    "<tr><th>Comment</th><th>Posted By</th><th>Date Posted</th></tr>";

            while($row = mysqli_fetch_assoc($result))
            {
                echo "<tr><td>".$row['comment']."</td><td>".$row['username']."</td><td>".$row['datePosted']."</td></tr>";
            }
            echo "</table>";
        }
    } else {
        echo "<p> No Content</p>";
    }
}

require 'footer.php';
?>

<link rel="stylesheet" href="./video.css">

Create post:

<?php 
require 'header.php';
if (!isset($_SESSION['uid'])) {
    header("Location: ./index.php?error=please_log_in");
    exit();
} 
if (isset($_POST['submit-post'])) {
    require 'includes/db_conn.php';

    $title = $_POST['title'];
    $content = $_POST['content'];
    $video = $_POST['video'];
    $uid = $_SESSION['uid'];
    $view = 1;
    if (empty($title) || empty($content)) {
        header("Location: ../newpost.php?error=empyfield");
        exit();
    } else {
        echo ".$title $content.";

        $sql = "INSERT INTO posts (uid, title, content, video, datePosted, views) VALUES (?, ?, ?, ?, now(), ?);";
        $stmt = mysqli_stmt_init($conn);    

        if (!mysqli_stmt_prepare($stmt, $sql)) {
            header("Location: ../newpost.php?error=sqlerror=".mysqli_error($conn)."&uid=".$uid."&title=".$title);
            exit();
        } else {
            mysqli_stmt_bind_param($stmt, "issss", $uid, $title, $content, $video, $view);
            mysqli_stmt_execute($stmt);

      

            header("Location: ../forum.php?post=successful"); 
            exit();
        }
    }
    mysqli_stmt_close($stmt);
    mysqli_close($conn);
}
?>
<link rel="stylesheet" href="./style.css">
<form action="<?php echo htmlentities($_SERVER['PHP_SELF']); ?>" method="post">
    <div class='form-group row'>
        <div class="col-md-6 offset-md-3">
            <h1 id="createpost">Create a Post</h1>
            <label>Title</label><br />
            <input type="text" name="title" placeholder="Title"> <br />
            
            <label>Video Link</label>
            <br>
            <input type='url' id='vid' name='video' placeholder="Video Link"/>
            <br>
            </form>
            <label>Description</label><br />
            <textarea cols='97' rows='10' name='content'></textarea>
            <button id="btnn" type="submit" name="submit-post">Post</button>
        </div>
    </div>
</form>


<?php
require 'footer.php';
?>

create post

show post

Answer 1

Hmm, tough school... So, let's begin with setting up your database, and let's call it my_community. Log into mysql and create the database.

CREATE DATABASE my_community;

You will need a users table with a few users, so, let's create that..

CREATE TABLE users (
    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL
);
INSERT INTO users(name) VALUES ('kev');
INSERT INTO users(name) VALUES ('susan');

You will also need a table to hold your communities, so

CREATE TABLE community (
    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name varchar(255) NOT NULL,
    description varchar(255)
);
INSERT INTO community(name, description) VALUES ('python', 'Happy Community');
INSERT INTO community(name, description) VALUES ('php', 'Not so Happy Community');
INSERT INTO community(name, description) VALUES ('perl', 'Miserable Community');

Also, a table to hold you posts.

CREATE TABLE post (
    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
    content TEXT NOT NULL,
    user_id int NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

And finally, a table to link posts to a community. In this instance we use a separate table, so that a post may be posted to multiple communities. Just like here on SO.

CREATE TABLE community_post (
    post_id int NOT NULL,
    community_id int NOT NULL,
    FOREIGN KEY (post_id) REFERENCES post(id),
    FOREIGN KEY (community_id) REFERENCES community(id),
    PRIMARY KEY (post_id, community_id)
);

If you bundle these four create table commands into a single my_db.sql file, you can import them easily using:

mysql my_community < db.sql -u my_username -p

Cool, now we have a database which we can POST to. I will leave it up to you to create a form to do this, but let's look at the PHP needed to store and retrieve the data.

When you form is posted to a PHP file, eg: post.php, you need to first connect to the database, and then INSERT the data.

<?php
// some data which would come from validated POST data
$user_id = 1; // kev 
$content = "How do I create a community?";
$community_id = 1;

$stmt = $dbh->prepare("INSERT INTO post (user_id, content) VALUES (:user_id, :content)");
$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt->bindValue(':content', $content, PDO::PARAM_STR);
$stmt->execute();
$post_id = $dbh->lastInsertId();

This will put the data into the post table which you created earlier, but then we need to link the post to a community, like this:

$stmt = $dbh->prepare("INSERT INTO community_post (post_id, community_id) VALUES (:post_id, :community_id)");
$stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT);
$stmt->bindParam(':community_id', $community_id, PDO::PARAM_INT);
$stmt->execute();

OK, so that will do a basic post.. but what if we wanted to post to both PHP and PERL communities. (I know, like, who would do that?).

The post part remains, basically, the same as before, but we will use susans id (2), and some new text.

// some data which would come from validated POST data
$user_id = 2; // susan
$content = "How do I create a NICE community?";
$community_id = 2; // second community

$stmt = $dbh->prepare("INSERT INTO post (user_id, content) VALUES (:user_id, :content)");
$stmt->bindParam(':user_id', $user_id, PDO::PARAM_INT);
$stmt->bindValue(':content', $content, PDO::PARAM_STR);
$stmt->execute();
$post_id = $dbh->lastInsertId();

One again, we need to link the post to a community, or, in this instance, to two communities.

stmt = $dbh->prepare("INSERT INTO community_post (post_id, community_id) VALUES (:post_id, :community_id)");
$stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT);
$stmt->bindParam(':community_id', $community_id, PDO::PARAM_INT);
$stmt->execute();


$community_id = 3;  // third community
$stmt = $dbh->prepare("INSERT INTO community_post (post_id, community_id) VALUES (:post_id, :community_id)");
$stmt->bindParam(':post_id', $post_id, PDO::PARAM_INT);
$stmt->bindParam(':community_id', $community_id, PDO::PARAM_INT);
$stmt->execute();

Now, we are ready to retrieve the data. First up, lets look getting the posts for the first community. Once again, we need to connect to the database, and then we can SELECT the data.

$dbh = new PDO("mysql:host=localhost;dbname=my_community", 'root', 'bigkev123');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// show results for php community
$community_id = 1;
$stmt = $dbh->prepare("SELECT p.*, c.name AS community_name FROM post p JOIN community_post cp ON p.id=cp.post_id JOIN community c ON cp.community_id=c.id WHERE cp.community_id = :community_id");
$stmt->bindParam(':community_id', $community_id, PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll();
foreach($results as $res)
{
        echo $res['community_name'].' - '.$res['content']."\n";
}

This will now display the text

python - How do I create a community?

Now, we can look at the perl community posts in the same manner

$community_id = 2;
$stmt = $dbh->prepare("SELECT p.*, c.name AS community_name FROM post p JOIN community_post cp ON p.id=cp.post_id JOIN community c ON cp.community_id=c.id WHERE cp.community_id = :community_id");
$stmt->bindParam(':community_id', $community_id, PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll();
foreach($results as $res)
{
        echo $res['community_name'].' - '.$res['content']."\n";
}

And again for the perl community

$community_id = 3;
$stmt = $dbh->prepare("SELECT p.*, c.name AS community_name FROM post p JOIN community_post cp ON p.id=cp.post_id JOIN community c ON cp.community_id=c.id WHERE cp.community_id = :community_id");
$stmt->bindParam(':community_id', $community_id, PDO::PARAM_INT);
$stmt->execute();
$results = $stmt->fetchAll();
foreach($results as $res)
{
        echo $res['community_name'].' - '.$res['content']."\n";
}

Notice that the post by susan displays in both the perl and php community. This is we linked the post to them both in the community_post table.

Happy Janmastami.