loop through two sql queries php

Question

Hi I have two tables (users & friends) I need to connect to the friend table to bring all the friends for a user id, then connect to the user table and bring the information for that user, using the friend id. I have the code below but this only brings one user.

It needs to count the number of users online.

function online_friends()
{

$ID = ($_SESSION['ID']);
$friend = "SELECT * FROM `friends` WHERE `user_id` = '$ID' AND `accepted` = 'TRUE'";
$result=mysql_query($friend);

    while ($row = mysql_fetch_assoc($result)) 
    {
    $friend_id = $row['friend_id'];
    $get = "SELECT * FROM `users` WHERE `ID` = '$friend_id' AND `online` = 'TRUE'";
    $result_friend=mysql_query($get);
    $count_friends=mysql_num_rows($result_friend);
    }
    return $count_friends;

}

Thanks Edit: I have checked these manually by running through phpmyadmin and it brings two or more rows.

Answer 1

first off, you should really be using prepared queries instead of building the query string manually.

secondly, the query you want looks like:

Select count(*) from `friends` join `users` on `user_id`=`users`.`id` where `users`.`id`=? and `accepted`=`TRUE` and `online`='TRUE'