commenting system

[AjACK]

I am working on a commenting system for my website I am having some issues. As of right now user can comment and "reply" but the issue is coming in with the replies. I can't get the post_id to associate with the reply table of my comment. 

There is a table called `posts` has 
post_id-id of the post
id-id of the user who made the post
created_on-time stamp of post creation
content-text of the post or images, videos etc. 

There is a table called `replies` has
reply_id-id of the reply
id-id of user who made the reply
created_on-time stamp of reply
content-text of the reply 
post_id-this is the post that the user is replying to this is where we are running into issues. 

Here is the code that I am working on. PHP using MySQLi prepared statements. 


this is the comment.php file 

<?php

require_once "config/config.php";
include "functions.php";

db_connect();
check_auth();

$sql = "INSERT INTO replies (id, post_id, content) VALUES(?,?,?)";
$statement = $conn->prepare($sql);
$statement->bind_param('isi', $_SESSION['id'], $_POST['content'], $_GET['post_id']);

if($statement->execute())
{
    redirect_to("dashboard.php");
}else{
    redirect_to("dashboard.php?error=true");    
}

this is the section where the user can trigger the reply code
 

$result = get_posts($conn);

if($result->num_rows > 0 )
{
   
    while($post = $result->fetch_assoc())
    {

                      $sql = "SELECT id, first_name, last_name, pro_img FROM accounts WHERE id = ? LIMIT 1";
                      $statement = $conn->prepare($sql);
                      $statement->bind_param('i', $post['id']);
                      $statement->execute();
                      $statement->store_result();
                      $statement->bind_result($id, $first_name, $last_name, $pro_img);
                      $statement->fetch();
                    ?>
                    
           <!--user posts-->
           <div class="user_posts">
            <label><?php echo '<img src="data:pro_img/jpg;base64,'.base64_encode($pro_img).'" height="50" width="50"/>';?><?php echo ($row['first_name']), ($row['last_name']);?></label>
            <p><?php echo $post['content'];?></p>
            <div class="likes">
            <button class="like"><i class="material-icons like">favorite</i></button>
            <button class="like"><i class="material-icons dislike">heart_broken</i></button>
            </div>
            <form action="comment.php?post_id=<?php $_GET['post_id'];?>" method="post">
            <input type="text" name="content" id="content" required>
            <input type="submit" name="comment" value="comment">
            </form>
            
           </div>

As you can see I am attempting to use $_GET to get the post_id to associate the reply with the comment but the post_id somehow always being associated with the "id" of the user instead of the id of the post we are trying to reply to.


here is the code to get the posts to show on the screen. 

 

function get_posts($conn)
{
$sql = "SELECT * FROM posts ORDER BY posted_on DESC";;
$statement = $conn->prepare($sql);
$statement->execute();
return $statement->get_result();

}

Any kind of help would be much appreciated thank you in advanced. 

[maxxd]

41 minutes ago, AjACK said:

As you can see I am attempting to use $_GET to get the post_id to associate the reply with the comment but the post_id somehow always being associated with the "id" of the user instead of the id of the post we are trying to reply to.

In that case, the problem is happening where $_GET is being assigned, not where it's being read. $_GET['post_id'] could be literally anything, it all depends on what the system assigns to that variable. Show us the code that builds the link that takes you to the reply page.

From a different tact - your column names are confusing and misguided. `replies.reply_id` and `posts.post_id` should both simply be called `id`, and in both tables `id` should be `user_id`. That way all the fields more clearly and correctly reflect the data they concern. I have a sneaky suspicion this is actually the source of your problem, but won't know until we see the code requested above.

[AjACK]

3 hours ago, AjACK said:

            <form action="comment.php?post_id=<?php $_GET['post_id'];?>" method="post">

this is where the link is being generated for the "comment" I'm sure this is where the issue is coming into play. Been a long time since I've used php learned it around 1998 eek I feel old an PHP has changed quite a bit since then. But alas I'm not above learning and honing my skills. 

As far as database column names it's the easiest way for me to know what each column means. 

I know 

ID = user id
post_id = the ID of the post 
reply_id = the ID of the reply 
 

so on so forth. My brain wouldn't be able to handle if I had tables with the name ID in them but unsure which ID it was representive of how would I know that ID was for post_id or for comment_id etc etc. Unless I am misunderstanding you lol it's possible.

[Barand]

<form action="comment.php?post_id=<?php $_GET['post_id'];?>" method="post">

That code will generate a link with no id value, like this...

<form action="comment.php?post_id=" method="post">

It needs to be either

<form action="comment.php?post_id=<?php echo $_GET['post_id'];?>" method="post">

or

<form action="comment.php?post_id=<?= $_GET['post_id'] ?>" method="post">

 

[maxxd]

The variable names only matter to the table itself. If you open your replies table in MySQL Workbench, you'll see `id` and `reply_id`. There's no indication that `id` is actually a foreign key that contains a user id.

That having been said, in order to keep things sane outside of your database schema, rename the columns in the SQL or php. For instance, something along these lines:

SELECT	 posts.id AS post_id
	,replies.id AS reply_id
	,posts.user_id AS post_user_id
	,replies.user_id AS reply_user_id
	,replies.content,
	,post_user.first_name AS post_user_first_name,
	,reply_user.first_name AS reply_user_first_name
FROM posts
LEFT JOIN replies
	ON replies.post_id = posts.id
LEFT JOIN users AS post_user
	ON posts.user_id = users.id
LEFT JOIN users AS reply_user
	ON replies.user_id = users.id;

Then in the returned record you'd use $return['post_id'], $return['reply_id'], $return['post_user_id'], $return['reply_user_id'], etc. So behind the scenes the data labeling makes sense according to the actual data when scoped to the datatype it describes, and in php the data labeling makes sense when scoped to its dataset.

I hope that makes sense.