CODEWITHSUNDEEP
phpmysql
Xaarth
Xaarth

Reputation: 1151

How to count messages for a user with php and mysql

I'm trying to count number of messages each user got. I have a users with user's detail id, name etc. I have created another table users_msgs where I have id, msg_id, user_id. I want to display count of messages each user got. what will be the best way to do it?

The web application have more than 2000 users. so the script have to select all of them and count their messages. I think this is not the best solution.

I thinking of count rows for 1 user from users_msgs table as count and then querying the users table for user's name with his id from users_msgs table.

I have tried selecting all users without any limit:

SELECT * FROM users

then iterating over the results like so:

<?php

  while ($user = mysqli_fetch_assoc($users)) {
    $count = count_user_msgs($user['id']);
    echo "{$user['name']} messages: $count";
  }

?>

The count_user_msgs function looks like this:

<?php

$sql = "SELECT COUNT(id) as msgs_count FROM users_msgs WHERE user_id = ?";
$stmt = mysqli_stmt_init($db);
if (mysqli_stmt_prepare($stmt, $sql)) {
  mysqli_stmt_bind_param($stmt, 's', $user_id);
  mysqli_stmt_execute($stmt);
  $result = mysqli_stmt_get_result($stmt);
  $count = mysqli_fetch_assoc($result)['msgs_count'];
  return $count;
}
return false;

?>

Upvotes: 1

Views: 479

Answers (3)

Tim Morton
Tim Morton

Reputation: 2644

FWIW, You can get it in a single query...

SELECT u.name, count(m.user_id) message_count
FROM users u
LEFT JOIN users_messages m ON u.user_id=m.user_id
GROUP BY m.user_id, u.name

Upvotes: 1

Felippe Duarte
Felippe Duarte

Reputation: 15131

You need to group by each user and get the count:

$sql = "SELECT user_id, name, count(user_id) as msgs_count from table group by (user_id, name)";

$result = $mysqli->query($query)

while ($user = mysqli_fetch_assoc($users)) {
    $user_id = $users['user_id'];
    $msgs_count= $users['msgs_count'];
    $count[$user_id] = $msgs_count;
    echo "{$user['name']} messages: $msgs_count";
}

Upvotes: 2

Lugh
Lugh

Reputation: 100

You want to count the number of msg_ids per user_id, so you'll want to GROUP BY user_id and count msg_id, like this:

$sql = "SELECT COUNT(msg_id) as msgs_count FROM user_msgs WHERE user_id = ? GROUP BY user_id";

The GROUP BY might not be necessary.

Upvotes: -1

Related Questions