mysql query order by listings that have been paid for

Question

i'm having a bit of trouble trying to write an sql query. i have a table for listings, now all i am trying to do here is make it so that listings that have been paid for will show up at the top of the results.

so say i am on province page and the province ID is 23, i need to make the listings that have upgrade_province='yes' and upgrade_status='paid' and id_province='23' show up as the first results ordered by date, and everything else below those ordered by date. if upgrade_province='yes' and upgrade_status='unpaid' then it needs to show that listing as a normal listing.

heres the table structure:

`id` int(11) NOT NULL auto_increment,
`id_user` int(11) NOT NULL,
`contact_email` varchar(255) NOT NULL,
`price` varchar(255) NOT NULL,
`title` varchar(255) NOT NULL,
`title_slug` varchar(255) NOT NULL,
`description` text NOT NULL,
`id_province` int(11) NOT NULL,
`id_city` int(11) NOT NULL,
`map_address` varchar(255) NOT NULL,
`id_type` int(11) NOT NULL,
`id_contract` int(11) NOT NULL,
`bedrooms` varchar(255) NOT NULL,
`bathrooms` varchar(255) NOT NULL,
`image_main` varchar(255) NOT NULL,
`image_2` varchar(255) NOT NULL,
`image_3` varchar(255) NOT NULL,
`image_4` varchar(255) NOT NULL,
`image_5` varchar(255) NOT NULL,
`status` varchar(255) NOT NULL default 'Active',
`upgrade_urgent` varchar(255) NOT NULL default 'no',
`upgrade_city` varchar(255) NOT NULL default 'no',
`upgrade_province` varchar(255) NOT NULL default 'no',
`upgrade_price` varchar(255) NOT NULL,
`upgrade_status` varchar(255) NOT NULL default 'unpaid',
`num_views` int(11) NOT NULL default '0',
`num_replies` int(11) NOT NULL default '0',
`date_posted` int(11) NOT NULL,
`date_upgrade` int(11) NOT NULL default '0',
`mod_status` varchar(255) NOT NULL default 'approved',

i can't seem to figure out how i can make it treat unpaid ads as normal even if their upgrade_province='yes'

Answer 1

You can use arbitrary expressions in ORDER BY clauses. The boolean true/false values will get treated as '0' and '1' integers which can be ordered with ASC/DESC as usual, so

...rest of query...
ORDER BY (upgrade_province='yes' and upgrade_status='paid' and id_province='23') DESC,
   ... other order clauses ...

Any records which evaluate to TRUE in that 'and' sequence will evaluate to a '1', which when ordered DESC will appear first in the results. Anything which comes out false is cast to 0 and will sort lower.