mysql query select count

I must use count and group and I'm a little confused.

I have 2 tables:

users

id_user | firstname | lastname | genre | logo_src

and

user_stalking
```
user_id | user_stalking_id
```

At the moment, my query looks like:

SELECT   us.user_id, us.user_stalking_id, us.notification_viewed, u.id_user,
         u.firstname, u.lastname, u.logo_src, u.genre, us.id, us.date
FROM     users u, user_stalking us
WHERE    us.user_id = ".$_SESSION['user']['id_user']."
     AND us.notification_viewed = 0
     AND us.user_stalking_id = u.id_user
ORDER BY us.date DESC

and it returns:

John | Jonson | image1.png | male

Now I want to receive count of all rows that contains them same ID in the user_stalking.user_id and user_stalking.user_stalking_id fields.

Solution

First off, I hope somewhere before this statement you are checking/sanatizing the $_SEESION['user']['id_user'] value or else you are leaving yourself wide open to injection attacks.

To answer your question, you can retrieve both those values by altering the query like so (may not be the most efficient query).

SELECT  us.user_id, us.user_stalking_id, us.notification_viewed, u.id_user, u.firstname, u.lastname, u.logo_src, u.genre, us.id, us.date, 
    (SELECT COUNT(s.user_id) FROM user_stalking s WHERE s.user_id = u.user_id) AS stalking_count, 
    (SELECT COUNT(s2.user_id) FROM user_stalking s2 WHERE s2.user_stalking_id = u.user_id) AS being_stalked_count 
FROM    users u, user_stalking us 
WHERE   us.user_id = ".$_SESSION['user']['id_user']." 
    AND us.notification_viewed = 0 
    AND us.user_stalking_id = u.id_user ORDER BY us.date DESC