how to fecth and count all the same emails in a db?

[Simon180]

abit like the title am looking for a way to scan my full database and to look at each accounts email address and to find any emails the same and then count them can anyone show me how this is done ? thanks

below is my basic db table

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=utf8

cheers

[ajbest]

abit like the title am looking for a way to scan my full database and to look at each accounts email address and to find any emails the same and then count them can anyone show me how this is done ? thanks

below is my basic db table

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password` varchar(50) NOT NULL,
  `email` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=89 DEFAULT CHARSET=utf8

cheers

if your looking for just the total number of duplicates you would do something like this

$emails=array();
$result=mysql_query("SELECT * FROM users");
while($entry=mysql_fetch_assoc($result)){
   if (in_array($entry['email'], $emails)){$count+=1;}
   else {$emails[]=$entry['email'];}
}
echo $count;

if you are looking for the total number of occurrences for each e-mail address you could use this

$emails=array();
$result=mysql_query("SELECT * FROM users");
while($entry=mysql_fetch_assoc($result)){
   $emails[$entry['email']]+=1;
}
foreach($emails as $email => $count){
   echo $email . " = " . $count . "<br>";
}

note that there may be a quicker way to do this via a mysql query using the group by function by I am not sure on that.

[Simon180]

thanks ill give them a shot

[Simon180]

ok is there away way of turing this into a function were i can check it with a email being sent from a form?

[somedude3488]

I would do this:

$query = mysql_query('SELECT `email`,COUNT(`email`) AS `count` FROM `table` GROUP BY `email`');
if ( mysql_num_rows( $query ) > 0 ) {
  while( list( $email,$count ) = mysql_fetch_row( $query ) ) {
    echo "{$email} has {$count} occurrences<br />";
  }
}

[ajbest]

yes, Which one did you use and what piece of data were you after? do you just want the duplicates identified?

[Simon180]

none dont think there what am after basicly I need sumway of checking a email being send from a form as a register, and to see how many times that one email as been used as a count like [e-mail] found 3 times so on

any ideas?

[ajbest]

I think this is what your looking for. This function will take in the e-mail address and return the number of times it appears in the users database.

<?
      function get_send_count($email){
			$count=mysql_num_rows(mysql_query("SELECT email FROM users WHERE email='".$email."'")); 
			return $count;
	  }?>

[Simon180]

thank you :)