UK Postcode mysql partial match array please help

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.

shawty · Feb 25, 2009

Ok... so let me just sum this up, just to make sure i have it clear:

Your trying to write a system where you enter a post code and a radius.

Once the PC & R are entered, you then want to search through your Postcode database containing just the out-codes, and retrieve only post codes that lie within the provided radius.

You then want to take this list of retrieved out-codes, and use it to search the jobs data base for any job that is with-in the out-code area, but the postcodes your searching for in my sql my be partial or full.

All ok so far?

Cheers

Shawty

jezza52 · Feb 25, 2009

correct

shawty · Feb 25, 2009

Excellent.

Ok, working backwards...

Lets assume we already have our list of out codes in $outcodes as an array of strings.

The best way of using that to search would be something like the following:


  $link = mysql_connect('host','user','password');
  mysql_select_db('jobs');

  $basesql = "SELECT * FROM joblistings WHERE postcode LIKE ";
  foreach($outcodes as $outcode)
  {
    $query = $basesql . "'".$outcode."%'";
    $results = mysql_query($query) or die(mysql_error());

while($line = mysql_fetch_row($results))
{
    //Process any rows matched here, column data will
    // be in numerical order, so column 1 in the db will be
    // in $line[0], column 2 will be in $line[1] and so on...

    print "<p>".$line[0]." - ".$line[10]."</p>"; // example....
}
  }

  mysql_close($link);

You could also put all your entries into another array or whatever else you want to do with them.

The important point is how we do the SQL search.

First we use the 'LIKE' operator, this is a fuzzy match rather than an actual match as = performs.

Secondly the LIKE operator allows the use of % as a wildcard, this can be seen above by the fact we add it on after the outcode.

Each time round the for-each loop you'll end up with an SQL statement that looks something like


SELECT * FROM joblistings WHERE postcode LIKE 'CT1%'

What this is saying in essence is, select any item where the first 3 letters of the post code are equal to 'CT1', and i don't care what the rest of the code is, or even if there is any.

Did you manage to get your radius search working? That is, can you generate your list of outcodes from the entered PC & Rad, or do you need help with that too?

Cheers

Shawty

jezza52 · Feb 26, 2009

Hello.

Yep the radius thing is working OK when 'out codes' are used on the job listing I have used the following code to correct the 'searching users postcode'

$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);

I have looked and looked and I am unable to find the mysql database query for the lookup of the job listing postcode surely it is the following section that needs changing?

	$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'
    ";

Is the above code to query multiple SQL tables at once? Could you help me alter the above code to match only out codes? Am I missing something?

Many thanks.

shawty · Feb 26, 2009

These are the actual lines that build up the query...


   $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'
    ";

Without actually looking at the whole bigger picture, including the actual database schemas i can't give you a 100% answer, however.. looking at the above... I would re-write it as follows.


  $sql_tables = 'job';
  $sql_query = "SELECT * FROM " . $sql_tables . " WHERE ";
  $sql_query = $sql_query . "job_office_zip LIKE '" . $outcode . "%'";

To answer your multiple tables question, yes the above SQL is querying multiple tables, but since your only looking for jobs, then you only need to query the 'job' table, hence setting the 'sql_tables' variable to 'job'.

As your only searching for zip codes, your realistically only need to match to the 'job_office_zip' column in the database.

Looking at the actual SQL statment you posted above, i'm guessing the query is part of the radius search given the fact it uses the latitude & longtitude entries.

What you need to do is do the whole thing in 2 parts, don't even try to find the jobs in the same SQL as your doing the radius lookups (which is what it appears your trying to do) you'll end up with a huge complicated mess.

Do your radius lookups, and every outcode you find, add it to an array using


  $outcodes[] = $codetoadd;

Where $codetoadd is the code you found, in your mysql_fetch_row loop.

Once you have a list of codes, then use a method similar to my last post, and construct the SQL in the same way as i've done in this post, to get the list of jobs.

Cheers

Shawty

jezza52 · Feb 26, 2009

Thank you very much shawty this all makes much more sense now. Greatful you spent your time helping me out.

shawty · Feb 26, 2009

No worries jezza.

If your done then mark the thread as resolved :-)

Cheers

Shawty

jezza52 · Feb 27, 2009

One more question if you dont mind:

This following script removes the space then the last 3 characters:


$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);

Could you please help me edit this so it:

Checks the postcode is over 4 characters, then checks for spaces - if no spaces are found a space is inserted after the 3rd digit in from the right i.e.

User types CT118NN script creates CT11 8NN or CT64MM script create CT6 4MM.

Thanks.

shawty · Feb 27, 2009

Hi Jazza,

You can check for a space following 4 chars alpha and digits as follows.

if(preg_match("/[A-Z][a-z]{2}\d\d/",$postcode,$matches))
{
// Do stuff here
}

the above regular expression will match exactly

2 chars anywhere in A to Z or a to z followed by 2 digits in the range 0 to 9

So...

AB12 will match

but A12 or AB1

will not.

hope that helps.

Cheers

Shawty

UK Postcode mysql partial match array please help

Ddjjjozsi

Jjezza52

Jjezza52

Jjezza52

Sshawty

Jjezza52

Sshawty

Jjezza52

Sshawty

Jjezza52

Sshawty

Jjezza52

Sshawty

Jjezza52

Sshawty