How to use mysqli_fetch_fields

Hello Experts!

I need help on the following:

Assuming myTable has the following data:
id         Name        Class          Subject        Marks
1          Stu1           SS1A          Eng               40
2          Stu2           SS1A          Eng               50
3          Stu3           SS1B          Eng               60
4          Stu1           SS1A          Maths          45
5          Stu2           SS1A          Maths          55

How can I achieve the following in report using either WHILE LOOP or FOREACH or mysqli_fetch_fields or whatever (Note: number of Subject above (that will form columns) is unknown)?

Class_Name: SS1A
SN       Name         Eng           Maths         No_of_Subjects
1          Stu1            40             45                2
2          Stu2            50             55                2

Thank you so much in advance.
Opeyemi AbdulRasheedAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If the column names are known, then we can use a static PIVOT with hard coded column names..
Since the column names are unknown, we need to go for Dynamic Pivot and below link will explain you with examples..
Kindly try it and let us know if you would require any assistance so that we will assist accordingly.
http://webdevzoom.com/dynamic-pivot-table-using-mysql/
0
PortletPaulfreelancerCommented:
The traditional way to "pivot" is to use "conditional aggregates" which is basically case expressions inside aggregate functions like sum() or max(). You will need one of these for each subject.

SELECT
    min(id) id
  , Name
  , Class
  , sum(case when Subject = 'Maths' then Marks end) as Maths
  , sum(case when Subject = 'Eng'   then Marks end) as Eng
  , count(*) as No_of_Subjects
FROM myTable 
GROUP BY
   Name, class

Open in new window


[edit] note, that because of the case expressions, the data is moved is a way that using SUM() or MAX() or even MIN() doesn't change the actual results (assuming there is only one row per student, per subject)

Looking the a partial query, the interim results show that there is still only one value per student per class, so SUM() doesn't alter the final outcome.
SELECT
    id
  , Name
  , (case when Subject = 'Eng'   then Marks end) as Eng
  , (case when Subject = 'Maths' then Marks end) as Maths
FROM myTable 

id   Name  Class    Subject  Eng     Maths
1    Stu1     SS1A    Eng    40
4    Stu1     SS1A    Maths          45

2    Stu2     SS1A    Eng    50
5    Stu2     SS1A    Maths          55

3    Stu3     SS1B    Eng    60

Open in new window

and once this is simplified to one row per student it wlil look like this:
id   Name  Class      Eng     Maths
1    Stu1     SS1A    40      45
2    Stu2     SS1A    50      55
3    Stu3     SS1B    60

Open in new window

0
gr8gonzoConsultantCommented:
If you want to accomplish this in code, then I would use object-oriented approaches for this:

<?php
$db = new mysqli(...connection parameters here...);

// ================== CLASS DEFINITIONS ==================
class Student
{
  public $Name;
  public $Subjects = array();
}

class Class
{
  public $Name;
  public $Students = array();
  public $SubjectList = array();
}

// ================== QUERY AND ORGANIZE DATA INTO CLASS OBJECTS ==================
// Our top-level data array
$classes = array();

// Query the data and organize it into objects
$rs = $db->query("SELECT Name, Class, Subject, Marks FROM myTable");
while($row = $rs->fetch_assoc())
{
  $name    = $row["Name"];
  $class   = $row["Class"];
  $subject = $row["Subject"];
  $marks   = $row["Marks"];

  // Initialize new Class
  if(!isset($classes[$class]))
  {
    $classes[$class] = new Class();
    $classes[$class]->Name = $class;    
  }

  // Record the subject for the class
  $classes[$class]->SubjectList[$subject] = $subject;

  // Initialize student
  if(!isset($classes[$class]->Students[$name]))
  {
    $classes[$class]->Students[$name] = new Student();
    $classes[$class]->Students[$name]->Name = $name;
  }

  // Add subject marks
  $classes[$class]->Students[$name]->Subjects[$subject] = $marks;
}

// ================== BUILD OUTPUT TABLE ==================

// Now loop through our data
foreach($classes as $Class)
{
  echo "<b>Class Name: {$Class->Name}</b><br />\n";

  // Build table - column headers
  echo "<table>\n";
  echo "<tr><td>Name</td>";
  foreach($Class->SubjectList as $subject)
  {
      echo "<td>{$subject}</td>";
  }
  echo "<td>No_of_Subjects</td></tr>\n";

  // Loop through students and populate the table
  foreach($Class->Students as $Student)
  {
    echo "<tr><td>{$Student->Name}</td>";
    foreach($Class->SubjectList as $subject)
    {
        // Display the marks if the student has them for that subject, otherwise display a blank string
        echo "<td>" . (isset($Student->Subjects[$subject]) ? $Student->Subjects[$subject] : "") . "</td>";
    }
    echo "<td>" . count($Student->Subjects) . "</td></tr>\n";
  }
  echo "</table><br />\n";
}

Open in new window


I haven't tested that code but the concept is sound.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Opeyemi AbdulRasheedAuthor Commented:
@Raja Jegan R, thank you so much. I checked out the link, nice but I couldn't do much with it.

@PortletPaul, I thank you for your help. In your response, I figured I'll have to hard-code all Subjects (i currently have 73)

So, I tried @gr8gonzo's which yields desirable result.

Thank you all.
1
Opeyemi AbdulRasheedAuthor Commented:
See result. Attached
result.png
0
gr8gonzoConsultantCommented:
Glad it worked!
0
Opeyemi AbdulRasheedAuthor Commented:
If I want to find Total and Average as in:

SN       Name         Eng           Maths         No_of_Subjects    Total      Avg
1          Stu1            40             45                2                             85          42.5
2          Stu2            50             55                2                             105        52.5

Please, how can I achieve that?
0
gr8gonzoConsultantCommented:
In the future, make sure you open up a new question entry if you have new things to ask instead of appending to the old question entry.

You can put the calculation logic into the Student class:
class Student
{
  public $Name;
  public $Subjects = array();

  // Functions to get Total and Average marks
  public function getTotalMarks()
  {
    $total = 0;
    foreach($this->Subjects as $subject => $marks)
    {
      $total += $marks;
    }
    return $total;
  } 

  public function getAverageMarks()
  {
    if(!count($this->Subjects)) { return 0; }
    return $this->getTotalMarks() / count($this->Subjects);
  } 
}

Open in new window


Then in your loop, just add the column headers for Total and Avg:
...
echo "<td>No_of_Subjects</td>
echo "<td>Total</td>
echo "<td>Avg</td>
</tr>\n";
...

Open in new window


And use the new student functions to get the data:
...
echo "<td>" . count($Student->Subjects) . "</td>
echo "<td>" . $Student->getTotalMarks() . "</td>
echo "<td>" . $Student->getAverageMarks() . "</td>
</tr>\n";
...

Open in new window

0
Opeyemi AbdulRasheedAuthor Commented:
Thank you sir.

I'm opening a new question on this. I need to ask one more question. I'll notify you when it's published. Thank you so much
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.