Avatar of Opeyemi AbdulRasheed
Opeyemi AbdulRasheed
 asked on

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.
DatabasesPHPMySQL ServerSQL

Avatar of undefined
Last Comment
Opeyemi AbdulRasheed

8/22/2022 - Mon
Raja Jegan R

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/
PortletPaul

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

ASKER CERTIFIED SOLUTION
gr8gonzo

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Opeyemi AbdulRasheed

ASKER
@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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Opeyemi AbdulRasheed

ASKER
See result. Attached
result.png
gr8gonzo

Glad it worked!
Opeyemi AbdulRasheed

ASKER
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
gr8gonzo

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

Opeyemi AbdulRasheed

ASKER
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