Revisions to How do I display mysql table row as column

Multibyte safety.

Source Link

edited Apr 24, 2014 at 22:10

126.5k
18
220
244

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        mb_regex_encoding($charset);

        $columns = str_replacemb_ereg_replace("`"'`', "``"'``', $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

        $sql .= "
        WHERE level = ? AND semester = ?";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);

        $params = $courses;
        array_push($params, $level, $semester);
        $qry->execute($params);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

        $sql .= "
        WHERE level = ? AND semester = ?";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);

        $params = $courses;
        array_push($params, $level, $semester);
        $qry->execute($params);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        mb_regex_encoding($charset);

        $columns = mb_ereg_replace('`', '``', $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

        $sql .= "
        WHERE level = ? AND semester = ?";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);

        $params = $courses;
        array_push($params, $level, $semester);
        $qry->execute($params);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

added 187 characters in body

Source Link

edited Apr 30, 2012 at 19:10

eggyal

126.5k
18
220
244

Construct a rather horrible MySQL query to perform the pivoting operation manually:

SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP FROM gp NATURAL JOIN ( SELECT student_id, grade AS MAT111 FROM result WHERE course_code = 'MAT111' ) AS tMAT111 NATURAL JOIN ( SELECT student_id, grade AS MAT112 FROM result WHERE course_code = 'MAT112' ) AS tMAT112 NATURAL JOIN ( -- etc. WHERE level = @level AND semester = @semester

If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.

Here is one way that you could do that in PHP:

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

        $sql .= "
        WHERE level = ? AND semester = ?";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);

        $params = $courses;
        array_push($params, $level, $semester);
        $qry->execute($courses$params);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

Construct a rather horrible MySQL query to perform the pivoting operation manually:

SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP FROM gp NATURAL JOIN ( SELECT student_id, grade AS MAT111 FROM result WHERE course_code = 'MAT111' ) AS tMAT111 NATURAL JOIN ( SELECT student_id, grade AS MAT112 FROM result WHERE course_code = 'MAT112' ) AS tMAT112 NATURAL JOIN ( -- etc.

If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.

Here is one way that you could do that in PHP:

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);
        $qry->execute($courses);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

Construct a rather horrible MySQL query to perform the pivoting operation manually:

SELECT student_id AS Matriculation, MAT111, MAT112, gp AS GP FROM gp NATURAL JOIN ( SELECT student_id, grade AS MAT111 FROM result WHERE course_code = 'MAT111' ) AS tMAT111 NATURAL JOIN ( SELECT student_id, grade AS MAT112 FROM result WHERE course_code = 'MAT112' ) AS tMAT112 -- etc. WHERE level = @level AND semester = @semester

If you choose to go down this path, you can make your life slightly easier by generating this query automatically, using either a looping construct in PHP or a prepared statement in MySQL.

Here is one way that you could do that in PHP:

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

        $sql .= "
        WHERE level = ? AND semester = ?";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);

        $params = $courses;
        array_push($params, $level, $semester);
        $qry->execute($params);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

deleted 8 characters in body

Source Link

edited Apr 30, 2012 at 18:13

eggyal

126.5k
18
220
244

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);
        $qry->execute($courses);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          echo "<th scope='col'>";
         $meta echo= htmlentities($qry->getcolumnMeta($i)['name']);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);
        $qry->execute($courses);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          echo "<th scope='col'>";
          echo htmlentities($qry->getcolumnMeta($i)['name']);
          echo "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

1. Obtain a list of courses:

        $dbh = new PDO('mysql:dbname=testdb;host=127.0.0.1', $user, $password);
        $qry = $dbh->query("SELECT DISTINCT course_code FROM result [WHERE ...]");
        $courses = $qry->fetchAll(PDO::FETCH_COLUMN, 0);

2. Loop over the results, constructing the above SQL:

        $columns = str_replace("`", "``", $courses);
        $sql = "
        SELECT student_id AS Matriculation, `".implode("`,`", $columns)."`, gp AS GP
          FROM gp";

        foreach ($columns as $column) $sql .= "
          NATURAL JOIN (
            SELECT student_id, grade AS `$column`
            FROM result
            WHERE course_code = ?
          ) AS `t$column`";

3. Execute the SQL, passing in the array of courses as parameters:

        $qry = $dbh->prepare($sql);
        $qry->execute($courses);

4. Output the results:

        echo "<table>";

        echo "<tr>";
        for ($i = 0; $i < $qry->columnCount(); $i++) {
          $meta = $qry->getcolumnMeta($i);
          echo "<th scope='col'>" . htmlentities($meta['name']) . "</th>";
        }
        echo "</tr>";

        while ($row = $qry->fetch(PDO::FETCH_NUM)) {
          echo "<tr>";
          foreach ($row as $field) echo "<td>" . htmlentities($field) . "</td>"
          echo "</tr>";
        }

        echo "</table>";

added 16 characters in body

Source Link

edited Apr 30, 2012 at 18:05

eggyal

126.5k
18
220
244

Loading

added 73 characters in body

Source Link

edited Apr 30, 2012 at 13:36

eggyal

126.5k
18
220
244

Loading

deleted 11 characters in body

Source Link

edited Apr 29, 2012 at 17:40

eggyal

126.5k
18
220
244

Loading

added 1307 characters in body

Source Link

edited Apr 29, 2012 at 17:29

eggyal

126.5k
18
220
244

Loading

added 1307 characters in body

Source Link

edited Apr 29, 2012 at 17:23

eggyal

126.5k
18
220
244

Loading

deleted 42 characters in body

Source Link

edited Apr 28, 2012 at 18:29

eggyal

126.5k
18
220
244

Loading

Source Link

answered Apr 28, 2012 at 18:11

eggyal

126.5k
18
220
244

Loading

Collectives™ on Stack Overflow

Return to Answer