使用MySQL对url的反馈结果进行排序

Question

I'm having a problem sorting my results with MySQL. I'm building a feedback function that sends good, average or poor into my database. I made a page in order to check those results and sort them by url. It works fine except that it shows me the total number of each feedback when I want it to show me the total for each page distinctively.

For example I got this :

URL 1 : Good 10 | Average 2 | Poor 4

URL 2 : Good 10 | Average 2 | Poor 4

When I want something like this :

URL 1 : Good 6 | Average 1 | Poor 0

URL 2 : Good 4 | Average 1 | Poor 4

Here's my code :

<?php

try {
    $bdd = new PDO('mysql:host=localhost;dbname=lexcelera', 'root', '');
} 
catch(Exception $e) {
    die('Erreur : ' .$e->getMessage());
}

$sorter = $bdd->query('SELECT DISTINCT avis, url FROM feedback');

while ($donnees = $sorter->fetch()) {

?>

<table><tr><td style="width:500px;">

<?php      
    echo $donnees['url'];
?>

</td>
<td>Good <br />

<?php
$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Good\'');

while ($donnees = $reponse->fetch()) {
    echo $donnees[0];
}
?>

</td>
<td>Average <br />

<?php
$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Average\'');

while ($donnees = $reponse->fetch()) {
    echo $donnees[0];
}
?>

</td>
<td>Poor <br />

<?php
$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Poor\'');

while ($donnees = $reponse->fetch()) {
    echo $donnees[0];
}
?>

</td></tr>
</table>

<?php
}
$reponse->closeCursor();

?>

Any idea of I should do it? I looked a lot on forums but couldn't find a proper solution to my problem...

Thanks!

So now I have this :

<?php

try {
    $bdd = new PDO('mysql:host=localhost;dbname=lexcelera', 'root', '');
} 
catch(Exception $e) {
    die('Erreur : ' .$e->getMessage());
}

$sorter = $bdd->query('SELECT DISTINCT url FROM feedback ORDER BY url');

while ($donnees = $sorter->fetch()) {

?>

<table><tr><td style="width:500px;">

<?php      
    echo "<br/>".$donnees['url'];
?>

</td>
<td>Good <br />

<?php
$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Good\' AND url = \'' . $donnees['url'] . '\'');

while ($donnees = $reponse->fetch()) {
    echo "<br/>".$donnees[0];
}
?>

</td>
<td>Average <br />

<?php
$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Average\' AND url = \'' . $donnees['url'] . '\'');

while ($donnees = $reponse->fetch()) {
    echo "<br/>".$donnees[0];
}
?>

</td>
<td>Poor <br />

<?php
$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Poor\' AND url = \'' . $donnees['url'] . '\'');

while ($donnees = $reponse->fetch()) {
    echo "<br/>".$donnees[0];
}
?>

</td></tr>
</table>

<?php

}
$sorter->closeCursor();
?>

And it's almost working but it just returns the good statement (stay on 0 for poor and average).

Also I'm not sure about the closure statement : should I put ^response or $sorter?

Answer 1

Your query will return count of all the results having avis='Good',both of URL1 and URL2

Change this to

$reponse = $bdd->query('SELECT COUNT(*) FROM feedback WHERE avis = \'Good\' GROUP BY url');

Insert a line break

$reponse = $bdd->query('SELECT COUNT(*) as count FROM test WHERE avis = \'Good\' and url=\''.$donnees['url'].'\'');

while ($donnees = $reponse->fetch()) {
    echo "<br/>".$donnees[0];
}

Also Change

$sorter = $bdd->query('SELECT avis, url FROM test group by url');