0

I have this in my database:

 Stud_id        subject      Total
 Abc12          Eng           60
 Abc13          Eng           40    
 Abc12          Math          70
 Abc13          Math          50

This is the output i expect:

 Stud_id        Eng       Math

 Abc12          60        70
 Abc13          40        50

If there was a large amount of records, how could i do this in bulk amount? performance and reliability is much needed!

I tried the following code:

<?php

            ...
            $sql = "select   
              GROUP_CONCAT(DISTINCT
                 CONCAT(
                  'ifnull(SUM(case when sub_code = ''',
                  sub_code,
                  ''' then total end),0) AS `',
                  sub_code, '`'
                )
                ) INTO @sql
                FROM scores_tbl;
            
           (stud_id) as USERID
           , sum(total) as Total,
           ('') as '%'
       
           FROM scores_tbl where class_name='JSS1' AND GROUP by stud_id";
            $result = $db->multi_query($sql);

            if ($err=mysqli_error($db)) { echo $err."<br><hr>"; }

            if ($result) {
              do {
              if ($res = $db->store_result()) {
                  echo "<table width=100% border=1><tr>";
    
              // printing table headers
                  for($i=0; $i<mysqli_num_fields($res); $i++)
                  {
                      $field = mysqli_fetch_field($res);
                  echo "<td bgcolor=lightgray><b>{$field->name}</b></td>";
                  }
                  echo "</tr>\n";
    
                  // printing table rows
                  while($row = $res->fetch_row())
                  {
                      echo "<tr>";
                      foreach($row as $cell) {
                            if ($cell === NULL) { $cell = '(null)'; }
                        echo "<td>$cell</td>";
                      }
                      echo "</tr>\n";
                  }
                  $res->free();
                  echo "</table>";
        
                }
              } while ($db->more_results() && $db->next_result());
            }
            $db->close();
            ?>

It doesn't give any errors, but doesn't work either

Ryan The Ghost
  • 116
  • 1
  • 13
jst
  • 11
  • 2
  • First question: Do you know all if the subjects in advance? – mickmackusa Feb 14 '22 at 04:44
  • No. The user may decide to enter as many subjects whicg i may not be aware – jst Feb 14 '22 at 05:46
  • *I have something like these in the mysqli database* Provide sample data as complete CREATE TABLE + INSERT INTO. *How can it be done dynamically? I have the following but seem not to work* Do you want to solve on PHP side? or maybe stored procedure on MySQL side is safe for you? – Akina Feb 14 '22 at 06:33
  • CREATE TABLE IF NOT EXISTS `scores_tbl` ( `id` mediumint(10) NOT NULL AUTO_INCREMENT, `stud_id` varchar(100) NOT NULL, `class_name` varchar(100) NOT NULL, `sub_code` varchar(100) NOT NULL, `total` decimal(6,1) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `scores_tbl` -- – jst Feb 14 '22 at 07:43
  • INSERT INTO `scores_tbl` (`id`, `stud_id`, `class_name`, `sub_code`, `total`) VALUES (1, 'Abc12', 'JSS1', 'Eng', '60.0'), (2, 'Abc13', 'JSS1', 'Eng', '40.0'), (3, 'Abc12', 'JSS1', 'Math', '70.0'), (4, 'Abc13', 'JSS1', 'Math', '70.0'); – jst Feb 14 '22 at 07:43
  • **Never use `multi_query`** – Dharman Feb 14 '22 at 12:01
  • Please, why did you say I should never use multi query? – jst Feb 14 '22 at 15:52

1 Answers1

0

Thank you everyone here. I later solved the problem by myself using the similar solution posted on this platform. This is how I solved it:

$MySQLiconn->query('SET @sql = NULL');
$MySQLiconn->query("
SELECT
  GROUP_CONCAT(DISTINCT
     CONCAT(
     'ifnull(SUM(case when sub_code = ''',
      sub_code,
      ''' then total end),0) AS `',
      sub_code, '`'
    )
   ) INTO @sql
FROM scores_tbl;"

);
               
$MySQLiconn->query("SET @sql = CONCAT('SELECT 
stud_id, ', @sql, ' FROM scores_tbl GROUP BY 
stud_id');");
$MySQLiconn->query("PREPARE stmt FROM @sql");

$res = $MySQLiconn->query("EXECUTE stmt");
jst
  • 11
  • 2