Clever Geek Handbook

PHP: number of repetitions in a table by most

I am trying to replicate the most common duplicates in a table. I was able to repeat the name of the duplicates, but what I am looking for is a single column in the table that shows how many times the duplicate is repeated. Is there a way to reflect the COUNT (*) value? A way to organize the values ​​by size would also be helpful.

My php:

$interests = "SELECT name, COUNT(*) FROM pageinterests WHERE pageid = '$id' GROUP BY name HAVING COUNT(*) > 1";
$interestresults = mysqli_query($conn, $interests);

My html:

      <div class="panel panel-default panel-table">
<div class="panel-heading">
<div class="row">
<h2 class="cba-title">Interests</h2>

                    </div>
                    </div>
                    <div>
                            <?php
                                if(mysqli_num_rows($interestresults) > 0){
                                    $rim = mysqli_num_rows($interestresults);
                                    echo "
                                     <table class = 'table table-striped table-bordered table-list'>
                                     <thead>
                                      <tr>
                                       <th>Name</th>

                                      </tr>
                                       </thead>
                                      <tbody>'";

                                    for($x = 1; $x <= $rim; $x++){
                                        $rat = mysqli_fetch_assoc($interestresults);
                                        echo "<tr>

                                            <td>".$rat['name']."</td>
                                            </tr>";

                                    }

                                    echo '</tbody>
                                        </table>
                                      </center>';
                                }else{
                                    echo " There doesn't seem to be any common interests yet";
                                }
                            ?>
                        </div>
                        </div>
+3


source to share


4 answers


Use alias and order for correct counting sequence



    $interests = "SELECT name, COUNT(*) AS my_count 
          FROM pageinterests 
          WHERE pageid = '$id' 
          GROUP BY name HAVING COUNT(*) > 1
          ORDER BY my_count";


    .......

   for($x = 1; $x <= $rim; $x++){
       $rat = mysqli_fetch_assoc($interestresults);
       echo "<tr>

        <td>".$rat['name']."</td>
        <td>".$rat['my_count']."</td>
          </tr>";

    }
+4


source


You can use an alias like this:



SELECT name, COUNT() as The_Count 
FROM pageinterests
WHERE pageid = '$id'
GROUP BY name HAVING COUNT() > 1
ORDER BY The_Count DESC;
+4


source 


SELECT name, COUNT(*) as mycount FROM ...
+3


source


you need to use an alias for count (*). Then you can use it for order and echo.

$interests = "SELECT name, COUNT(*) as count_alias FROM pageinterests WHERE pageid = '$id' GROUP BY name HAVING COUNT(*) > 1 ORDER BY count_alias DESC";
+3


source






More articles:


All Articles