John Davidson

Message:

I have 3 tables, users, warnings, warningnames.

The content of the tables is as follows:

Users(simplified):

u_id, username, password, rank

Warnings:

w_id, wn_id, u_reporter_id, u_reported_id

Warningnames:

wn_id, warningInfo, warningPoints

I have a loop in PHP which gets me all users with a simple query:

SELECT *

FROM users 

WHERE u_id < :startcount 

ORDER BY u_id DESC 

LIMIT :perpage 

How do I make a query which gets all users but puts the users with the most warnings at the top and the users with rank 0 at the bottom?

The query I have so far is:

SELECT users.*, COUNT(warnings.w_id) as warningCount

FROM users 

LEFT JOIN warnings 

ON users.u_id = u_reported_id 

WHERE u_id < :startcount 

ORDER BY warningCount DESC

LIMIT :perpage

But that doesn't return what I want, it just returns the warningCount of all users in a single query that fit the WHERE statement.

Example fiddle:

http://sqlfiddle.com/#!9/ec6c2d/6

Sample PHP code:

<?php

$query = $this->handler->prepare('SELECT * FROM users WHERE u_id < :startcount ORDER BY u_id DESC LIMIT :perpage');

try{

    $query->execute([

        ':startcount'   => 25,

        ':perpage'      => 25

    ]);

}

catch(PDOException $e){

    echo $e->getMessage();

}



while($fetch = $query->fetch(PDO::FETCH_ASSOC)){

    echo $fetch['username'] . '<br />';

}

?>

How can I correct this and order by a warningCount column but put all users with rank 0 at the end?

---

My Answer:
IN (SELECT u_reported_id FROM warnings)_ORDER BY rank DESC;__This query orders the users by their rank in descending order. However, I want to add a condition where if a user has a certain warningPoints value in the warningnames table, I want to put them at the end of the result set. Is this possible in MySQL?