SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Merge duplicates and add value to existing value

    Hi!
    I hope someone know how to do this:
    In my mysql database i have several posts that are duplicates ( only one field has a unique value )
    example:
    IDnr Name Place Fruit
    200 Tony Sweden Apple
    201 Jessica Sweden Orange
    200 Tony Sweden Orange
    200 Tony Sweden banana


    As above i want the data base to do this:
    IDnr Name Place Fruit
    200 Tony Sweden Apple,Orange,Banana
    201 Jessica Sweden Orange


    Hope you guys know what i mean

    if i need some script please guide me with how to do that in php

    Regards Tony

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    What you want is to normalize your database

    Code:
    Users table:
    
    IDnr  Name      Place 	
    200   Tony      Sweden 	
    201   Jessica   Sweden 	
    
    UserFruits table:
    
    UserID    Fruit
    200       Apple
    200       Orange
    200       Banana
    201       Orange

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    I forget the exact syntax, but something along these lines would return the values that you're looking for:
    Code MySQL:
    SELECT IDnr, Name, Place, GROUP_CONCAT(Fruit) FROM table_name GROUP BY Name;
    I agree with Guido that you should really normalise the table however as you'll struggle if you need to select people with just "Orange", for example. However, if you didn't have that option, for whatever reason, I would select into another table, then when you're done get rid of the old table and rename the new one to the old one, if that makes sense. Something like:
    Code MySQL:
    INSERT INTO new_table (IDnr, Name, Place, Fruit)
         SELECT IDnr
              , Name
              , Place
              , GROUP_CONCAT(Fruit)
           FROM old_table
       GROUP BY Name;
    That's kinda off the top of my head and not necessarily the best solution, but it should work

  4. #4
    SitePoint Enthusiast
    Join Date
    Sep 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!
    thanks for the answer, but what i need is:
    i have a excel with several duplicates except one column and i need to merge that column so i can import to a crm system without any duplicates

    so i imported all in a mysql database tohandle this merge thing :-)

    if i do as you guys said, is there a way to export all to one single row for each record so i can then import that file into the crm?

    Hope you know what i mean

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2011
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks i tryed your code and that made it work exactly what i wanted

    THANKS

    Next problem :-)
    now i have only unique rows, and i also have a csv file with people example:
    IDnr, name, email, telephone

    is there a way to import that into mysql and insert a new column with the value of "place" from the first table were Idnr is like the IDnr in this people table?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i disagree that the data presented means that normalization should be done

    granted, the sample data is overly simplistic, but suppose the table was intended to record where someone first tasted a fruit?

    oh, and antnee, your GROUP BY is deficient, you'll get one row per person, but if the person had more than one country, you'll get an indeterminate one
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    To be honest, looking at it again, I think I'd have left the schema the same, but removed the duplicates and made the IDnr and Fruit fields UNIQUE. Theoretically the Name field should relate back to a user via the IDnr field, judging by the test data, so I'd probably have removed the Name. Country? Hmmm, depends. Is this example recording what countries a particular person has tasted this particular fruit? If so then you could expect my own data to be something like:
    Code:
    Country | Fruit
    --------+------------
    UK      | Apple
    UK      | Banana
    UK      | Orange
    USA     | Apple
    USA     | Banana
    Spain   | Pineapple
    So, should the UNIQUE be on IDnr, Country and Fruit?

    Don't understand exactly what the data is to be used for, but at least this way I could easily select fruits by country, fruits by user, countries by user etc.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Antnee View Post
    Don't understand exactly what the data is to be used for...
    this is precisely the problem with made-up/dumbed-down data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    698
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is precisely the problem with made-up/dumbed-down data
    If I could like a post on here, I would like that


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
  •