[SOLVED] My MYSQL query works, but i missed a table from select, how to add please!

[scotchegg78]

Hi Guys.

 

I have a query I do not think is working right. But i missed checking a table in the select!

I have 3 tables...

 

ukpostcode -Simply used to reference postcode lats and longs.

cr_office - used to locate offices in uk

cr_regions - used to allocate offices into regions, but they also act as an office aswel.

 

I get the closet postcode of the offices to the users, and get the region is falls into. however regions are also offices in their own right!

 

The problem is I have wrote my query which i think is right, but sadly it does not include region offices in the search.

 

	$temp3officesql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance,
cr_region.regionEmailAdmin,cr_office.address1,cr_office.address2,cr_office.address3,cr_office.town,cr_office.postcode,cr_region.regionaddress1,cr_region.regionaddress2,cr_region.regionaddress3,cr_region.regiontown,cr_region.regionpostcode  FROM
ukpostcodes,cr_region,
cr_office
WHERE
ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_office.postcode,' ',1)) AND cr_region.regionID = cr_office.regionID ORDER BY distance ASC LIMIT 3";

 

How do I get it to also include the region table details? Do I just add "OR ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_region.regionpostcode,' ',1))" and add the region fields to the select like cr_region.regionaddress1,.. etc  ?

 

or create a temp table combining offices and regions and then work off this?

thanks for any advice

 

[fenway]

That's a bit hard to read, but yes, you should be able to modify your WHERE clause the way you suggest -- did it not work?

[scotchegg78]

Hi thanks for the reply.

 

No it does not work right, I think the issue is I have a region Foreign Key in offices and its in the where as "cr_region.regionID = cr_office.regionID", so to get region details, but as i am also using regions address details this where messes it all up ? with ?

 

 

this is the curretn query...

$temp3officesql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance,
cr_region.regionEmailAdmin,cr_office.address1,cr_office.address2,cr_office.address3,cr_office.town,cr_office.postcode,cr_region.regionaddress1,cr_region.regionaddress2,cr_region.regionaddress3,cr_region.regiontown,cr_region.regionpostcode  FROM
ukpostcodes,cr_region,cr_office
WHERE
(ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_office.postcode,' ',1)) OR ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_region.regionpostcode,' ',1)) ) AND cr_region.regionID = cr_office.regionID ORDER BY distance ASC";

 

I get to many returns and it takes a few seconds to run so somethings up! I should get like 58 office address and 12 region addresses, but i get 112 rows?

 

I think the issue is how i am handling merging the two tables (office and region) and to use them as a dataset of addresses, but also then get the region id from offices table to get region details if required, but of course if the office is a region then i dont need that bit?

 

confused much

 

[scotchegg78]

I think simply I need to figure out how i merge the office and region table first as a dataset, then run my select on this new table?

 

 

[scotchegg78]

I think I am nearly therem honest!

 

I am selecting it from a union, but i can not see whats wrong with this sql to get it working?

 

SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.4780)) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(52.4780))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((1.7410)-ukpostcodes.Long)))) as distance,

cr_region.regionEmailAdmin,a.address1,a.address2,a.address3,a.town,a.postcode FROM

((SELECT c.regionID as regionID,c.regionaddress1 as address1,c.regionaddress2 as address2,c.regionaddress3 as address3,c.regiontown as town,c.regionpostcode as postcode FROM fcasvn.cr_region c) union (SELECT o.regionID as regionID,o.address1 as address1,o.address2 as address2,o.address3 as address3,o.town as town,o.postcode as postcode FROM fcasvn.cr_office o);)as a,ukpostcodes,cr_region WHERE

ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(a.postcode,' ',1)) AND cr_region.regionID = a.regionID ORDER BY distance ASC LIMIT 3";

 

any ideas folks?

[fenway]

I can't read that query... if you format it a bit better, then perhaps someone else (or I) will be able to figure it out.

[scotchegg78]

Sorry I do not know how best to style it?

 

Is this any better?

   SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(52.4780)) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(52.4780))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((1.7410)-ukpostcodes.Long)))) as distance,
   cr_region.regionEmailAdmin,a.address1,a.address2,a.address3,a.town,a.postcode 
   FROM
   ((SELECT c.regionID as regionID,c.regionaddress1 as address1,c.regionaddress2 as address2,c.regionaddress3 as address3,c.regiontown as town,c.regionpostcode as postcode 
   FROM fcasvn.cr_region c)
   union
   (SELECT o.regionID as regionID,o.address1 as address1,o.address2 as address2,o.address3 as address3,o.town as town,o.postcode as postcode
   FROM fcasvn.cr_office o);
   )as a,
   ukpostcodes,cr_region
   WHERE
   ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(a.postcode,' ',1))
   AND
   cr_region.regionID = a.regionID 
   ORDER BY distance ASC LIMIT 3";

