UK Postcode mysql partial match array please help

jezza52 · Feb 21, 2009

Hello.

I have been trying to accomplish this for since 10:00 this morning and just cannot work it out. I have 3000 UK postcodes in a mysql database containing the first 3/4 digits of a UK postcode i.e. CT1, CT2, CT3 (this also contains longitude and latitude, but ignore that for now as it calculates distance using the first 3/4 digits of the post code OK already).

Users use their full post code i.e. CT1 1AD when logging items and other users search using full post codes for users items but the issue is, the database only contains the first 3/4 digits (I have tested logging items with just 3/4 digits and then searching for the items with the first 3/4 digits and the script works and calculates the distance in miles).

I was wondering if anyone could help me alter the script to it only needs to match the first 3/4 digits in a full post code to return a result, currently is will only match if any user uses the first 3/4 digits such as CT1. The code I am using which I think needs to be altered is:

// radius ...

$i = 0;
$sql_query = "SELECT * FROM setup_radius ORDER BY radius_order ASC, radius_name ASC";
$result = mysql_query($sql_query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

  $radius_id               = $row[radius_id];
  $radius_name            = $row[radius_name];   
  $arr_radius_id[$i]         = $radius_id;
  $arr_radius_name[$i]      = $radius_name;
  $arr_radius_status[$i]      = "no";

  if ($radius_id == $sradius) { $arr_radius_status[$i] = "yes"; }
  $i++;

}

Please help me and I can provide more of the script if required, thanks.

jmbrink26 · Feb 21, 2009

Try this:


// radius ...
$i = 0;
$sql_query = "SELECT * FROM setup_radius ORDER BY radius_order ASC, radius_name ASC";
$result = mysql_query($sql_query) or die(mysql_error());
while($row = mysql_fetch_array($result)){

$arr_radius_id[$i] = $radius_id;
$arr_radius_name[$i] = $radius_name;
$arr_radius_status[$i] = "no";
$radius_id = $row[radius_id];
$radius_name = $row[radius_name];

if ($radius_id == $sradius) { $arr_radius_status[$i] = "yes"; }
$i++;

}

Perhaps there is more than what meets the eye, but if I am seeing it right, you are trying to call a variable before it actually has any value.

jezza52 · Feb 22, 2009

Hello.

Thanks for the quick reply, not sure if I made much sense, the script as it is is fully functional with full postcodes but the postcode table in the mysql database only has the area code (the first 3 or 4 digits) such as SE1 or CT5.

The users will list items and search for items based on the full post code, the issue is, the postcode lookup database (which includes longitude and latitude for distance calculation) does not contain the full postcode so when users do a distance seach the script is unable to find the postcode in the database thus does not calculate the distance.

I am looking for a way of when users enter the full post code in the search field the postcode database is queried with the first 3/4 digits of the postcode only and returns a result if a partial match is found, for example:

User list item Apple located at postcode CT1 8NP.
User searches for item 'Apple' and is located at postcode ME2 6NN.
Postcode database contains CT1 and ME2 only and gives a rough distance calculation.

This script works if I was to list 'Apple' at postcode CT1 and search for 'Apple' from postcode ME2 (works out at about 39.6 miles) - but users need to be able to use the full postcode when listing and searching.

Have I made more sense?

Thanks again.

jmbrink26 · Feb 22, 2009

That definitely makes more sense now.

What I would personally do for such a task would be use AJAX or just straight JavaScript to truncate the value entered before PHP gets the form value.

So lets say you had a simple one field form called address, and the text box called zip.

<form name="address" id="address" method="post" action="<?php $_SERVER['PHP_SELF']; ?>">
     <table>
          <tr>
             <td>Zipcode</td>
             <td><input type="text" name="zip" id="zip" /></td>
          </tr>
     </table>
</form>

So with that in mind, we'll want JS to handle the input before the action is met in the form (sending it to PHP).

var zip = document.address.zip.value
if(zip.length > 3) {
  Math.trunc(zip * 100) / 100;
 }

And then just have it return the value to PHP as typical...

$zip = $_POST['zip'];

