updated -link between two tables to get username - mysql / php

Question

What do I want to do with this? Well, I have a school project, where we have to make a fully functional website, where people can view lyrics of songs, create an account, login in the account, comment on every lyrics and edit the lyrics.

So I have created a DB called klyrics Inside klyrics I have the following tables.

Table users

CREATE TABLE users (
userId int(8) PRIMARY KEY AUTO_INCREMENT,
username varchar(100),
email varchar(100),
password varchar(100));

Table lyrics

CREATE TABLE lyrics (
lyricsId int(8) PRIMARY KEY AUTO_INCREMENT,
artist varchar(100),
song varchar(100),
cover varchar(100),
lyrics varchar(9999),
chartId int(8),
commentId int(8),
FOREIGN KEY (chartId) REFERENCES charts(chartId));

Table charts

CREATE TABLE charts (
chartId int(8) PRIMARY KEY AUTO_INCREMENT,
cover varchar(100),
artist varchar(100),
song varchar(100));

and table comments

CREATE TABLE comments (
commentId int(8) PRIMARY KEY AUTO_INCREMENT,
userId int(8),
username varchar(100),
comment varchar(9999),
commentedon DATETIME DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (userId) REFERENCES users(userId));

---

Right now I want to create a comment system, so when I open for example the lyrics of Drake's song I can only comment on that song, and when I post the comment, I want to display the username of the guy who posted and the comment.

I followed this tutorial to create the login/register system

So what have I done so far? In my addcomment.php I have the following code:

<?php
include("connectdb.php");

if(isset($_POST) & !empty($_POST)){
    $comment = mysqli_real_escape_string($conn, $_POST['comment']);
    $username = mysqli_real_escape_string($conn, $_SESSION['username']);

    $isql = "INSERT INTO comments (comment, username) VALUES ('$comment', '$username')";
    $ires = mysqli_query($conn, $isql) or die(mysqli_error($conn));
    if($ires){
        $smsg = "Your Comment Submitted Successfully";
        header('location: index.php');
    }else{
        $fmsg = "Failed to Submit Your Comment";
    }

} ?>

Problem 1: When I submit my comment,the comment goes into the DB and it looks like this: There is blank spot under the username.

Problem 2: Comments are not displaying, this is my code to display the comments.

    <?php

$sql = "SELECT * FROM comments WHERE commentiD = $id";
$res = mysqli_query($conn, $sql);

while ( $r = mysqli_fetch_assoc($res)) {

?>
        <h1>By: <?php echo $r['username']; ?></h1>
        <h1>Comment: <?php echo $r['comment']; ?></h1>

<?php } ?>


<?php
}
?>

Small UPDATE for Problem 2 Like I said earlier, I have a few pages with ID, so when you click on View Lyrics you go to for ex to http://localhost/klyrics/lyrics.php?id=2and I only want to see the comments related to this lyrics.

Answer 1

Creating a comment system Like yours! you need to set username and userid to session on login.php.

And add an extra column into your table named postid.

And then insert into database like this:

$stmt = $pdo->prepare("INSERT INTO comments(postid, userid, username, comment)
                VALUES(:postid, :userid, :username, :comment)");
$stmt->bindParam(':postid', $post_id, PDO::PARAM_INT);
$stmt->bindParam(':userid', $_SESSION['userid'], PDO::PARAM_INT);
$stmt->bindParam(':username', $_SESSION['username'], PDO::PARAM_STR);
$stmt->bindParam(':comment', $comment, PDO::PARAM_STR);
$stmt->execute();

Attention commentid is autoincrement and TIMESTAMP 0 ON UPDATE CURRENT_TIMESTAMP no need to add date.

You can use left join or join to get info about post and users from another table etc... like following:

SELECT * FROM comments
        JOIN users ON comments.userid = users.userid
        AND postid = :postid 
        ORDER BY commentid DESC";

I do simple.

$post_id = '1';
$sql = "SELECT * FROM comments WHERE postid = :postid";
$stmt = $pdo->prepare($sql);
$stmt->bindValue(":postid", $post_id);
$stmt->execute();
$comment = $stmt->rowCount();
if($comment == 0){
    echo "be the first commenter";
}else{
    while($row = $stmt->fetch()){
        //display your codes here
    }
}

NOTE : I am using PDO prepared statements, you need to do in mysqli