PHP Search page

[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

[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

[smahaf]

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