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
精彩评论