CODEWITHSUNDEEP
phparrayspdologicconcept
user3297073
user3297073

Reputation: 127

Get highest values from database-table

I have this structure in my database-table:

enter image description here

Now I need most clicked usernames(Top 4), like this output:

  1. name10: 15

  2. name10: 14

  3. name10: 13

  4. name10: 12

I just have this at the moment:

    $result = $dbh->query("SELECT * FROM table");
    $result->execute();
    while ($user = $result->fetch(PDO::FETCH_NUM)) {
        // save data in array´s ? 
    }

Do I have to create multidimensional arrays?

Upvotes: 0

Views: 123

Answers (2)

larsAnders
larsAnders

Reputation: 3813

To get only the highest number, you can use MAX:

$result = $dbh->query("SELECT MAX(clicks) AS clicks FROM table");

To get the top 4 results and order by clicks, you use ORDER BY:

$result = $dbh->query("SELECT * FROM table ORDER BY clicks DESC LIMIT 4");
$result->execute();
$rows = $result->fetchAll(PDO::FETCH_ASSOC);
foreach ($rows as $key => $array) {
    echo $key + 1 . '. ' . $array['username'] . ': ' . $array['clicks'] . '<br/><br/>';

}

Upvotes: 1

Jonathan Spiller
Jonathan Spiller

Reputation: 1895

You can order the table by clicks, in descending order and then limit the select to the top 4.

SELECT id, username, clicks FROM table ORDER BY clicks DESC LIMIT 4

You can save the data into an array in 1 step using:

$users = $result->fetchAll();

Which can return an associative (PDO::FETCH_ASSOC) or numeric (PDO::FETCH_NUM) array (amongst other options).

Upvotes: 0

Related Questions