Now, the exact process of that I'm sure probably won't completely get you there, but should at least get you in the right direction. What your ultimately looking to accomplish is to have the value truncated to 3 or whatever characters.

I suppose you could use the PHP strlen() function, but it's much easier and quicker at least in my opinion to let JavaScript do it for you before the server starts to process the entered search data.

Hope this helps!

jezza52 · Feb 22, 2009

Thanks, ill give it a try, someone else suggested the following:

// radius ...
$i = 0;
$sql_query = "SELECT radius_id, radius_name
FROM setup_radius
WHERE radius_id LIKE ".$sradius."%'
ORDER BY radius_order ASC, radius_name ASC";
$result = mysql_query($sql_query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$radius_id = $row[radius_id];
$radius_name = $row[radius_name];
$arr_radius_id[$i] = $radius_id;
$arr_radius_name[$i] = $radius_name;
$arr_radius_status[$i] = "no";

if ($radius_id == $sradius) { $arr_radius_status[$i] = "yes"; }
$i++;
}

But it returns the following:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '%' ORDER BY radius_order ASC, radius_name ASC' at line.

Would this code do what I require?

thanks again

jmbrink26 · Feb 22, 2009

The double quotes surrounding ".$sradius." are cancelling out the string of your query, because you opened your query string with double quotes. Try changing so single quotes '.$sradius.'

jezza52 · Feb 23, 2009

Hello, thanks for your help I have tried:

// radius ...	
$i				= 0;
$sql_query = "SELECT radius_id, radius_name 
    FROM setup_radius 
    WHERE radius_id LIKE '.$sradius.'%' 
    ORDER BY radius_order ASC, radius_name ASC";

$result = mysql_query($sql_query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
$radius_id = $row[radius_id];
$radius_name = $row[radius_name];
$arr_radius_id[$i] = $radius_id;
$arr_radius_name[$i] = $radius_name;
$arr_radius_status[$i] = "no";

	if ($radius_id == $sradius) { $arr_radius_status[$i] = "yes"; }
	$i++;

}

And im getting

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''
ORDER BY radius_order ASC, radius_name ASC' at line

Am I doing something wrong?

Thanks

jezza52 · Feb 23, 2009

OK ive worked it out but still doesnt to work correctly, would the above script achieve the following:

The script now displays no error but still only works if I use 3/4 digits if I use the full post code the script will not try and acheive the best possible match from the SQL database for example.

User lists item 'Apple' at post code ME2 5NN
Another user searches for item 'Apple' and enters location as CT12 6MM
Database has both ME2 and CT12 and knows the distance between both areas

If the user was to list 'Apple' at 'ME2' and another user was to search for 'Apple' at CT12 the script returns item 'Apple' and 39.95 miles.

Im not sure if I made much sense initially but I would like a way of the script finding the best possible match in the database based on users entering full post codes and the database only containing the area code (first 3/4 digits).

Im not really any good at programming im just wondering if this type of function: WHERE radius_id LIKE '.$sradius.'%' if applicable for this task?

Please help!

Thanks.

djjjozsi · Feb 23, 2009

cut the first 3 digits:

$loc_code=substr($_POST["postcode"],0,3);

The syntax should be:

$sradius=mysql_real_escape_string(str_replace(" ","",sradius));
$sql="SELECT     ..........FROM ........   WHERE radius_id LIKE '{$sradius}%'";

jezza52 · Feb 23, 2009

Hello.

Thing is - some postcodes have 4 digits if you cut it down to 3 digits you make the postcode lookup much more inaccurate.

CT11 CT12 would become CT1 CT1 which is 30 miles out.

thanks

djjjozsi · Feb 23, 2009

<?php
function first( $var )
{
		$part = explode( " ", $var );  /lets explode the entered values using the space as delimiter  

		$len = strlen( $part[0] );  //count the first part

	switch ( $len )
	{
			case 0;
					die( "Empty Postcode" );
					break;

			case 3;  // 3 charater 
					return( strtoupper( $part[0] ) );
					break;

			case 4;    // 4 character long
					return( strtoupper( $part[0] ) );
					break;

			default:
					die( "$var is not a UK postcode lenght" );
	} 
} 


