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

So, where are you stuck exactly? We're not really going to code a whole new feature for you. You need to start by linking each post to a specific community when you save it to the database. For that to work, you'll need to add something to the form so the user can choose which community to post it in — ADyson, Aug 30 '21 at 06:34
I more stuck on the implementation aspect. I assume first thing is to have a feature that creates a community similar to how a post is created. That database will then display the name and description of the community. Then when you click on the the community, it will give you a page where posts will be. I just don't know where to start when it comes to linking the post to that specific community. Not looking for someone to do it for me but I just need some other perspective. — knickatnite1234, Aug 30 '21 at 07:21
`don't know where to start when it comes to linking the post to that specific community.`... via a foreign key relationship in the database — ADyson, Aug 30 '21 at 07:37
**Warning:** You are wide open to [SQL Injections](https://php.net/manual/en/security.database.sql-injection.php) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 30 '21 at 09:45
You need to stop manually checking for errors. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) and [Should I manually check for errors when calling “mysqli_stmt_prepare”?](https://stackoverflow.com/q/62216426/1839439) — Dharman, Aug 30 '21 at 09:45

score 1 · Answer 1 · answered Aug 30 '21 at 11:02

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.

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

1 Answers1