What is the best approach to retrieve the top 5 authors from a MySQL table using PHP?

To retrieve the top 5 authors from a MySQL table using PHP, you can use a SELECT query with the ORDER BY clause to sort the authors based on a certain criteria (e.g., number of posts or likes). Then, limit the results to 5 using the LIMIT clause. 

<?php
// Connect to MySQL database
$connection = mysqli_connect("localhost", "username", "password", "database");

// Check connection
if ($connection === false) {
    die("ERROR: Could not connect. " . mysqli_connect_error());
}

// Query to retrieve top 5 authors based on number of posts
$query = "SELECT author_id, COUNT(*) as total_posts FROM posts GROUP BY author_id ORDER BY total_posts DESC LIMIT 5";

// Execute query
$result = mysqli_query($connection, $query);

// Fetch and display results
while ($row = mysqli_fetch_assoc($result)) {
    echo "Author ID: " . $row['author_id'] . " - Total Posts: " . $row['total_posts'] . "<br>";
}

// Close connection
mysqli_close($connection);
?>

Keywords

PHP MySQL database query LIMIT ORDER BY

Related Questions