php/mysql count issue

[changeco]

I wrote this piece of code to pull out the count of users(rtnUser) subscribed to a class(CDID)...

However, I get a count of '0'... I'm not sure if I have the variables reading correctly... I second set of eyes would be great...

<?php
$cdid = $row_rtnUser['CDID'];

$sql = "SELECT COUNT(CDID) AS total FROM rtnUser GROUP BY CDID"; 
$query = mysql_query($sql);
$recordset = mysql_fetch_assoc($query);
$count = $recordset["total"];

echo "$count";
?>

Ted

[nonshatter]

you could create a loop and counter to count the number of users...

$sql = "SELECT COUNT(CDID) AS total FROM rtnUser GROUP BY CDID"; 
$query = mysql_query($sql);

$count=0;
while ($recordset = mysql_fetch_assoc($query))
{
   $count++;
}
echo "$count";

[changeco]

I took your advice, used the following code

<?php
$cdid = $row_rtnUser['CDID'];
$rtnUID = $row_rtnUser['rtnUID'];

$sql = "SELECT COUNT(CDID) AS total FROM rtnUser GROUP BY CDID"; 
$query = mysql_query($sql);

$count=0;
while ($recordset = mysql_fetch_assoc($query))
{
   $count++;
}
echo "$count";
?>

However I'm returning 4 for all of my 10 classes... Only the 1st one has anyone in it and the number 4 comes from the 4 total users...

Here's the code I'm currently using to return 4 users for all of the 10 classes... I'm not sure if 11 and 12 need to be there...

<?php 
do {  
?>
<option value="<?php echo $row_ClassDetails['CDID']; ?>" >
<?php
$mysqldate = $row_ClassDetails['CDDate']; 
$mydate = date("M, d Y",strtotime($mysqldate)); 
echo $mydate;
?> -
<?php echo $row_ClassDetails['CDName']; ?> - <?php
$cdid = $row_rtnUser['CDID'];
$rtnUID = $row_rtnUser['rtnUID'];

$sql = "SELECT COUNT(CDID) AS total FROM rtnUser GROUP BY CDID"; 
$query = mysql_query($sql);

$count=0;
while ($recordset = mysql_fetch_assoc($query))
{
   $count++;
}
echo "$count";
?> of <?php echo $row_ClassDetails['CDMaximum']; ?> max.
</option>
<?php
} while ($row_ClassDetails = mysql_fetch_assoc($ClassDetails));
?>

[hielo]

On your INITIAL post, try:

$sql = "SELECT CDID, COUNT(*) AS total FROM rtnUser GROUP BY CDID";

[changeco]

Here's what I did to fix the issue I was having...

thanks @hielo and @nonshatter for your help!

<select name="CDID">
<option value="" selected="selected">- - - Select a Class - - -</option>
<?php 
  do {  
?>
<?php 
  $cdid = $row_ClassDetails['CDID']; 
  $rtnUID = $row_rtnUser['rtnUID']; 

  mysql_select_db($database_rtn, $rtn); 
  $sql = "SELECT DISTINCT COUNT(CDID) AS total FROM rtnUser WHERE CDID = $cdid";  
  $query = mysql_query($sql, $rtn) or die(mysql_error()); 
  $row_query = mysql_fetch_assoc($query); 
?>
<?php if($row_query['total'] < $row_ClassDetails['CDMaximum']) { ?> 
<option value="<?php echo $row_ClassDetails['CDID']; ?>" >
<?php
  // make date normal month/dd/yyyy
  $mysqldate = $row_ClassDetails['CDDate']; 
  $mydate = date("M, d Y",strtotime($mysqldate)); 
  echo $mydate;
?> -
<?php echo $row_ClassDetails['CDName']; ?> - <?php echo $row_query['total']; ?> of <?php echo $row_ClassDetails['CDMaximum']; ?> max.
</option>
<?php } ?>
<?php
  } while ($row_ClassDetails = mysql_fetch_assoc($ClassDetails));
?>
</select>