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

SN Name Eng Maths No_of_Subjects

1 Stu1 40 45 2

2 Stu2 50 55 2

Thank you so much in advance.

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.

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.

[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 (

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
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
```

[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
```

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
```

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

Thank you all.

@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.

ASKER

See result. Attached

result.png

result.png

Glad it worked!

ASKER

If I want to find Total and Average as in:

SN Name Eng Maths No_of_Subjects

1 Stu1 40 45 2

2 Stu2 50 55 2

Please, how can I achieve that?

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:

Then in your loop, just add the column headers for Total and Avg:

And use the new student functions to get the data:

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);
}
}
```

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";
...
```

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";
...
```

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

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

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/