Displaying info from more than one table
Having a nightmare trying to get some info to display correctly. I have 3 tables: users, accountants and clients. The common column in all of them is called JFBMicroID and contains a reference number in the format ABCD1234EFGH. It's basically a one to many to many relationship between the three tables: each user can have multiple accountants, and each accountant can have multiple clients.
I have a page set up in the users' members' area (created using SecurityAssist) where I want users to be able to view a list of all their accountants (identified by JFBMicroID), and also display the total number of clients that each accountant has, like this:
Firm Name Contact Name Email Address No. of Clients registered
Test Firm 1 Joe Bloggs test@firm1.com 4
Test Firm 2 John Doe test@firm2.com 8
I have a session variable on this page called JFBMicroID. The variable does work because elsewhere on the page I have a line that says:
Below is a list of all those who have registered so far using your Unique Registration Number: ABCD1234EFGH
However, the variable is not being picked up in the recordset. Instead it's displaying a list of all accountants regardless of their JFBMicroID. Actually, not all - 42 out of 115, not sure why! I'm also not sure how I would display the total number of clients next to each accountant.
The recordset currently looks like this:
$paramSession_rsAccountants = "-1";
if (isset($_SESSION['JFBMicroID'])) {
$paramSession_rsAccountants = $_SESSION['JFBMicroID'];
}
mysql_select_db($database_new_greenroom, $new_greenroom);
$query_rsAccountants = sprintf("SELECT * FROM accountants JOIN clients ON accountants.AccountantID = clients.AccountantID WHERE accountants.JFBMicroID = %s GROUP BY clients.AccountantID ORDER BY accountants.FirmName DESC", GetSQLValueString($paramSession_rsAccountants, "int"));
$rsAccountants = mysql_query($query_rsAccountants, $new_greenroom) or die(mysql_error());
$row_rsAccountants = mysql_fetch_assoc($rsAccountants);
$totalRows_rsAccountants = mysql_num_rows($rsAccountants);