Writing columns as rows
Posted: Tue Aug 23, 2011 7:00 am
I am trying to make a table 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.
My input tables:
My desired output (2 tables)
Output#1 (posts on a webpage and an outfile)
Output#2 (posts on a webpage and an outfile)
My input tables:
Code: Select all
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 |
+----+-------+-------+
Output#1 (posts on a webpage and an outfile)
Code: Select all
Classif. count percent
F 3 60%
M 2 40%
A 3 60%
B 1 20%
C 1 20%
Code: Select all
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
.....
.....
Code: Select all
<?php
$conn = mysql_connect("localhost","xx","xxx");
if (!$conn) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db("testDB", $conn);
$result_1 = mysql_query("
(SELECT sex, count(*) FROM table1 GROUP BY sex)
UNION
(SELECT sex, count(*) FROM table2 GROUP BY sex)
", $conn)
or die(mysql_error());
echo "Output#1<br>";
while($row = mysql_fetch_array( $result_1 ))
echo $row['sex']."\t". $row['count(*)']."\t".($row['count(*)']/5*100)."<br>";
// didn't know how to find number of rows for 2 tables
echo "<br>";
$result_2 = mysql_query("SELECT * FROM table3");
echo "Output#2<br>";
while($row = mysql_fetch_array( $result_2 ))
echo $row['Id']."\t". $row['test']."<br>";
echo "<br>";
?>