CODEWITHSUNDEEP
phpmysqlcount
zorzihit
zorzihit

Reputation: 13

mysql query multiple tables and count row

I´m new to MySql and I´m having a problem ordering the result of a query.

I have 2 tables:

Table1 contains the users of my system 

userId  userName
01      Ken
02      John
03      Bob
04      Steve

Table2 contains the followers of each user 

userId, FollowerId.
02      01
01      02
02      03
02      04

so, in this case John has 3 followers: Ken, Bob and Steve.

I would like to generate a third table with 2 columns: users of the database and Number of followers per User. I would like to have the table ordered by Number of followers.

  userId NrFollowers
  02    3
  01    1
  03    0
  04    0

at the moment I´m using the following code

    $stmt = $conn->prepare("SELECT userId, userName FROM Table1");
    $stmt->execute();
    $stmt->store_result();
    $stmt->bind_result($userId, $userName);
        while($stmt->fetch()) {
                //count number followers
                $stmtfl = $conn->prepare("SELECT * FROM Table2 WHERE userId = (?)");
                $stmtfl->bind_param("s", $userId);
                $stmtfl->execute();
                $stmtfl->store_result();
                $stmtfl->fetch();
                $followCount= $stmtfl->num_rows;

                //now I have $userName and $NrFollowers

This solutions is not optimised as required an loop for each databases`s user and does not allow to order the users by number of followers.

Can someone please help me to write the proper Mysql query?

Upvotes: 1

Views: 113

Answers (3)

Blank
Blank

Reputation: 12378

For sql query, you can try this:

select
    tbl1.userId,
    count(distinct tbl2.FollowerId) as NrFollowers
from tbl1
left join tbl2
on tbl1.userId = tbl2.userId
group by tbl1.userId
order by count(distinct tbl2.FollowerId) desc

See demo here.

Upvotes: 1

sandeep rawat
sandeep rawat

Reputation: 4957

use below code.. 

SELECT u.usersId, coalesce(COUNT(f.FollowerId) ,0)AS NrFollowers
FROM users u
LEFT JOIN followers f ON f.userId = u.userId
GROUP BY u.userId
order by coalesce(COUNT(f.FollowerId) ,0) desc

Upvotes: 1

Naincy
Naincy

Reputation: 2943

SELECT u.usersId, COUNT(f.FollowerId) AS NrFollowers
FROM users u
LEFT JOIN followers f ON f.userId = u.userId
GROUP BY u.userId

Upvotes: 1

Related Questions