Showing Number Of Members Associated With Certain Table

[tomyknoker]

I have the following MySQL query '$info = mysql_query("SELECT * FROM tblrep");' which displays my 'tblrep' data in a table, in this table there is a column 'rep_NBR'. This column gets associated with sub members in my database, these submembers are in a table called 'tblmem', When I am showing the data for 'tblrep' I also want to add into the table the number of 'tblmem' associated with each 'tblrep' using the 'rep_NBR', does anyone have any ideas on how to do this?

[tomyknoker]

I tried but can't seem to make it work! Any ideas would be great!



<?php

/* connect to the mysql database and use different queries for the count of members */

include 'config.php';
include 'open.php';

//navigation
include("nav.php");

$info = mysql_query("SELECT *, (select count(*) from tblmembers where tblmembers.ID = tblrepresentatives.rep_NBR) as total_members FROM tblrepresentatives");

echo '<table border="1" cellpadding="3" cellspacing="1">
<tr valign="top">
<td>First Name</td>
<td>Last Name</td>
<td>Members</td>
<td>Edit</td>
</tr>';

$result = mysql_query($query);
//now check it for failure and display a decent error message if it did fail
if (!$result) {
die("Query failed. Query text: $query<br />Error Message: ".mysql_error());
}

else {
while ($qry = mysql_fetch_array($info)) {


//create the layout
?>
<link href="cs_style.css" rel="stylesheet" type="text/css" />

<tr valign="top">
<td><?php echo $qry['rep_Firstname']; ?></td>
<td><?php echo $qry['rep_Lastname']; ?></td>
<td><?php echo $qty['total_members']; ?></td>
<td><a href="showAdminEdit.php?record_id=<?php echo $qry['id']; ?>">Edit</a></td>
<td>Delete</td>
</tr>
<?php
}
}

echo '</table>';

include 'library/closedb.php';

?>