MySQL order By issue

Hi simple question i guess, but cant figure out how to list the mysql sql the way i want it.

Basiclly in one row I have CityID's I want to be able to pull out the CityID's that == 14 and show them at the top of the return (BUT NOT AS A COUNT)

for e.g Perth == 15 Melbourne == 14 Preston == 14 Sydney == 13

currently they show like this Sydney == 13 Perth == 15 Melbourne == 14 Preston == 14

my code

$sth = mysql_query("SELECT users.id as id, users.username as username, profile.defaultpictureid as picture FROM users, userprofiles as profile WHERE online = '1' AND profile.country = ".$this->country." AND profile.state = ".$this->state." AND profile.city = ".$this->city." ORDER BY if (profile.city = 开发者_StackOverflow中文版12276,0,1)");

The code above seems to be working now.

However also seems to print out the data twice.

[{"id":"7","username":"A","picture":"0"},{"id":"1","username":"B","picture":"0"},{"id":"1","username":"B","picture":"1"},{"id":"7","username":"A","picture":"1"}]

You're selecting from two tables (users and profiles), but have no specified any kind of relationship between when in your where clause, so what you're getting is the Cartesian product of the two, which is why you're getting the duplicated results.

I'm guessing your query should look something more like this:

SELECT users.id as id, users.username as username, profile.defaultpictureid as picture
FROM users, userprofiles as profile
WHERE
    online = 1 AND
    profile.country = {$this->country} AND
    profile.state = {$this->state} AND
    profile.city = {$this->city} AND 
    users.id = userprofiles.userid     <---the join condition for the two tables
ORDER BY if (CityID = 14, 1, 0), profile.city

You can apply an if clause in the sorting

order by if(CityID = 14,0,1)