I have a problem where I have multiple rows in a database that contain the same email. The duplicated rows are due to contacts wishing to update their information and instead of updating it would insert as a new row.
What I would like to do is merge all duplicate rows given an email and merge the data together as a result returned in PHP.
There is a specific:
id email prefix first_name
1 email@example.com Mr. Bob
2 firstname.lastname@example.org Bob
3 email@example.com Bobby
4 firstname.lastname@example.org Mr Bobby
5 email@example.com Bob
email prefix first_name
firstname.lastname@example.org Mr Bob
id = 4
$this->db->select('company, title, address_line1, address_line2, address_line3, city, state/prov, country, postal_code');
In MySQL, one method uses
select email substring_index(group_concat(prefix order by (prefix is not null) desc, id desc separator '|' ), '|', 1) as prefix, substring_index(group_concat(first_name order by (first_name is not null) desc, id desc separator '|' ), '|', 1) as first_name, . . . from t group by email;
The approach is that you are concatenating the values together and then extracting the first element.
group_concat(). You might need to increase its size.
'|'in this case) should not appear in any value.
An alternative method uses correlated subqueries:
select e.email, (select t2.prefix from t t2 where t2.email = t.email and t2.prefix is not null order by id desc limit 1 ) as prefix, (select t2.first_name from t t2 where t2.email = t.email and t2.first_name is not null order by id desc limit 1 ) as first_name, . . . from (select distinct email from t) e;
This method has the advantage of preserving the original types.