SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to remove double row

    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

  2. #2
    SitePoint Enthusiast adityamenon90's Avatar
    Join Date
    Apr 2010
    Posts
    36
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    //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

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2010
    Posts
    110
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Code:
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •