0

I have a table like so: enter image description here And I would like to delete all duplicated values of 'Ognjen' and 'Nikola' and to save only those two, without their duplicates. I tried like so:

    public function findDuplicate($tabela){
        $query = "SELECT `user_facebook_id` FROM $tabela GROUP BY `user_facebook_id` HAVING count(*) > 1";
        $rez = $this->db->query($query, 3);
        if($rez){
            return $rez;
        }else{
            return false;
        }
    }
$duplicateResult=$settings->findDuplicate($tabela);
if($duplicateResult){
    echo '<div class="alert alert-warning">
  <strong>Warning!</strong> We can see that some of users you inserted already exists in database, so we are authorized to delete them.
</div>
';
    foreach($duplicateResult as $result){
        $to=$result['user_facebook_id'];
        $mysqli1 = new mysqli('localhost','servis_user','QrbRJQK7r52nFpx2','servis_racunara');
        $q="DELETE FROM $tabela WHERE `user_facebook_id`=$to";
        $rez=$mysqli->query($q);
        var_dump($rez);
    }
}

But all this did was to delete everything from my table. So there was no 'Ognjen' nor 'Nikola' anymore. Please help, I'm stuck with this.

Matthias
  • 12,873
  • 6
  • 42
  • 48
Ognj3n
  • 759
  • 7
  • 27

2 Answers2

0

Thanks to user187291; Reference: How to delete duplicates on a MySQL table?

"this removes duplicates in place, without making a new table

ALTER IGNORE TABLE foobar ADD UNIQUE (title, SID)

note: only works well if index fits in memory"

Community
  • 1
  • 1
Tommy
  • 377
  • 1
  • 9
  • 1
    Maybe you should have read some of the provisos on that answer you borrowed http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table#comment17099049_2630527 – RiggsFolly Apr 01 '16 at 09:15
0

Something like this should do it, let me know if it works for you

$result = mysqli_query($mysqli, "SELECT * from $tabela where user_facebook_id = '&to' and pol = '$pol' ");
$row_cnt = mysqli_num_rows($result);

if($row_cnt < 1) { 
    // do nothing 
} else {
    mysqli_query($mysqli, "DELETE FROM $tabela WHERE user_facebook_id = '$to' DESC LIMIT 1");
}

To explain this piece of code: It will search for user_facebook_id and pol ($pol you need to define yourself, not sure if you already have) if it finds more than one it will execute the last query that will delete the last result it finds. if it doesn't find more than 1 it will do nothing.

EDIT: I made a small change to the if($row_cnt < 1) it has to be < instead of >

Niels
  • 1,005
  • 1
  • 8
  • 18
  • No, it's not really what I'm looking for.. Had to remove DESC part(was the syntax error) and all it did was to remove only one row from the database. – Ognj3n Apr 01 '16 at 09:27
  • btw I use this method if a user logs into a dashboard of their website, because I work with unique sessions instead of a username. to make login easier. but since the user occasionally logs out and I don't want to have a database full with sessions. I check if the user has logged in before with the same IP. if so. I delete the old ones from the database. – Niels Apr 01 '16 at 09:27
  • try changing it to `DESC LIMIT 1` with capital letters. And do you know how many duplicated there are? – Niels Apr 01 '16 at 09:30
  • In fact, all I have to do was to make `user_facebook_id` to be unique row. Hilariously simple. Anyway, thanks for your effort. :) – Ognj3n Apr 01 '16 at 09:40