PHP ordering trophies

Question

I'm looking to order trophies by their type using PHP.

$trophies = $engine->query("SELECT name, detail, type FROM trophy WHERE group=:group");
$trophies->execute(array(':group'=>$group));

if($trophies->rowCount() > 0) {
    while ($trophy = $trophies->fetch(PDO::FETCH_ASSOC)) {
        echo $trophy['name'] . ': ' . $trophy['type'] . '<br>';
    }
} else {
    echo 'No trophies to be displayed.';
}

Right now, the trophies aren't being ordered.

Trophies can have the following types: platinum, gold, silver, bronze.

I would like to display platinum first, then gold, then silver and then bronze.

How can I do this? Is it possible to modify the SQL query to order the trophies to behave like the desired way?

Please show the database schema and a handfull of test data. What is "group" for? or "type"? Have all these metals the same group? Are there more fields in the table (for example: a "sort_order" ? :)) — Honk der Hase, Mar 21 '19 at 21:47
https://stackoverflow.com/questions/3550942/can-you-add-an-if-statement-in-order-by you need an order by if — imposterSyndrome, Mar 21 '19 at 21:50
https://stackoverflow.com/a/9378709/1512654 you can also use FIELD() — khartnett, Mar 21 '19 at 21:51
How are you expecting to display them in a specific order with no `ORDER BY`? As previously stated, we can't help with your question if we don't know what data you're working with. — EternalHour, Mar 21 '19 at 21:59

score 0 · Answer 1 · answered Mar 21 '19 at 21:54

0

Or if neither of those are useful you can create extra columns in your select :

SELECT ...., IF(type = 'platinum',1,0) as plat, IF(type='gold',2,0) AS gold...etc then use a multiple order by like ORDER BY play, gold .... etc

answered Mar 21 '19 at 21:54

imposterSyndrome

896
1
7
18

score 0 · Answer 2 · answered Mar 21 '19 at 22:05

0

Solved by adding ORDER BY FIELD(type,'platinum','gold','silver','bronze'), ID

Thanks to @khartnett in the comments

answered Mar 21 '19 at 22:05

Appel Flap

261
3
23

PHP ordering trophies

2 Answers2