We help IT Professionals succeed at work.

How to use mysqli_fetch_fields

128 Views
Last Modified: 2018-09-08
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.
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
CERTIFIED EXPERT
Awarded 2009
Distinguished Expert 2019

Commented:
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/
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
@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
gr8gonzoConsultant
CERTIFIED EXPERT

Commented:
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?
gr8gonzoConsultant
CERTIFIED EXPERT

Commented:
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
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.