Member Avatar for smahaf

Hi I have the following PHP search results page I am trying to configure the results page so that regardless of what is searched or what search fields have or do not have data the results are allways ordered by lastname.

If I use Order by lastname in the initial query and just search with all fields empty it works but if I enter part of the firstname or lastname it does not order the results. I have tried putting the order by statement after the firstname and lastname clauses in the query but then the query fails ... I guess it detects multiple order by statements on the same field

It seems it will work for some but not all search scenarios???

  //build the query with the requested info (if avail) 
  $search_query = "SELECT clientid, additionalownerid, additionalpropertyid, officeref, title, firstname, lastname, email1, email2, tel, mobile, identification1, identification2  from clients WHERE clientid = clientid order by lastname"; 

  if($clientid != ''){    $search_query .= " and clients.clientid = '$clientid' ";  }
  if($officeref != ''){    $search_query .= " and clients.officeref like '%$officeref%' ";  }  
  if($title != ''){    $search_query .= " and clients.title like '%$title%' ";  } 
  if($firstname != '' ){    $search_query .= " and clients.firstname  like '%$firstname%'  oder by lastname";  }
  //if($firstname != '' and $lastname !=''){    $search_query .= " and clients.firstname  like '%$firstname%' ";  } 

 // if($firstname != ''){    $search_query .= " or clients.additionalfirstname like '%$firstname%'";  } 

  if ($startswith != 'contains' and $lastname != '' )   $search_query .= " and clients.lastname  like '$lastname%' order by lastname "; 
//  if ($startswith != 'contains' and $lastname != '' )   $search_query .= " or clients.additionallastname like '$lastname%' "; 
  if ($startswith != 'startswith' and $lastname != '' )    $search_query .= " and clients.lastname like '%$lastname%' order by lastname "; 
  if($addr1 != ''){    $search_query .= " and clients.addr1  like '%$addr1%' ";  } 
  if($addr2 != ''){    $search_query .= " and clients.addr2  like '%$addr2%' ";  } 
  if($town != ''){    $search_query .= " and clients.town  like '%$town%' ";  } 
  if($city != ''){    $search_query .= " and clients.city  like '%$city%' ";  } 
  if($postcode != ''){    $search_query .= " and clients.postcode  like '%$postcode%' ";  } 
  if($country != ''){    $search_query .= " and clients.country  like '%$country%' ";  } 
  if($tel != ''){    $search_query .= " and clients.tel  like '%$tel%' ";  } 
  if($mobile != ''){    $search_query .= " and clients.mobile  like '%$mobile%' ";  } 
  if($email1 != ''){    $search_query .= " and clients.email1  like '%$email1%'  or clients.email2  like '%$email1%' ";  } 
  if($identification1 != ''){    $search_query .= " and clients.identification1  like '%$identification1%' ";  } 
  if($identification2 != ''){    $search_query .= " and clients.identification2  like '%$identification2%' ";  } 
  if($powerofatorney != 'Choose'){    $search_query .= " and clients.powerofatorney  = '$powerofatorney' ";  }
  if($doesclienthaveawill != 'Choose'){    $search_query .= " and clients.doesclienthaveawill  = '$doesclienthaveawill' ";  }
  if($resident != 'Choose'){    $search_query .= " and clients.resident  = '$resident' order by lastname";  }


  //run the query 
  $search = mysql_query($search_query, $db) or die (mysql_error());

Thanks in advance

Steven

Edited by Reverend Jim because: Fixed formatting
  • 2 Contributors
  • 2 Replies
  • 100 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by smahaf
Member Avatar for almostbob 
$search_query = "SELECT clientid, additionalownerid, additionalpropertyid, officeref, title, firstname, lastname, email1, email2, tel, mobile, identification1, identification2 from clients WHERE clientid = clientid "; 
if($clientid != ''){ $search_query .= " and clients.clientid = '$clientid' "; }
if($officeref != ''){ $search_query .= " and clients.officeref like '%$officeref%' "; } 
if($title != ''){ $search_query .= " and clients.title like '%$title%' "; } 
if($firstname != '' ){ $search_query .= " and clients.firstname like '%$firstname%' "; }
/* 
etcetera 
*/
$search_query .= " order by lastname";

the conditional where clauses (if statemenst) were not in WHERE , they were part of ORDER BY by the order they were processed

Member Avatar for smahaf

So simple but Works GREAT!!!! Thanks for your quick reply I was pulling my hair out!!!!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.