Sort mysql search by function

Question

Sort mysql search by function

Navigation

#1 by (0 votes)

0

I have a considerable problem: - I want to list a search for a function that counts. I want it to be displayed by the value of notifications

Thenotificationcountismadefromthefollowingcode:

functioncount_reports_where_id($conn,$id){//$cmd="SELECT DISTINCT blogger_id FROM reports;";
        $cmd = "SELECT blogger_id FROM reports WHERE blogger_id = '$id'";
        $produtos = mysqli_query($conn,$cmd) or die(mysqli_error($conn));
        $total = mysqli_num_rows($produtos);
        return $total;
    }

My MYSQL structure

Listingcode:

$result=mysqli_query($conn,"SELECT DISTINCT blogger_id from reports ORDER BY ?????/;") or die(mysqli_error($conn));

    echo 
    "<table border='1' padding='111'>
    <tr>
    <th>Blogger ID</th>
    <th>Notificações</th>
    <th>Opções</th>
    </tr>";

    while($row = mysqli_fetch_assoc($result))
    {
    echo "<tr>";
    echo "<td>" . $row['blogger_id'] . "</td>";
    echo "<td>" . count_reports_where_id($conn, $row['blogger_id']) . "</td>";
    echo "<td><a href='admin_report.php?id=". $row['blogger_id'] ."'>Verificar</a></td>";
    echo "</tr>";
    }
    echo "</table>";

How to sort the list by count_reports_where_id? do you need blogger_id to count?

php mysql

asked by anonymous 07.12.2018 / 04:50

1 answer

Convert RTF to String through SQL? Error reading Json on Ionic

score 0 · Accepted Answer

According to my comment, I think you'd better adapt the first query to bring the counted values and then sort by them, thus saving several unnecessary queries and consequently saving processing, making it easier to read and take advantage of mysql features: / p>

$result = mysqli_query($conn,"SELECT count(blogger_id) as quantidade, blogger_id from reports group by blogger_id order by quantidade desc") or die(mysqli_error($conn));

echo 
"<table border='1' padding='111'>
<tr>
<th>Blogger ID</th>
<th>Notificações</th>
<th>Opções</th>
</tr>";

while($row = mysqli_fetch_assoc($result))
{
 echo "<tr>";
 echo "<td>" . $row['blogger_id'] . "</td>";
 echo "<td>" .  $row['quantidade']  . "</td>";
 echo "<td><a href='admin_report.php?id=". $row['blogger_id'] ."'>Verificar</a></td>";
 echo "</tr>";
}
echo "</table>";

You no longer need the count_reports_where_id function

More information:

link    link    link