SELECT `al`.*, `u`.username,
MATCH (listing_title) AGAINST ('+Nungarrayi, +Lynette +Corby' IN BOOLEAN MODE) * 10 AS title_relevance,
MATCH (artist_full_name) AGAINST ('+Nungarrayi, +Lynette +Corby' IN BOOLEAN MODE) * 5 AS artist_relevance
FROM `tbl_auction_listing` AS `al`
JOIN `tbl_user` AS `u` ON `al`.`user_id` = `u`.`user_id`
WHERE 1 = 1
AND u.active > 0 AND u.removed < 1
AND `al`.`status` = 1
AND `al`.`start_date` < NOW()
AND `al`.`end_date` > NOW()
AND (
MATCH(`al`.`listing_title`) AGAINST('+Nungarrayi, +Lynette +Corby' IN BOOLEAN MODE) OR
MATCH(`al`.`artist_full_name`) AGAINST ('+Nungarrayi, +Lynette +Corby' IN BOOLEAN MODE) OR
MATCH(`al`.`item_number`) AGAINST ('Nungarrayi, Lynette Corby*' IN BOOLEAN MODE) OR
MATCH(`al`.`description`) AGAINST ('+Nungarrayi, +Lynette +Corby' IN BOOLEAN MODE) OR
MATCH(`u`.`first_name`) AGAINST('Nungarrayi, Lynette Corby' IN BOOLEAN MODE) OR
MATCH(`u`.`last_name`) AGAINST('Nungarrayi, Lynette Corby' IN BOOLEAN MODE) OR
MATCH(`u`.`username`) AGAINST ('+Nungarrayi, +Lynette +Corby*' IN BOOLEAN MODE)
OR `u`.`username` = 'Nungarrayi, +Lynette +Corby'
) ORDER BY title_relevance + artist_relevance DESC
