MySQL Forums
Forum List  »  Newbie

Writing columns from different tables as rows in another table
Posted by: Jason Bourne
Date: August 24, 2011 11:13AM

I am trying to dispay two tables from my database and I am not sure how to proceed. I was using Excel and Access until someone suggested me to use PHP and mySQL. I would appreciate a little help in writing the code. I think the easiest way for me to explain would be to post my input tables and desired output tables. I am struggling in writing sql code to rewrite rows in the form of columns.

Please note that CREATE TABLE AND INSERT VALUES statements are at the bottom.

Three input tables:

mysql> select * from table1;
 +----+------+------+------+------+------+------+------+
 | Id | L   | F   | sex  | town | t1   | t2   | t3   |
 +----+------+------+------+------+------+------+------+
 | 1  | x1   | y1   | F    | A    | 50   | 55   | 60   |
 | 2  | x2   | y2   | M    | B    | NULL | 70   | 75   |
 | 3  | x3   | y3   | F    | A    | 80   | NULL | 78   |
 +----+------+------+------+------+------+------+------+
 3 rows in set (0.00 sec)
 
mysql> select * from table2;
 +----+------+------+------+------+------+------+------+------+------+
 | Id | L   | F   | sex  | town | q1   | q2   | t1   | t2   | t3   |
 +----+------+------+------+------+------+------+------+------+------+
 | 10 | x5   | y5   | M    | C    | 4    | 6    | 71   | 72   | 73   |
 | 11 | x6   | y6   | F    | A    | NULL | 10   | 90   | 91   | 92   |
 | 1  | x1   | y1   | F    | A    | 8    | 9    | 50   | 55   | 60   |
 +----+------+------+------+------+------+------+------+------+------+
 3 rows in set (0.00 sec)
 
mysql> select * from table3;
 +----+-------+-------+
 | Id | test  | score |
 +----+-------+-------+
 | 1  | test1 | 80    |
 | 1  | test2 | 58    |
 | 1  | test3 | 67    |
 | 10 | test1 | 80    |
 | 10 | test2 | 85    |
 | 2  | test3 | 88    |
 | 2  | test4 | 89    |
 +----+-------+-------+


My desired output (2 tables)

Output#1 (posts on a webpage and an outfile)
 
Classif.        count   percent
  F              3       60%
  M              2       40% 
  A              3       60%
  B              1       20%
  C              1       20%

Output#2 (posts on a webpage and an outfile) 
Id      sex     test1   test2   test3   test4   t3
  1      F       80,     58      67      NULL    60
  2      M       NULL    NULL    88      89      75
  10     M       80      85      NULL    NULL    73
 .....
 .....

// Create 3 tables
                $sql_1 = "CREATE TABLE table1    
                 ( 
                        Id varchar(20),
                        LN varchar(20),
                        FN varchar(20),
                        sex varchar(20),
                        town varchar(20),
                        t1 varchar(20),
                        t2 varchar(20),
                        t3 varchar(20),
                        primary key(ID)
	)";

                $sql_2 = "CREATE TABLE table2    
                 ( 
                        Id varchar(20),
                        LN varchar(20),
                        FN varchar(20),
                        sex varchar(20),
                        town varchar(20),
                        q1 varchar(20),
                        q2 varchar(20),
                        t1 varchar(20),
                        t2 varchar(20),
                        t3 varchar(20),
                        primary key(ID)
                  )";

                $sql_3 = "CREATE TABLE table3    
                 ( 
                        Id varchar(20),
                        test varchar(20),
                        score varchar(20),
                        primary key (Id, test)
                 )";

         
// Execute queries
            mysql_query($sql_1,$conn);
            mysql_query($sql_2,$conn);
            mysql_query($sql_3,$conn);


// Inserts
            mysql_query("INSERT INTO table1(Id, LN, FN, sex, town, t1, t2, t3) VALUES('1', 'x1', 'y1', 'F', 'A', '50', '55', '60') ");
            mysql_query("INSERT INTO table1(Id, LN, FN, sex, town, t1, t2, t3) VALUES('2', 'x2', 'y2', 'M', 'B', NULL, '70', '75') ");
            mysql_query("INSERT INTO table1(Id, LN, FN, sex, town, t1, t2, t3) VALUES('3', 'x3', 'y3', 'F', 'A', '80', NULL, '78') ");

            mysql_query("INSERT INTO table2(Id, LN, FN, sex, town, q1, q2, t1, t2, t3) VALUES('10', 'x5', 'y5', 'M', 'C', '4', '6', '71', '72', '73')");
            mysql_query("INSERT INTO table2(Id, LN, FN, sex, town, q1, q2, t1, t2, t3) VALUES('11', 'x6', 'y6', 'F', 'A', NULL, '10', '90', '91', '92')");
            mysql_query("INSERT INTO table2(Id, LN, FN, sex, town, q1, q2, t1, t2, t3) VALUES('1', 'x1', 'y1', 'F', 'A', '8', '9', '50', '55', '60')");            

            mysql_query("INSERT INTO table3(Id, test, score) VALUES('1', 'test1', '80')");
            mysql_query("INSERT INTO table3(Id, test, score) VALUES('1', 'test2', '58')");
            mysql_query("INSERT INTO table3(Id, test, score) VALUES('1', 'test3', '67')");
            mysql_query("INSERT INTO table3(Id, test, score) VALUES('10', 'test1', '80')");
            mysql_query("INSERT INTO table3(Id, test, score) VALUES('10', 'test2', '85')");
            mysql_query("INSERT INTO table3(Id, test, score) VALUES('2', 'test3', '88')");
            mysql_query("INSERT INTO table3(Id, test, score) VALUES('2', 'test4', '89')");

Options: ReplyQuote


Subject
Written By
Posted
Writing columns from different tables as rows in another table
August 24, 2011 11:13AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.