$postcoooode2 = "ME2 5NN";
$postcoooode = "CT12 XXX 6MM";

echo first( $postcoooode ) ."<br />";
echo first( $postcoooode2 ) . "<br />";

?>

jezza52 · Feb 23, 2009

Hello.

Sorry im not very good at PHP stuff I think I can see that this will check to see if there is a space and if no space is found and will display incorrect format. The thing is if I was to search for a 3 or 4 digit postcode no results are returned as the listing data contains the full postcode.

3 items contains postcode data
'listed item' - contains full post code (CT11 0AA, ME5 6YN, SE1 9PP etc...)
'Postcode database' contains short postcode CT11, ME5, SE1 including coordinates for distance calculation)
'Searching user' - contains searching users full post code (CT11 0AA, ME5 6YN, SE1 9PP etc...)

the 'listed item' post code will always be different to the 'searching user'.

This script currently works perfectly if the users use the area code for both listing and searching. Is there a way to search for a best match? So it would compare for the closest match maybe with removing characters 5/6/7? if these would cause the best possible match to be different for the script to work out?

Whats the best way of doing this im stuck and I cant work out how I can get your script to match, help!!

Thanks alot.

djjjozsi · Feb 24, 2009

Is there a way to search for a best match? So it would compare for the closest match maybe with removing characters 5/6/7?

That code makes what you wanted to do in the thread:

If the user gives a postcode, that makes a chunk from it , this cut the string with a space character, and if it is 3 or 3 character long will return. Thats all.

Make a dropdown list for the oportunities next to the Postcode user input, if this selected item is "searching user", or postcode database, then search in another table / field.

So it would compare for the closest match maybe with removing characters 5/6/7?

Yes, you can do lots of things in php. But its a good idea to start from the 7th character using the LIKE % ,
if its 4-5-6 then use the LIKE %% , if its 3-4 then use the correct match. You see in the first() function it check the str-lenght using the SWICTH case. Its very talketive condition. In several cases you do something.

As you can see, the substr function get a range from your string.
i'v made a little tutorial how to make a postcode search, somebody asked to me. Did you see that?
Hy, jjozsi.

jezza52 · Feb 24, 2009

Hello.

I would love to see your tutorial. I tried to implement your post code digit check script but unfortunately when I go to site_search_advanced.php the script reports 'empty postcode' without loading the script for me to type the postcode. I can see the script works when I use it by itself, I think I have not given you enough code to go by also I think the listing postcode will cause an issue to as it uses full postcodes.

If I manage to get your script implemented and it keeps only digits 3/4 of the searching user post code do I need to implement it to keep digits 3/4 on the listing postcode? The main database only has 3/4 digits so that can remain.

Shall I send you the scripts might make it a bit easier as you have a tiny bit of a huge script.

Thanks again.
Jez

djjjozsi · Feb 24, 2009

I would love to see your tutorial.

i'll send you the url.

The usage of this function should be , in that case if its needed to crop the 3 or 4 characters only.

$postcode=first($_POST["postcode1"]);

Shall I send you the scripts might make it a bit easier as you have a tiny bit of a huge script.

Sorry, but i had to do lots of other things.

But you can share with us that piece of code, not the whole script of course.

Hello, jjozsi.

jezza52 · Feb 24, 2009

Hello.

No worries I think where im getting confused is the site has site_search_advanced.html (template) and site_search_advanced.php

If I enter your script in search_search_advanced.php it says empty postcode because the var is not set, is that right?

The template file contains, amongst other search features:

<td><strong>Zip / Postal Code</strong> <font class="normal_12_red"></font></td>
<td><strong>:</strong></td>
<td>

<input type="text" name="szip" value="{$szip}" class="textbox" style="width:100px" />

</td>
</tr>
{if $zip_radius_enable == "yes"}
<tr>
<td><strong>Radius</strong> <font class="normal_12_red"></font></td>
<td><strong>:</strong></td>
<td>

