How to show/fetch amount of posts, comments etc a user has made

I want to show the users the amount of posts, comments etc they have created in their profile section, just like any other forums that exist. I am doing this with php and MYSQLI. Post table:

1   post_id Primary int(11)     AUTO_INCREMENT  

    2   title   varchar(255)

    3   users_id    int(11)     

    4   content   varchar(500)      
    5   type    int(11)                     
    6   imagepath   varchar(50) 
    7   date_created    datetime

I have tried to add another column to the post table and increment its value by 1 each time the user makes a post in php by an INSERT statement but it’s value only stays at 1, even though the user continues to create more posts. Here is what I tried:

function createPost($conn, $content, $title, $users_id, $date_created, $type, $total_post){
    $sql = "INSERT INTO post (title, users_id, content, date_created, type, total_post) VALUES (?,?,?,?,?,?);";

    $stmt = mysqli_stmt_init($conn);
 
    if (!mysqli_stmt_prepare($stmt, $sql)){
     header("location: ../home.php?error=stmtfailed");
     exit();
    }

    $mysqltime = date ('Y-m-d H:i:s');
    $total_post++;
    $type;


    mysqli_stmt_bind_param($stmt, "ssssss", $title, $users_id, $content, $mysqltime, $type, $total_post);
    mysqli_stmt_execute($stmt);
    mysqli_stmt_close($stmt);
    header("location: ../home.php?error=noerroronpost");
     exit();
 }

This is in profile.php where im trying to display the information to the user

      $id = $_SESSION["userid"];
        
        $stmt = $conn->prepare('SELECT * from post LEFT JOIN users on users.users_id = ? order by post_id DESC;');
        $stmt->bind_param('s', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        while($row = $result->fetch_assoc()){

        echo "<div class='userinfo'>";
        echo "<h5 id='usernameprofile'>" ."Username: " .$row["users_username"] ."</h5>";
        echo "<h5 id='usernameprofile'>" ."Registration date: " .$row["create_datetime"] ."</h5>";
        echo "<h5 id='usernameprofile'>" ."Posts: " .$row["post_id"] ."</h5>"; echo "<br>";
        echo "</div>";
        }

        $stmt->close();

EDITED: Thanks to ADyson the query he provided worked perfectly. I tweaked it a bit to just show the amount of posts.

        $id = $_SESSION["userid"];   
        mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
        $stmt = $conn->prepare('SELECT COUNT(p.post_id) as total_posts from post p INNER JOIN users ON users.users_id = p.users_id WHERE p.users_id = ?;');
        $stmt->bind_param('s', $id);
        $stmt->execute();
        $result = $stmt->get_result();
        while($row = $result->fetch_assoc()){
          $count = $row['total_posts'];

        echo "<div class='usertotalpost'>";
        echo "<h5 id='totalposts'>" ."Posts: " .$count ."</h5>"; echo "<br>";
        echo "</div>";
        }

        $stmt->close();

Answer

You’ll still need to count and group if you want to see totals per user. And your join style is all wrong too – you’re supposed to link columns in each table together to make the join, not just restrict on an input field.

Try it like this:

SELECT
  u.users_username, 
  u.create_datetime, 
  COUNT(p.post_id) AS total_posts
FROM 
  post p
  INNER JOIN users 
    ON users.users_id = p.users_id
WHERE 
  p.users_id = ?
GROUP BY 
  u.users_username, 
  u.create_datetime

(Obviously the WHERE clause is optional – if you want to a see a list of totals for all users then remove it.)

Advertisement

Answer