CODEWITHSUNDEEP
phpmysql
denny
denny

Reputation: 111

fetch data from mysql and replace duplicate entry

I have a table name test with columns id(primary key) and t1(varchar). t1 contains the email id which have multiple duplicate entries. So i want to select all data and replace the duplicate entry a blank row for example:

the table looks like

 id   |   t1
  1   |    [email protected]
  2   |    [email protected]
  3   |    [email protected]
  4   |    [email protected]
  5   |    [email protected]
  6   |    [email protected]

the result i want

 id   |   t1
  1   |    [email protected]
  2   |    [email protected]
  3   |    [email protected]
  4   |    DUPLICATE
  5   |    [email protected]
  6   |    DUPLICATE

FIDDLE LINK: http://sqlfiddle.com/#!9/e94620/2

OR

can i get this done using PHP

Upvotes: 0

Views: 204

Answers (2)

MrSmile
MrSmile

Reputation: 1233

or use SQL query:

select id, t_new as t1
from
(
  select id, t1,
      @num := if(@data = `t1`, @num + 1, 1) as `counter`,
      if(@num>1, 'DUPLICATE', `t1`) as t_new,
      @data := `t1` as `blabla`
  from
  `test`
  order by `t1`
) x
order by `id`

Sqlfiddle here

Upvotes: 0

anon
anon

Reputation:

So...

I had a look into this, and it's much easier in PHP, for example, I have the following script;

<table>
    <tr>
        <th>ID</th>
        <th>Email</th>
    </tr>

<?php

$data = [ // This will be your info from DB
    1 => '[email protected]',
    2 => '[email protected]',
    3 => '[email protected]',
    4 => '[email protected]',
    5 => '[email protected]',
    6 => '[email protected]'
];

$used_emails = [];

foreach ($data as $key => $part)
{
    print "<tr><td>" . $key . "</td><td>";
    if (!in_array($part, $used_emails))
    {
        print $part;
        $used_emails[] = $part;
    }
    else
    {
        print "duplicate";
    }
    print "<td></tr>";
}

?>

</table>

And this results in;

ID  Email
1   [email protected] 
2   [email protected] 
3   [email protected] 
4   duplicate   
5   [email protected] 
6   duplicate

This gets the results, and for each, checks to see if they've been used, if not, display and say it's used, otherwise, print "duplicate"

Here is my example working; https://tehplayground.com/BKTIXja1usyRxYM3

Upvotes: 2

Related Questions