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')");