开发者

MYSQL put users with no profile picture at the bottom, but everyone else still by the other ORDER properties

So what I want to to is put users that don't have a profile picture at the VERY VERY bottom of the page.

but if they do have a picture, then it skips them, and adds them to the following rules

SELECT users.id as id, users.username as username,    
       DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(userprofiles.birthdate)), '%Y')+0 AS age,   
       userprofiles.defaultpictureid as picture, city.City 
  FROM users, 
       userprofiles, 
       Cities as city 
 WHERE users.id = userprofiles.userid 
   AND city.CityId = userprofiles.city 
   AND userprofiles.online = '1' 
   AND userprofiles.co开发者_开发百科untry = ".$this->country." 
   AND userprofiles.state = ".$this->state." 
ORDER BY if (userprofiles.city = ".$this->city.",0,1) AND userprofiles.membertype DESC


Maybe I'm misreading the question since you already have the concept of an IF inside the order by but this should work assuming when defaultpictureid is null when the user doesn't have a picture

....
ORDER BY
    if( userprofiles.defaultpictureid is null,1,0),
    if (userprofiles.city = ".$this->city.",0,1)  
....

Some DBs don't have an IF statement like this. In ones that don't they usually have the case (mysql has both)

ORDER BY
    CASE WHEN userprofiles.defaultpictureid is null THEN 1 ELSE 0 END,

this is also useful when you have more values than just 1 and 0 and you don't want to do nested ifs

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