What can I do with this problem with mysql php and table result?

Asked Nov 08 '19 at 20:02

Active Nov 09 '19 at 17:39

Viewed 38 times

I have this query to list name categories and avg of each one, but the result is not what I want, how to sove this? if you look to image you see that the table resulta don't show correct

$query = $connMysqli->query("SELECT qc.name as cat_name

FROM fhrw_question_categories qc
  ...
group by Quiz, cat
ORDER BY u.id");

?>
<table>
<tr>
<?php
while($row = $query->fetch_assoc()){
    echo '<th>' . $row['cat_name'] . '</th>';   
}?>
</tr>

<!-- table continiuous avg-->
<?
$query = $connMysqli->query("SELECT avg(IFNULL(fraction, 0)) * 10 as Media, u.ud as student, qc.name as cat_name

FROM fhrw_question_attempt_steps qc
 ...
group by Quiz, u.id, cat
ORDER BY u.id ASC");
?>

<?php
while($row = $query->fetch_assoc()){
    echo '<tr><td>' . $row['cat_name'] . ' - ' . round($row['Media'],2) . '</td></tr>';
}?>

edited Nov 09 '19 at 17:39

Dharman

30,962
25
85
135

asked Nov 08 '19 at 20:02

Miguel Silva

You should just do `group by cat_name` – Barmar Nov 08 '19 at 20:12
2

What is the result supposed to look like? – Barmar Nov 08 '19 at 20:13
2

Why are you calculating `Media` in the first query if you don't echo it? – Barmar Nov 08 '19 at 20:14
If you want a column for each category, you should use a pivot query. See https://stackoverflow.com/questions/12004603/mysql-pivot-row-into-dynamic-number-of-columns – Barmar Nov 08 '19 at 20:14
@Barmar you are right, it is not necessary calculate Media in the first query. it Edited – Miguel Silva Nov 09 '19 at 00:27
Ok, I should use pivot query, but it is very dificult understand it. – Miguel Silva Nov 09 '19 at 01:05

What can I do with this problem with mysql php and table result?

0 Answers0