How to remove double row

[cruelity93]

For example I got like 10000 rows.

It looks like this
URL STATUS LASTCHECKED HASH HOST
noob.com up today 1111 ns1.noob.com
noob.com up today 1111 ns1.noob.com
awfw.com up today 1111 ns1.awfw.com
noob.com up today 1111 ns1.noob.com
werwe.com up today 1111 ns1.werwe.com
noob.com up today 1111 ns1.noob.com
dsssa.com up today 1111 ns1.dssa.com

How would I remove all the double rows?

So if I wanted to keep only 1 noob.com, and delete all other noob.coms

[adityamenon90]

//query the database
$query = mysql_fetch_array("YOUR QUERY HERE");

//store them all in an array
while($row = mysql_fetch_array($query))
{
    $duplicatedArray[] = $row;
}

//MOST IMPORTANT PART - RUN YOUR ARRAY THROUGH array_unique()
$uniqueArray = array_unique($duplicatedArray);

Check out Array_unique on the PHP spellbook:
PHP: array_unique - Manual

[cruelity93]

Could this be more noob friendly.

I've asked a couple days ago how to remove in row a kind of status.
So they've told me :
DELETE FROM shells WHERE status IN ('down' , '404')

Which is really noob friendly, its a command I run and its gone.

[guelphdad]

the most obvious thing you've missed is that you specified

1 noob.com, and delete all other noob.com

yet did not specify which one you needed to keep. In some cases it may matter and in others you may not care whether the row was first one entered, last one entered or any other.

adityamenon90

Sorry, way too complicated and it doesn't address the problem at the database level, the duplicate entries are still there.

One solution is to declare a unique key on the table which you can delete later.

ALTER IGNORE TABLE tablenamehere
ADD UNIQUE (onecolumn, anothercolumn, thirdifyouneeditcolumn)

that will add the unique key across one or several columns and discard any duplicates based on that unique key. you can then drop or change the unique key.