1

I have gone through all the various suggestions but none seems to matches the requirement. This is NOT a Pivot Table. The first table (Course_table) has a list of course, about 1,200. I want to create a second table (marksheet_table) which has as its columns the rows of the first table. How can you create the marksheet_table in PHP and SQL (MySQL) using the records in the first Table as its field names e.g.

Course_table    
 |  Subject |   unit    |   staff   |
--------------------------------------------------
 |  Math    |   3       |   Mr James    |   
 |  Econs   |   1       |   Dr Smith    |   
 |  Chem    |   2       |   Mrs Aisha   |       



Marksheet_table    
StudentID | Math    |   Econs   |   Chem    |
--------------------------------------------------
10001     | 10      |     20    |     30    |
10045     | 11      |     09    |     45    |


<?php
include 'config.php'; 
        mysql_select_db("DB_Subject", $conn);
        $Select_sql = mysql_query(" SELECT DISTINCT subject FROM Course_table");   
        while($row= mysql_fetch_array($Select_sql)) 
        { $CourseCode   = $row["CourseCode"] ;
            $Create_sql = mysql_query(" CREATE TABLE Marksheet_table (  
                                    id INT(5) UNSIGNED ATO_INCREMENT PRIMARY KEY,
                                    regno INT(8) NOT NULL,
                                    $subject DECIMAL(4,2),
                                    ");     
                }
          mysql_fetch_array($Create_sql) ;
    mysql_close($conn); 

?>
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Nditah
  • 1,429
  • 19
  • 23
  • 2
    Very strange requirement. – Rahul Nov 13 '14 at 11:07
  • 2
    Don't see what's so strange (maybe the fact that B should be a table, I would rather use it as a view) : it's a PIVOT, see for example http://stackoverflow.com/questions/7674786/mysql-pivot-table – Raphaël Althaus Nov 13 '14 at 11:08
  • 1
    Well, it's unlikely that you would want to create a 'table', but a 'view' of table_a seems like a fairly reasonable requirement. – Strawberry Nov 13 '14 at 11:19
  • @SamRans this http://sqlfiddle.com/#!2/0adbb/1 may help you – Mobasher Fasihy Nov 13 '14 at 11:20
  • Looking at what you whant to achieve, i'd say you have a conception problem more than a technical problem – Anthony Raymond Nov 15 '14 at 15:08
  • thank you all for commenting. I am working on a school database. I already have a table of all the courses (Table A). I now want to create a table of Marksheet (Table B). I dont know if there is any better and simpler way of achieving it but I am sure if I can have it dont this way, it will be the real deal. I appreciate any meaningful contribution. Thanks! ........ ... – Nditah Nov 15 '14 at 15:10
  • 1
    Sure there is a better way: Table B should have columns `StudentID`, `Subject`, `Grade`. (Or some variation if `Subject` does not uniquely identify the course taken.) – alexis May 30 '18 at 14:59

0 Answers0