Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

enter image description here

share|improve this question
    
@RolandoMySQLDBA: any help here? –  karto May 28 '12 at 16:23
add comment

1 Answer

up vote 2 down vote accepted

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 !!!

share|improve this answer
    
wow. looks very good. I'm trying it now. –  karto May 28 '12 at 18:07
    
please what does where 1=2 mean in the first line? –  karto May 28 '12 at 18:12
    
The where 1=2 is there to prevent data from being copied. That query is just to create the table with no keys. I add the primary next before loading the final table –  RolandoMySQLDBA May 28 '12 at 19:10
    
OMG,Worked like magic. Thanks a million –  karto May 28 '12 at 19:39
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.