Member Avatar for 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

Edited by Simon180 because: n/a
  • 3 Contributors
  • 8 Replies
  • 127 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by Simon180

Dani AI

Generated

Short modern answer that ties the thread together and fills gaps from and :

Normalize, count, then prevent. Normalize incoming addresses (trim + lowercase) so "Foo@Example.com" and "foo@example.com" are treated the same. Use a server-side parameterized query at registration to get the current count (prevents SQL injection and race conditions). For full-table analysis use a grouping query that also returns the ids so you can review and safely dedupe before enforcing uniqueness.

Example: safe check at registration using PDO and normalization:

$pdo = new PDO($dsn, $user, $pass, $opts);
$email = strtolower(trim($_POST['email']));
$stmt = $pdo->prepare('SELECT COUNT(*) FROM users WHERE LOWER(TRIM(email)) = :email');
$stmt->execute([':email' => $email]);
$count = (int) $stmt->fetchColumn();

Find all duplicate emails and see which rows they map to:

SELECT e, COUNT(*) AS cnt, GROUP_CONCAT(id ORDER BY id) AS ids
FROM (SELECT id, LOWER(TRIM(email)) AS e FROM users) AS t
GROUP BY e
HAVING cnt > 1
ORDER BY cnt DESC;

To remove duplicates while keeping the lowest id (test on a copy first):

DELETE u FROM users u
JOIN (
  SELECT MIN(id) AS keep_id, LOWER(TRIM(email)) AS e
  FROM users
  GROUP BY e
  HAVING COUNT(*) > 1
) dup ON LOWER(TRIM(u.email)) = dup.e AND u.id <> dup.keep_id;

Operational tips: update email length (varchar(32) is too small—RFC guidance allows much longer addresses), normalize all rows before adding a UNIQUE index, and then add a unique constraint to prevent future duplicates. Use PDO or mysqli prepared statements instead of the old mysql_* API (see the PDO docs). For grouping/ids refer to MySQL . Always run destructive queries on a backup copy first.

Recommended Answers

All 8 Replies

Member Avatar for 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.

Member Avatar for Simon180

thanks ill give them a shot

Member Avatar for 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?

Member Avatar for 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 />";
  }
}
Edited by somedude3488 because: n/a
Member Avatar for ajbest

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

Member Avatar for 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 found 3 times so on

any ideas?

Member Avatar for 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;
	  }?>
Member Avatar for Simon180

thank you :)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.