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?
Rate this post
5 of 5 based on 9923 votesComments