Link to home
Start Free TrialLog in
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.
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

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/
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
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Opeyemi AbdulRasheed
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.
See result. Attached
result.png
Glad it worked!
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?
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

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