php/mysql merge duplicate records in table and create a new table

Question

please with respect to username and storetype, how can I merge rows to achieve Final table? storetype should be unique per username. There are thousands of usernames and records so php/mysql would help. thanks

Answer 1

Here is the pure MySQL solution

create table finaltable select * from table1 where 1=2;
alter table finaltable add primary key (id);
insert into finaltable (username,storetype,id)
select * from
(select username,storetype,min(id) min_id
from table1 group by username,storetype) A;
update finaltable A inner join
(
    select username,storetype,
    MIN(IFNULL(filename,'ZZZZZZZZZZZZ')) filename,
    MIN(IFNULL(`date`  ,'ZZZZZZZZZZZZ')) 'date',
    MIN(IFNULL(`desc`  ,'ZZZZZZZZZZZZ')) 'desc',
    MIN(IFNULL(password,'ZZZZZZZZZZZZ')) password,
    MIN(IFNULL(email   ,'ZZZZZZZZZZZZ')) email,
    MIN(IFNULL(ftp     ,'ZZZZZZZZZZZZ')) ftp
    from table1 group by username,storetype
) B using (username,storetype) set 
A.filename = B.filename,
A.`date`   = B.`date`,
A.`desc`   = B.`desc`,
A.password = B.password,
A.email    = B.email,
A.ftp      = B.ftp
;

First here is your sample data from the question

mysql> drop database if exists karto;
Query OK, 2 rows affected (0.01 sec)

mysql> create database karto;
Query OK, 1 row affected (0.00 sec)

mysql> use karto
Database changed
mysql> create table table1
    -> (
    ->     id int not null auto_increment,
    ->     username varchar(20),
    ->     filename varchar(20),
    ->     `date` date,
    ->     `desc` varchar(20),
    ->     storetype varchar(20),
    ->     password varchar(20),
    ->     email varchar(20),
    ->     ftp varchar(20),
    ->     primary key (id),
    ->     key username_storetype (username,storetype)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1
    -> (username,filename,`date`,`desc`,
    -> storetype,password,email,ftp) values
    -> ('ken',NULL         ,NULL        ,'good'  ,'google','1235'    ,'ken@me.com','Ken.me'),
    -> ('ken',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken','Product.txt','2012-1-1'  ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('ken',NULL         ,NULL        ,'good'  ,'amazon','4654'    ,'Ken1@me.com','Ken1.net'),
    -> ('ken',NULL         ,NULL        ,NULL    ,'amazon',NULL      ,NULL,NULL),
    -> ('ken','Amazon.txt' ,'2012-1-4'  ,NULL    ,'amazon',NULL      ,NULL,NULL),
    -> ('sam',NULL         ,NULL        ,'works' ,'google','477'     ,'Sam1@gm.com','sam'),
    -> ('sam',NULL         ,NULL        ,NULL    ,'google',NULL      ,NULL,NULL),
    -> ('sam','Sales.txt'  ,'2012-10-10',NULL    ,'google',NULL      ,NULL,NULL),
    -> ('sam',NULL         ,NULL        ,'broken','ebay'  ,'3534534' ,'samlee@hm.com','Sam44'),
    -> ('sam',NULL         ,NULL        ,NULL    ,'ebay'  ,NULL      ,NULL,NULL),
    -> ('sam','june.txt'   ,'2012-2-12' ,NULL    ,'ebay'  ,NULL      ,NULL,NULL);
Query OK, 13 rows affected (0.02 sec)
Records: 13  Duplicates: 0  Warnings: 0

mysql> select * from table1;
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
| id | username | filename    | date       | desc   | storetype | password | email         | ftp      |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
|  1 | ken      | NULL        | NULL       | good   | google    | 1235     | ken@me.com    | Ken.me   |
|  2 | ken      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
|  3 | ken      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
|  4 | ken      | Product.txt | 2012-01-01 | NULL   | google    | NULL     | NULL          | NULL     |
|  5 | ken      | NULL        | NULL       | good   | amazon    | 4654     | Ken1@me.com   | Ken1.net |
|  6 | ken      | NULL        | NULL       | NULL   | amazon    | NULL     | NULL          | NULL     |
|  7 | ken      | Amazon.txt  | 2012-01-04 | NULL   | amazon    | NULL     | NULL          | NULL     |
|  8 | sam      | NULL        | NULL       | works  | google    | 477      | Sam1@gm.com   | sam      |
|  9 | sam      | NULL        | NULL       | NULL   | google    | NULL     | NULL          | NULL     |
| 10 | sam      | Sales.txt   | 2012-10-10 | NULL   | google    | NULL     | NULL          | NULL     |
| 11 | sam      | NULL        | NULL       | broken | ebay      | 3534534  | samlee@hm.com | Sam44    |
| 12 | sam      | NULL        | NULL       | NULL   | ebay      | NULL     | NULL          | NULL     |
| 13 | sam      | june.txt    | 2012-02-12 | NULL   | ebay      | NULL     | NULL          | NULL     |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
13 rows in set (0.00 sec)

mysql>

Now, let's run the SQL I proposed

mysql> create table finaltable select * from table1 where 1=2;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table finaltable add primary key (id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into finaltable (username,storetype,id)
    -> select * from
    -> (select username,storetype,min(id) min_id
    -> from table1 group by username,storetype) A;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> update finaltable A inner join
    -> (
    ->     select username,storetype,
    ->     MIN(IFNULL(filename,'ZZZZZZZZZZZZ')) filename,
    ->     MIN(IFNULL(`date`  ,'ZZZZZZZZZZZZ')) 'date',
    ->     MIN(IFNULL(`desc`  ,'ZZZZZZZZZZZZ')) 'desc',
    ->     MIN(IFNULL(password,'ZZZZZZZZZZZZ')) password,
    ->     MIN(IFNULL(email   ,'ZZZZZZZZZZZZ')) email,
    ->     MIN(IFNULL(ftp     ,'ZZZZZZZZZZZZ')) ftp
    ->     from table1 group by username,storetype
    -> ) B using (username,storetype) set
    -> A.filename = B.filename,
    -> A.`date`   = B.`date`,
    -> A.`desc`   = B.`desc`,
    -> A.password = B.password,
    -> A.email    = B.email,
    -> A.ftp      = B.ftp
    -> ;
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> select * from finaltable;
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
| id | username | filename    | date       | desc   | storetype | password | email         | ftp      |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
|  1 | ken      | Product.txt | 2012-01-01 | good   | google    | 1235     | ken@me.com    | Ken.me   |
|  5 | ken      | Amazon.txt  | 2012-01-04 | good   | amazon    | 4654     | Ken1@me.com   | Ken1.net |
|  8 | sam      | Sales.txt   | 2012-10-10 | works  | google    | 477      | Sam1@gm.com   | sam      |
| 11 | sam      | june.txt    | 2012-02-12 | broken | ebay      | 3534534  | samlee@hm.com | Sam44    |
+----+----------+-------------+------------+--------+-----------+----------+---------------+----------+
4 rows in set (0.00 sec)

mysql>

All you have to do is run these SQL commands with each mysqli function in PHP and you are all set.

Give it a Try !!!