list entries by surname only if more than one exists in DB

[jasonc]

i wish to list entries where one of the fields is repeated elsewhere in the DB

 

say i want to list all the surnames of people with the name 'smith' but only if their is more than one all the single 'smiths' are not used for this.

 

the field is as you would expect,  'surname'

 

what query should i use to obtain these records?

 

 

[divadiva]

so you want to search ?right?

 

Try

select * fom table

where name regexp =

 

(check with the synatx for regular expression)

 

 

[Mchl]

SELECT surname, COUNT(*) AS count FROM table GROUP BY surname HAVING count > 1

[jasonc]

i use this to display the results but only get one from each 'group' not all in each group where there are doubles or repeats entries of the same surname.

 

i also used phpmyadmin and used the query there and got the same results, just a few of them when they is like 4 or 5  in most of the groups of 'surnames' (l_name)

 

for ($i = 0; $i < mysql_num_rows($members); $i++) {
?><br>f_name: <?=mysql_result($members, $i, "f_name");?> l_name: <?=mysql_result($members, $i, "l_name");?>
<? } ?>

[Mchl]

So you want to have all duplicates listed?

 

SELECT ID, surname FROM table WHERE surname IN (SELECT surname FROM table GROUP BY surname HAVING COUNT(*) > 1 ) ORDER BY surname

[jasonc]

ok that worked

 

thanks

 

i wish to display then as follows

 

to the left is the surname and to the right is the first names.

 

surname1

                  firstname1

                  firstname2

                  firstname3

---------------------------

surname2

                  firstname1

                  firstname2

---------------------------

 

 

and so on...

 

 

but my code does not work  please help

 

<?

$members = mysql_query("SELECT * FROM `table` WHERE `l_name` IN (SELECT `l_name` FROM `table` GROUP BY `l_name` HAVING COUNT(*) > 1 ) ORDER BY `l_name`") or die(mysql_error());

for ($i = 0; $i < mysql_num_rows($members ); $i++) {

?><div align="left">Surname: <?=mysql_result($members , $i, "theip");?></div><br><?

while (mysql_result($members , $i, "l_name") != mysql_result($members , $i + 1, "l_name")) { ?>

<div =align="right"><?=mysql_result($members , $i, "f_name");?></div><?

$i++;

} ?><hr>

<?

$i--;

} ?>

[fenway]

Display is not a mysql issue.