Page 1 of 1

Writing columns as rows

Posted: Tue Aug 23, 2011 7:00 am
by PHP_mySQL__Newbie
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:

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    |
+----+-------+-------+
My desired output (2 tables)

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%
Output#2 (posts on a webpage and an outfile)

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>";            
         ?>


Re: Writing columns as rows

Posted: Wed Aug 24, 2011 11:56 am
by PHP_mySQL__Newbie
I am learning how to post as I go. Someone suggested that I should also post the code that is needed to create and insert values. I would have thought about this earlier. My main problem is that I am not able to select columns from one table and display it as rows (see the two desired outputs above). Thank you.
(Not sure if I should be posting in the database forum)

Code: Select all

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