[SOLVED] Count(*)

[greens85]

Hi,

 

Im currently in the process of building a jobs board, I want to count the number of people that have signed up for alerts by email. I have tried the following code but it just keeps returning 3 when the actual number is 7.

 

<?php
$alertcount = 'SELECT COUNT(*) AS count FROM resumes WHERE alertstatus IS NOT NULL GROUP BY alertstatus';
$alertresult = mysql_query ($alertcount) or die (mysql_error());
$alertrows = mysql_num_rows ($alertresult);
$alertrow = mysql_fetch_row ($alertresult);
?>
(<?php print "$alertrows"; ?>)

 

Can anybody see why this might be returning 3 opposed to 7?

[Mchl]

Check what does the query return, when run from PHPmyAdmin, MySQL console etc...

[greens85]

count 

24

6

 

Thats what it returns, im very limited in my php/mysql knowledge but i would guess whats why it is returning 3.

 

Do you have any idea how i need to change the query so that it counts the number of 'yes' in the alertstatus field?

[sasa]

3 is number of result rows that query return

you have 3 diferent value in alertstatus field in database

try

<?php
$alertcount = 'SELECT COUNT(*) AS count, alertstatus FROM resumes WHERE alertstatus IS NOT NULL GROUP BY alertstatus';
$alertresult = mysql_query ($alertcount) or die (mysql_error());
$alertrows = mysql_num_rows ($alertresult);
while($alertrow = mysql_fetch_row ($alertresult)){
echo 'value: "', $alertrow['alertstatus'], '" is in database ', $alertrow['count'], " times.<br />\n";
}
?>

(not tested)

[greens85]

I've solved this now, incase anyone needs it for future reference the code is:

 

<?php    
$x2 = "SELECT resumeid, COUNT(*) FROM resumes WHERE alertstatus = 'yes' GROUP BY resumeid";
$result2 = mysql_query($x2) or die(mysql_error());
$total_rows2 = mysql_num_rows($result2); 
$row2 = mysql_fetch_row($result2);  
?>
(<font color="#FF0000"><?php print "$total_rows2"; ?></font>)