<select name="sradius" class="textbox" style="width:105px">
<option value="">Any radius</option>
{section name=var loop=$radius_id}
<option value="{$radius_id[var]}" {if $radius_status[var] == "yes"} selected {/if} >{$radius_name[var]}</option>
{/section}
</select>

</td>
</tr>
{/if}

Would this be where I needed to set the variable? This script is for a job seekers website so the user search for a job within a radius of a postcode. The postcode the user enters will be full length (CT11 0TT), the job will contain a full length postcode (ME2 7NN) but the radius database only contains the area code.

How can I implement your script to cut down both the job seekers postcode and the jobs listed postcodes within the radius?

Does the following script contains all 3 references to postcodes, search postcode, job postcode and radius area code?

[code=php]$i				= 0;
$sql_query		= "SELECT * FROM setup_radius ORDER BY radius_order ASC, radius_name ASC";
$result			= mysql_query($sql_query) or die(mysql_error());
while($row		= mysql_fetch_array($result)){

	$radius_id					= $row[radius_id];
	$radius_name				= $row[radius_name];	
	$arr_radius_id[$i]			= $radius_id;
	$arr_radius_name[$i]		= $radius_name;
	$arr_radius_status[$i]		= "no";

	if ($radius_id == $sradius) { $arr_radius_status[$i] = "yes"; }
	$i++;

[/code]

Sorry if I sound a little slow on this and I do really appreciate your help and I cant wait to see this working

jezza52 · Feb 25, 2009

Hello.

Got much further now thanks to your tutorial the code I gave you was to do with radius stuff and was incorrect, applogies, I have now found the correct .php file (which was encrypted) and obtained an unencrypted version.

I have now implemented this:

  // create the sql query for the search ...
	if ($szip && $sradius) {
$postcode = strtoupper(trim($szip));

#Remove any spaces
$postcode = str_replace(" ","",$postcode);

#Trim the last 3 characters off the end
$szip = substr($postcode,0,strlen($postcode)-3);

	// get the latitude and longitude for searched zip code ...
	$sql_query			= "SELECT * FROM setup_zipcode WHERE zip_code = '$szip'";
	$result				= mysql_query($sql_query) or die(mysql_error());
	$row				= mysql_fetch_array($result);
	$zip_found			= mysql_num_rows($result);
	$zip_latitude		= $row[zip_latitude];
	$zip_longitude		= $row[zip_longitude];
	#Convert the post code to upper case and trim the variable

I can now use full postcodes such as SE1 7NN on the advanced search if the job listing has an area postcode such as CT1 - definately going in the right direction. Can you please help me alter the script that allows me to use full postcodes on the listed items which I think is the following section, I have tried using the same script as in your tutorial but it cant find the jobs in the mysql database:

	// $row		= decode_array($row);
		$job_id		= $row[job_id];
		$job_zip	= $row[job_office_zip];




    // get the latitude and longitude ...
	$sql_query	= "SELECT * FROM setup_zipcode WHERE zip_code = '$job_zip'";
	$tresult	= mysql_query($sql_query) or die(mysql_error());
	$trow		= mysql_fetch_array($tresult);
		$show_distance = "yes";

	$job_lat	= $trow[zip_latitude];
	$job_lon	= $trow[zip_longitude];
	$job_dist	= calculate_mileage($job_lat, $zip_latitude, $job_lon, $zip_longitude);

	// convert the distance between mile and km ...
	if ($zip_radius_unit == "mile"	) { $job_dist = $job_dist;				}
	if ($zip_radius_unit == "km"	) { $job_dist = $job_dist * 1.609344;	}

Thanks alot.

jezza52 · Feb 25, 2009

I think this section might have something to do with it too:

	$sql_tables	= "job , employer , setup_zipcode";
		if ($sacademic) { $sql_tables .= ", setup_academic		"; }
		if ($sfunction) { $sql_tables .= ", setup_jobfunction 	"; }

	$sql_query 	= "
	SELECT * FROM $sql_tables
	WHERE 
	job_employer			=   employer_id		AND
    job_office_zip			=   zip_code		AND 
	employer_status_email	=  'approved'		AND
	employer_status			=  'approved'		AND
	job_date_expire			>  '$date_database'	AND
	job_status				=  'approved'		AND
	zip_latitude 		    <= '$maxLat'		AND 
	zip_latitude 		    >= '$minLat'		AND 
	zip_longitude 		    <= '$maxLong'		AND 
	zip_longitude 		    >= '$minLong'
    ";

}

How do I make this part:

job_office_zip	=   zip_code   AND

Match when job_office_zip = CT1 4BQ and zip_code = CT1 ??

Thanks

shawty · Feb 25, 2009

Hi guys,

I guess i can help here...

All it takes is somone who under stands UK POST codes. :-) (I have the full UK table in MySQL with approx 7,000,000 rows)