If i can not figure out this union join i suspect i may have to just run two seperate querys for offices and regions and then compare them in php to get my top 3.

 

thanks for any input or time

[scotchegg78]

Also if it helps..

 

these are the two seperate qrys i want to combine to one!

 

$temp3officesql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance,
cr_region.regionEmailAdmin,cr_office.address1,cr_office.address2,cr_office.address3,cr_office.town,cr_office.postcode  FROM
ukpostcodes,cr_region,
cr_office
WHERE
ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_office.postcode,' ',1)) AND cr_region.regionID = cr_office.regionID ORDER BY distance ASC LIMIT 3";

$temp3regionsql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance,
cr_region.regionEmailAdmin,cr_region.regionaddress1 as address1,cr_region.regionaddress2 as address2,cr_region.regionaddress3 as address3,cr_region.regiontown as town,cr_region.regionpostcode as postcode FROM
ukpostcodes,cr_region WHERE
ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_region.regionpostcode,' ',1))ORDER BY distance ASC LIMIT 3";

[fenway]

What output do you get from your UNION statement?

[scotchegg78]

I dont, the statement fails and i can not see the error

[fenway]

I dont, the statement fails and i can not see the error

Why can't you see it? error_reporting()? mysql_error()?

[scotchegg78]

Hi

 

I have the qry working now, it was a few silly things missing like an as for the table c and o.

 

I am not sure its doing it right, but i have done it all another way now using 2 qrys and handling it with php

 

If anyone else can benfit from this the way I have done it is ... (full php function included..)

 

function get_closest_postcode(){

$db = new CRDatabase();
$link = $db->connect();	

$query = 'SELECT `Lat`, `Long` FROM `ukpostcodes` WHERE `PostCode`="'.$this->trimmedpostcode[0].'";';

$result = $link->query($query);


$num_results = $result->num_rows;

if($num_results > 0)
{

	$first = $result->fetch_row();

	$temp3officesql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance,
	cr_region.regionEmailAdmin,cr_office.address1,cr_office.address2,cr_office.address3,cr_office.town,cr_office.postcode  FROM
	ukpostcodes,cr_region,
	cr_office
	WHERE
	ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_office.postcode,' ',1)) AND cr_region.regionID = cr_office.regionID ORDER BY distance ASC LIMIT 3";

	$temp3regionsql = "SELECT 60 * 1.1515 * DEGREES(ACOS(SIN(RADIANS(".$first[0].")) * SIN(RADIANS(ukpostcodes.Lat)) +  COS(RADIANS(".$first[0]."))* COS(RADIANS(ukpostcodes.Lat)) * COS(RADIANS((".$first[1].")-ukpostcodes.Long)))) as distance,
	cr_region.regionEmailAdmin,cr_region.regionaddress1 as address1,cr_region.regionaddress2 as address2,cr_region.regionaddress3 as address3,cr_region.regiontown as town,cr_region.regionpostcode as postcode FROM
	ukpostcodes,cr_region WHERE
	ukpostcodes.PostCode LIKE (SUBSTRING_INDEX(cr_region.regionpostcode,' ',1))ORDER BY distance ASC LIMIT 3";

	$result2 = $link->query($temp3officesql)or die("Error : Failed to Get 3 offices");
	$result = $link->query($temp3regionsql)or die("Error : Failed to Get 3 regions");

	$count = 0;
	while($row = $result->fetch_row())
	{
		$count ++;

		$closestoffices[] = array('distance'=>$row[0],'email'=>$row[1],'address1'=>$row[2],'address2'=>$row[3],'address3'=>$row[4],'town'=>$row[5],'postcode'=>$row[6]);
	}
	while($row = $result2->fetch_row())
	{
		$count ++;
		$closestoffices[] = array('distance'=>$row[0],'email'=>$row[1],'address1'=>$row[2],'address2'=>$row[3],'address3'=>$row[4],'town'=>$row[5],'postcode'=>$row[6]);

	}

	foreach ($closestoffices as $key => $row) {
   		$distance[$key]  = $row['distance'];
	}
	array_multisort($distance, SORT_ASC, $closestoffices);		

}

	$this->closestoffices = $closestoffices;
//	exit();

}

 

Thanks for your help fenway.