Count occurances of data in one table which is the same as another table

[petrakid]

I have two tables.

 

Table 1, called tbl_maillist_lists, stores the mailing list information (ID, Listname, Desc, Status[Active,Deleted].

 

Table 2, called tbl_maillist_users, stores the users who may or may not be subscribed to any of the lists (ID, Name, Email, ListID[the ID of the List they are subscribed to], Status[Active,Pending,Deleted,A-Inactive].

 

What I want to do, if you haven't already guessed, is output the TOTAL number of subscribers for each list, whereas they are ACTIVE users.

 

Example:

Listname

Desc

Subscribers

ListA

Fun List!!!

76

 

ListA comes from the tbl_maillist_lists table, as does Desc, and Subscribers would be a count of all USERS who are subscribed to ListA, using the ID of the lists table and comparing it to the total count of all users with the same ListID in the users table. It's simple, but unfortunately, all I've been able to output is "Array"...Any help would be appreciated!

 

Current Code:

<?php
$result = mysql_query("SELECT * FROM tbl_maillist_lists ORDER BY ID")
or die(mysql_error());
$mylists = mysql_fetch_array( $result );
?>
<table width="100%" align="center" border="2">
<tr><td align="center" width="25%"><b>List Name</b></td><td align="center" width="60%"><b>Description</b></td>
<td align="center" width="15%"><b>Subscribers</b></td></tr>
<tr><td align="left"><?php echo $mylists['Listname']; ?></td><td align="left"><?php echo $mylists['Listdesc']; ?></td>
<?php
$ListsID = $mylists['ID'];
$sql = mysql_query("SELECT count(*) FROM tbl_maillist_users WHERE ListsID='$ListsID' AND Status='Active'")
or die(mysql_error());
$users = mysql_fetch_row($sql);
?>
<td align="center"><?php echo $users ?></td></tr>
?>

[fenway]

You mean $users[0].

[petrakid]

in both cases?  in the definition and the echo statement?  I guess I understand why this needs to be an array (silly me)...I'll have to look into it again when I get back to designing this mailing list.

[fenway]

In the echo.

 

Alternatively,

 

$users = mysql_result($sql, 0, 0);

 

Will work too.

[petrakid]

Here's how I did it, and I think this is necessary for how I am printing the various results:

 

<?php
//* Counts the rows and prints result *//
include ('+opendb.php');

$result = mysql_query("SELECT * FROM tbl_maillist_lists"); 
$num_rows = mysql_num_rows($result);
?>
<p>There <?php if ($num_rows == 1) { echo "is"; } else {echo "are"; } ?> currently <?php echo $num_rows ?> list<?php if ($num_rows > 1) { echo "s"; } ?> in the Mailing List database. Use the menu above to manipulate current lists, add lists, or delete lists.</p>
<?php
//* Lists the Mailing Lists *//
$listres = "SELECT * FROM tbl_maillist_lists ORDER BY ID";
$myres = mysql_query( $listres );
?>
<table width="100%" align="center" border="1">
<tr><td align="center" width="10%">ID</td><td align="center" width="20%"><b>List Name</b></td><td align="center" width="60%"><b>Description</b></td><td align="center" width="10%"><b>Subscribers</b></td></tr>
<?php
while($mylists = mysql_fetch_array($myres)){
?>
<tr><td align="center"><?php echo $mylists['ID'] ?></td><td align="left"><?php echo $mylists['Listname']; ?></td><td align="left"><?php echo $mylists['Listdesc']; ?></td>
<?php
//* Counts users subscribed to each list *//
$ListsID = $mylists['ID'];
$sql = mysql_query("SELECT * FROM tbl_maillist_users WHERE ListsID='$ListsID' AND Status='Active'");
$num_rows = mysql_num_rows($sql);
?>
<td align="center"><?php echo $num_rows ?></td></tr>
<?php }
?>

[fenway]

NO NO NO.... you're retrieving ALL the rows in each table just to get a simple count!

 

The answer was provided above.

[petrakid]

Look I'm not a PHP expert like ya'll , so I'm amazed that I came up with this.  so like when one is learning how to read, I need everything spelled out.  I looked at the examples given, but I wasn't exactly sure WHERE to put said examples.  I tried figuring it out, but...it never did work. 

 

So if there's a better way to do it, by all means, show me...but put a bit more code up.  Like if you could include the line of code before and the line after, I could get it from there.  I just don't know exactly where to put the "$users = mysql_result($sql, 0, 0); or the "$users[0]", and how to pull from it the count.

[fenway]

Like this:

 

$sql = mysql_query("SELECT count(*) FROM tbl_maillist_users WHERE ListsID='$ListsID' AND Status='Active'")
or die(mysql_error());
$users = mysql_result($sql, 0, 0);