Say i want to get my data and order by 3 columns
i currently have:
ORDER BY e.HomePage DESC, e.FeaturedProfile DESC, e.DateModified DESC
this returns HomePage 1st, then FeaturedProfile 2nd these and the rest of the results are ordered by DateModified (some profiles are neither HomePage or FeaturedProfile so these get displayed below and sorted on DateModified only)
What i want it for the 1st results to be a mix of HomePage AND FeaturedProfile ordered by DateModified and then any other results (that are neither HomePage or FeaturedProfile below )
Idealy i want to do this all within 1 query
EDIT:
Below is the full query (inc php code + i have replaced the bound variables for ease of reading): the fields of HomePage and FeaturedProfile are boolean (this is for my main search function so sorry, it is quiet large) Ideally i want all results returned but if HomePage and FeaturedProfile are = 1 i want these first and anything else below. i want HomePage and FeaturedProfile to be mixed together though but still above other results (i can do this with 2 queries, but am hoping to do it in a single query for neatness )
$query = "SELECT DISTINCT e.EscortID,
e.EscortName,
e.EscortEmail,
e.EscortTelephone,
e.EscortWebsite,
e.InCallLocation,
e.HairColour,
g.GenderDescription,
n.NationalityName,
et.EthnicityName,
((date_format(now(),'%Y') - date_format(e.DateOfBirth,'%Y')) - (date_format(now(),'00-%m-%d') < date_format(e.DateOfBirth,'00-%m-%d'))) AS Age,
SUBSTRING_INDEX(e.EscortProfile,' ', 24) AS Description,
p.PhotoAlt,
p.PhotoURL,
e.ShowReviews
FROM tEscort e
INNER JOIN tEscortArea ea ON e.EscortID = ea.tEscort_EscortID
INNER JOIN tEthnicity et ON e.tEthnicity_EthnicityID = et.EthnicityID
INNER JOIN tNationality n ON e.tNationality_NationalityID = n.NationalityID
INNER JOIN tGender g ON e.tGender_GenderID = g.GenderID AND g.Enabled=1
LEFT JOIN tEscortAgency eag ON e.EscortID = eag.tEscort_EscortID
LEFT JOIN tAgency a ON a.AgencyID = eag.tAgency_AgencyID
LEFT JOIN (tEscortPhoto ep INNER JOIN tPhoto p ON (ep.tPhoto_PhotoID = p.PhotoID AND p.Enabled=1))
ON e.EscortID = ep.tEscort_EscortID AND ep.ProfilePhoto = 1
LEFT JOIN (tEscortEnjoyments ee INNER JOIN tEnjoyment en ON (ee.tEnjoyment_EnjoymentID = en.EnjoymentID AND en.Enabled=1))
ON e.EscortID = ee.tEscort_EscortID
LEFT JOIN (tEscortServices es INNER JOIN tService s ON (s.ServiceID = es.tService_ServiceID AND s.Enabled=1))
ON e.EscortID = es.tEscort_EscortID
WHERE e.Enabled=1
AND e.Active=1
AND e.AvctivePayments=1";
if(!empty($areas)){$query .= " AND ea.tArea_AreaID IN($areas)";}
if(!empty($enjoyments)){$query .= " AND ee.tEnjoyment_EnjoymentID IN($enjoyments)";}
if(!empty($ethnicities)){$query .= " AND e.tEthnicity_EthnicityID IN($ethnicities)";}
if(!empty($genders)){$query .= " AND e.tGender_GenderID IN($genders)";}
if(!empty($nationalities)){$query .= " AND e.tNationality_NationalityID IN($nationalities)";}
if(!empty($services)){$query .= " AND es.tService_ServiceID IN($services)";}
if(!empty($hair)){$query .= " AND e.HairColour LIKE '%$hair%'";}
if(!empty($braSize)){$query .= " AND e.tGender_GenderID != 1 AND e.BraSize IS NOT NULL AND e.BraSize REGEXP '$braSize'";}
if(!empty($dressSize)){$query .= " AND e.tGender_GenderID != 1 AND e.DressSize IS NOT NULL AND e.DressSize >= $dressSize";}
if(!empty($escortType))
{
switch ($escortType)
{
case 'Agency' :
$query .= " AND eag.tAgency_AgencyID IS NOT NULL ";
break;
case 'Independent' :
$query .= " AND eag.tAgency_AgencyID IS NULL";
break;
}
}
if(!empty($ageFrom) && !empty($ageTo)){
$i = 0;
$query .= " AND (";
foreach($ageFrom as $from) {
if($i > 0 ){
$query .= " OR ";
}
$query .= " e.DateOfBirth BETWEEN '$ageTo[$i]' AND '$from' ";
$i++;
}
$query .= " ) ";
}
if(!empty($breasts)){
$i = 0;
$query .= " AND (";
foreach($breasts as $cupSize) {
if($i > 0 ){
$query .= " OR ";
}
$query .= " e.BraSize IS NOT NULL AND e.BraSize REGEXP '$cupSize' ";
$i++;
}
$query .= " ) ";
}
if(!empty($incallAreas)){
$i = 0;
$query .= " AND (";
foreach($incallAreas as $incallArea) {
if($i > 0 ){
$query .= " OR ";
}
$incallAreaSub = substr($incallArea, 0, 5);
$query .= " e.InCallLocation IS NOT NULL AND (e.InCallLocation LIKE '%$incallArea%' OR e.InCallLocation LIKE '%$incallAreaSub%') ";
$i++;
}
$query .= " ) ";
}
if(!empty($freeText)){ $query .= " AND (e.EscortName LIKE '%$freeText%' OR a.AgencyName LIKE '%$freeText%')"; }
switch ($orderBy)
{
case 'created' :
$query .= " ORDER BY e.DateCreated DESC";
break;
case 'age' :
$query .= " ORDER BY e.DateOfBirth DESC";
break;
case 'popularity' :
$query .= " ORDER BY e.ViewCount DESC";
break;
default :
$query .= " ORDER BY e.DateModified DESC";
break;
}
$query .= " LIMIT $start, $pageSize";