combining 2 tables and ordering from missing fields?

[scotchegg78]

HI Guys

 

I am not sure exactly how I need to wish to go about this, but put simply I have a companies service/operational area which they pre select on registering. IT can be down to a town or county level.

 

So I have a company table their id.

A reference towns table with (id, countyid,town)

A reference county table. (id, county)

A compaines in town table company_sa_town (id,companyid,townid)

A companies in county table company_sa_county (id,companyid,countyid)

 

Now when someone does a search for lets say Birmingham, I need the search to return first compnies that have said they are in birmingham only first, then underneath the companies saying they cover the west midlands.

 

So the first thing I do so far is grab the POST['searchlocation'] Variable and run a query against it in the countys to see if it gets a hit matching name. If so great I know they are searching by county, if not its a town name.

If its a town name i can search the towns table to find the town ID and the parent countyid (then use this to get the county name) and i have everything i need...

 

 

$sql = "select id from tc_counties where county = '".$_GET["frmsearchtown"]."'";
			$db_query = mysqli_query($this->db_link, $sql);
				if (mysqli_num_rows($db_query)==0) {

					$searchlocation ="town";
					//check towns
					echo "Town";  

					$sql = "select id,county_id from tc_towns where town LIKE '".$_GET["frmsearchtown"]."'";
					$db_query = mysqli_query($this->db_link, $sql);
					$row = mysqli_fetch_assoc ($db_query);						

					$countyid = $row['county_id'];
					$sql = "select county from tc_counties where id ='".$countyid."'";
					$db_querycounty = mysqli_query($this->db_link, $sql);
					$rowcounty = mysqli_fetch_assoc($db_querycounty);	
					$countyname = $rowcounty['county'];

 

So thats the easy bit, but now I need to get all companies that are in towns service area or are in county service area.

 

the where been something like..

 

((c.id in (select companyid from company_sa_town where townid=".$row['id'].")) OR
					(c.id in (select companyid from company_sa_county where countyid=".$row['county_id'].")))

 

but how can I return them in order so the towns come first over those in companies?

the full sql at the moment is ...

 

select distinct ci.companyfk, c.address_line_1, c.address_line_2, c.address_line_3, c.postcode, c.town_city, c.county, c.shortdescription, ci.id as cid, c.id, c.registrationtype, c.emailaddress, c.productsandservices, c.companyname, c.url, c.telephonenumber, ci.ext as cext from company as c inner join registration_type as rt on c.registrationtype = rt.id left join company_images as ci on ci.companyfk = c.id where (c.approved = 1) and (c.live=1) and (del=0) and ((c.id in (select companyid from company_sa_town where townid=38676)) OR (c.id in (select companyid from company_sa_county where countyid=94))) and ((ci.islogo = 1) or (ci.islogo is null)) group by ci.companyfk, c.id order by rt.searchpreference desc, c.companyname asc , id desc limit 20;

 

where 38676townid  is Birmingham and county 94 is west midlands by the way.

 

 

Thanks for any tips

[Barand]

One way would be a UNION

 

select 1 as townOrCounty, yourdata .... WHERE in town

UNION

select 2 as townOrCounty, yourdata .... WHERE in county

 

Now you can include townOrCounty in the sort fields