CODEWITHSUNDEEP
phpmysqliprepared-statement
Raylene
Raylene

Reputation: 296

MySQLi: Selecting 5 most common data in column & returning in order for display

I'm trying to select the 5 most common words from a column called favorite from a table called users. I only need the top 5 but would like them to return in order 1 to 5 for display. These are names so for example:

user1    Bob Marley
user2    Stan Smiley
user3    Bob Marley
user4    Joe Schmoe

etc...

I have the following as a start:

$db_fav_query = mysqli_prepare($con, "SELECT favorite, COUNT(*) AS favCOUNT FROM users GROUP BY favorite ORDER BY favCOUNT DESC LIMIT 5");
$db_fav_query->execute();

$db_fav_result = $db_fav_query->get_result();
$row = $db_fav_result->fetch_assoc();

$fav1 = $row['favorite'];

I'm trying to output as follows:

  <tr>
    <td><?php echo htmlentities($fav1); ?></td>
    <td><?php echo htmlentities($fav2); ?></td>
    <td><?php echo htmlentities($fav3); ?></td>
    <td><?php echo htmlentities($fav4); ?></td>
    <td><?php echo htmlentities($fav5); ?></td>
 </tr>

I realize that $fav2, $fav3 etc.. have not been declared, but am not sure how to fetch these from the dB so they can be assigned as such. I'm not getting any errors, but right now $fav1 returns nothing.

Upvotes: 0

Views: 32

Answers (1)

user1334621
user1334621

Reputation:

You need a while loop to loop through all the row

while($row = $db_fav_result->fetch_assoc()) {
  $fav = $row['favorite'];
  echo $fav;
}

to put them to a table:

<tr>
<?php
while($row = $db_fav_result->fetch_assoc()) {
  $fav = $row['favorite'];
  echo "<td>$fav</td>";
}
?>
</tr>

Upvotes: 2

Related Questions