mysql count help Please

[semperfi]

Hi still learning php mysql etc and ran into a little issue and haven't been about to figure it out. trying to get/display the invite count for each email her is the php code

<?php
$i = 1;
while($data = mysql_fetch_array($user)){
$count = mysql_fetch_array(mysql_query("SELECT COUNT( * ) FROM `interested` WHERE `invites` = '".$invites['invites']."'"));
?>
<tr>
<td><?php echo $i; ?></td>
<td><?php echo $data['email']; ?></td>
<td><?php echo $count['COUNT( * )']; ?></td>
<td><?php echo $data['signup_time']; ?></td>
<td><?php echo $data['signup_date']; ?></td>
</tr>
<?php
$i++;
  }
?>

 

Everything displays email etc and the invite count displays the total of the highest email that has invited for example [email protected] has 3 invites and [email protected] has none and so on joe having the most invites at 3 the table displays 3 for all emails (hope that makes sense).

Any help is much appreciated also any better way to write/refactor the code.     

[The Little Guy]

I don't know what $user is, so maybe there is a better way, but based off what you posted

<?php
$i = 1;
while($data = mysql_fetch_array($user)){
$sql = mysql_query("SELECT COUNT(*) as total FROM `interested` WHERE `invites` = '".$invites['invites']."'")or die(mysql_error()); // remove "or die" after testing
$count = mysql_fetch_array($sql);
?>
<tr>
<td><?php echo $i; ?></td>
<td><?php echo $data['email']; ?></td>
<td><?php echo $count['total']; ?></td>
<td><?php echo $data['signup_time']; ?></td>
<td><?php echo $data['signup_date']; ?></td>
</tr>
<?php
$i++;
  }
?>

[semperfi]

I don't know what $user is, so maybe there is a better way, but based off what you posted

<?php
$i = 1;
while($data = mysql_fetch_array($user)){
$sql = mysql_query("SELECT COUNT(*) as total FROM `interested` WHERE `invites` = '".$invites['invites']."'")or die(mysql_error()); // remove "or die" after testing
$count = mysql_fetch_array($sql);
?>
<tr>
<td><?php echo $i; ?></td>
<td><?php echo $data['email']; ?></td>
<td><?php echo $count['total']; ?></td>
<td><?php echo $data['signup_time']; ?></td>
<td><?php echo $data['signup_date']; ?></td>
</tr>
<?php
$i++;
  }
?>

 

Thank you for your help tried the above and it didnt work. I also should have mentioned the $user. This is in the head of the page for db connection etc.

<?php

include "config_db.php"; 
$user = mysql_query("SELECT * FROM `interested`");
?>

 

Again thank you for your help in trying to get this working been at it for a few more hours and got close but not there yet any more help would be greatly appreciated.

[The Little Guy]

also, what is $invites?

[semperfi]

also, what is $invites?

 

Again sorry should show the db structure.

TABLE `interested` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `email` varchar(1000) NOT NULL,

  `hash` varchar( NOT NULL,

  `invites` int(20) NOT NULL,

  PRIMARY KEY (`id`),

  UNIQUE KEY `hash` (`hash`)

 

Thanks for any help.