First a little theory.

The First Digit Grouping is called the Out Code. This part of the code identifies the UK sorting office responsible for mail to that address, and the district in that area.

The second half is the Incode and defines the exact location in the post area where that address resides.

The Outcode for many years was only 3 chars, until central & greater london exploded with so many new districts that there was not enough digits in the outcode to accomodate the expansions.

This then lead to most of the big citys segregating the post codes up into sub districts, and we now find that most of the big citys now have 4 charachters.

If your not to bothered about doing an actual preg to check the valididty of the first half, then the way i usually split them is as follows:

$postcode = "DH1 2BC";
$temp = explode(" ",$postcode);
$outcode = trim($temp[0]);
$incode = trim($temp[1]);

of course the problem you now have is if the post code has been entered without a space in it, in which case you then need to figure out where the split should be.

The good thing is that the outcode is always formatted as letters then digits, and is never more than 2 letters, and never more than 2 digits and the incode is always formatted as 1 number and 2 letters.

This means that in theory the following regular expression should always get the in code.

/.*(\d[A-Za-z]{2})/

Now asuming that all you have in the string is your post code, then the following should work with or without the seperating space:

if(preg_match("/(.*)(\d[A-Za-z]{2})/",$postcode,$matches))
{
  $outcode = trim($matches[1]);
  $incode = trim($matches[2]);
}

If you still have problems with this, then let me know and i'll dig through my code library see what i have that helps.

Cheers

Shawty

jezza52 · Feb 25, 2009

Hello Shawty

Thanks for replying.

We have managed to cut the postcodes down to the 'out codes' by removing the spaces then removing the last 3 digits and this is currently working on the websites search form if the listed job is listed with the 'out code'.

I only have the out code database which should be sufficient for this task.

The listed jobs use full post codes and I think the following is causing the issue:

 $sql_tables    = "job , employer , setup_zipcode";
        if ($sacademic) { $sql_tables .= ", setup_academic        "; }
        if ($sfunction) { $sql_tables .= ", setup_jobfunction     "; }

    $sql_query     = "
    SELECT * FROM $sql_tables
    WHERE
    job_employer            =   employer_id        AND
    job_office_zip            =   zip_code        AND
    employer_status_email    =  'approved'        AND
    employer_status            =  'approved'        AND
    job_date_expire            >  '$date_database'    AND
    job_status                =  'approved'        AND
    zip_latitude             <= '$maxLat'        AND
    zip_latitude             >= '$minLat'        AND
    zip_longitude             <= '$maxLong'        AND
    zip_longitude             >= '$minLong'
    ";

}

mainly:

        job_office_zip            =   zip_code        AND

As its directly comparing mySQL databases which differ and I am unsure how to make it compare uisng outcodes only:

job_office_zip = full post code zip_code = outcode database

Do you know of any way that I can strip the job_office_zip down to just the outcode when directly comparing SQL tables?

Thanks.

UK Postcode mysql partial match array please help

Jjezza52

Jjmbrink26

Jjezza52

Jjmbrink26

Jjezza52

Jjmbrink26

Jjezza52

Jjezza52

Ddjjjozsi

Jjezza52

Ddjjjozsi

Jjezza52

Ddjjjozsi

Jjezza52

Ddjjjozsi

Jjezza52

Jjezza52

Jjezza52

Sshawty

Jjezza52