Sort mysql search by function

Asked

Viewed 61 times

0

I have a considerable problem: - I want to list a search for a function that does a count. I want it to be displayed at the value of notifications inserir a descrição da imagem aqui

The counting of notifications is done from the following code:

    function count_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 inserir a descrição da imagem aqui

Code that lists:

        $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? function that requires blogger_id to count?

php mysql

  • It got pretty messy, I hope someone understands

  • $result = mysqli_query($Conn,"SELECT DISTINCT blogger_id from Reports order by $count_reports_where_id(bloggerid) ;") or die(mysqli_error($Conn)); ?

  • Can you try to explain it another way? How this query would be ordered?

  • The return of this function is num_rows, so it would be ordering by an integer value, I can’t understand the logic.

  • You would have to sort the output of the function where it is called. But I think it would be better to add Count to your sql. It would save a query and improve reading. I will elaborate a response

1 answer

1


According to my comment, I believe that it would be better for you to adapt the first query to bring the values counted 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:

$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>";

No longer need count_reports_where_id

More information:

https://dev.mysql.com/doc/refman/8.0/en/order-by-optimization.html https://www.w3schools.com/sql/sql_groupby.asp https://www.w3schools.com/sql/func_mysql_count.asp

  • Thank you very much!

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.