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:
[table=“width: 500”]
[tr]
[td]IDnr[/td]
[td]Name[/td]
[td]Place[/td]
[td]Fruit[/td]
[/tr]
[tr]
[td]200[/td]
[td]Tony[/td]
[td]Sweden[/td]
[td]Apple[/td]
[/tr]
[tr]
[td]201[/td]
[td]Jessica[/td]
[td]Sweden[/td]
[td]Orange[/td]
[/tr]
[tr]
[td]200[/td]
[td]Tony[/td]
[td]Sweden[/td]
[td]Orange[/td]
[/tr]
[tr]
[td]200[/td]
[td]Tony[/td]
[td]Sweden[/td]
[td]banana[/td]
[/tr]
[/table]
As above i want the data base to do this:
[table=“width: 500”]
[tr]
[td]IDnr[/td]
[td]Name[/td]
[td]Place[/td]
[td]Fruit[/td]
[/tr]
[tr]
[td]200[/td]
[td]Tony[/td]
[td]Sweden[/td]
[td]Apple,Orange,Banana[/td]
[/tr]
[tr]
[td]201[/td]
[td]Jessica[/td]
[td]Sweden[/td]
[td]Orange[/td]
[/tr]
[/table]
Hope you guys know what i mean
if i need some script please guide me with how to do that in php
I forget the exact syntax, but something along these lines would return the values that you’re looking for:
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:
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
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?
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?
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
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:
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.
