57881

PHP MySQL consolidate column where other column has duplicates

Question:

I have a MySQL table that has three columns, the first is a unique key (INT(11) AUTO_INCREMENT), the next is an indexed value (VARCHAR(255)) and the third is a description (TEXT). There are duplicate values in the second column, but each row has a different description. I want to remove all rows where the second column is duplicated but append each description of the same indexed value to the first instance the value, and breaking string with a semicolon and space.

For example, my table looks like this:

cid | word | description ------------------------------ 1 | cat | an animal with wiskers 2 | cat | a house pet 3 | dog | a member of the canine family 4 | cat | a cool person

I want to change the table to look like this:

cid | word | description ------------------------------ 1 | cat | an animal with wiskers; a house pet; a cool person 3 | dog | a member of the canine family

I'm not adverse to using a PHP script to do this, but would prefer MySQL. The table has over 170,000 rows and would take PHP a long time to loop over it.

Answer1:

SQL:

select `cid`,`word`,group_concat(`description` SEPARATOR '; ') as `description` from `test_table` group by `word`;

Ok.. you can copy all the data into another table, and rename it then..

insert into `test_new` (`cid`,`word`,`desc`) (select `cid`,`word`,group_concat(`desc` SEPARATOR '; ') as `description` from `test_table` group by `word`); mysql> describe `test_new`; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | word | char(10) | YES | | NULL | | | desc | text | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> select * from `test_new`; +------+------+---------------------+ | id | word | desc | +------+------+---------------------+ | 1 | cat | desc1; desc2; desc4 | | 3 | dog | desc3 | +------+------+---------------------+ 2 rows in set (0.00 sec)

Answer2:

As was mentioned before, you can create a new table and copy the info, you can also do it in two steps, but only if there´s no problem with modifying the old table:

UPDATE tableOld AS N1, tableOld AS N2 SET N1.description = concat(concat(N1.description,"; "),N2.decription)) WHERE N2.word = N1.word insert into tableNew (cid,name,description)select * from tableOld group by word

Recommend

  • Development Provisioning Profile [closed]
  • How do I create a duplicate of an XML element, but change the name of the element and delete the ori
  • is procedure exist in mongodb
  • Compare Value with Comma Separated String SQL PHP
  • Trim white spaces in both Object key and value recursively
  • Load in a simple list of objects defined in xml as an object in C#
  • Excel repeat cells
  • php reg ex to find data not in html tags but identify html using < and >
  • Is there a way to get around the limitations of index signatures in Typscript?
  • Deploy iOS application for one company employees only
  • Retrieve XML parent node attribute if child node meets a certain criteria and assign both to variabl
  • How to get parent id(root parent) from child id [duplicate]
  • Problem fetching XML data using Expedia API and curl
  • how to parse different type of xml data or similar to xml structure
  • add countdown for each row in html table
  • XML Convertion to CSV
  • Passing callback parameters to setTimeout() does not work in IE?
  • XPath expression with white space in node for defiant.js
  • How to store Button Text of Buttons in a variable? Android Calculator Project
  • Presenting NSOpenPanel as sheet synchronously
  • JFrame attached on the side of another JFrame
  • Why is JSON.NET adding all these backslashes
  • With one EDMX file use multiple connection strings that relate to multiple databases
  • What is a good persistence design for this entity hierarchy?
  • function for writing out a base 7 word, like binary counter style
  • Getting a generic method to infer the type parameter from the runtime type
  • Finding parents in a tree hierarchy for a given child LINQ (lambda expression)
  • DNS Lookup failed - Error with all browsers [closed]
  • How to determine the CCSID used in CPYFRMIMPF command?
  • php is_file always return false
  • How to get the date of next specified day of week
  • uml Composition relationships to RDF and OWL
  • SetWindowsHookEx does not react on media keys
  • Mysql - How to search for 26 records that each begins with the letter of the alphabet?
  • Declaring variable dynamically in VB.net
  • output of program is not same as passed argument
  • Join two tables and save into third-sql
  • Knitr HTML Loop - Some HTML output, some R output
  • Unit Testing MVC Web Application in Visual Studio and Problem with QTAgent
  • Trying to get generic when generic is not available