postcode search

[ldoozer]

Hi all, I am trying to extend my search criteria on my site. the site uses php and a mysql database.
I have a field called postcode which contains postcodes such as:
S1 2RB
S6 3RK
S11 7AB

I have tried to query using the LIKE function ie:
query = SELECT * FROM Activities WHERE ActDesc LIKE '%tennis%' AND PostCode LIKE 'S1%';

However this returns both "S1 2RB" and "S11 7AB"

is it possible within the query to differeciate between the two?

Any help would be great.

Many thank
Steve

[Barand]

... LIKE 'S1 %'

[ldoozer]

Thanks v much for your feedback Barand

Only thing is post code is not validated on the way in so there might not be a space between first and second part of the postcode so there might be S12RD for example.

[Barand]

try
[code]
SELECT postcode FROM Activities
WHERE LEFT(postcode, LENGTH(postcode)-3) IN ('S1', 'S1 ')
[/code]

[ldoozer]

[quote author=Barand link=topic=115430.msg470014#msg470014 date=1163848549]
try
[code]
SELECT postcode FROM Activities
WHERE LEFT(postcode, LENGTH(postcode)-3) IN ('S1', 'S1 ')
[/code]
[/quote]

:-[ forgive me but is this saying first 3 chars of postcode should match "S1" or "S1 "?

cos if thats the case would'nt that still return "S11" aswell.

[Barand]

S12RD
S1 2RD
S112RD

No, it's saying what's left when you remove the last 3 characters should match 'S1' or 'S1 '

[ldoozer]

Thanks again Barand - great help.

[ldoozer]

Sorry to bring this up again but got a new problem with the script. It works great until people have only entered a general postcode area such as just "S1".

current script:
[code]SELECT * FROM publicActivities WHERE ActDesc LIKE '%$mysport%' AND LEFT(PostCode, LENGTH(PostCode ) - 3 )IN('$myArea')[/code]

[swatisonee]

make sure they dont. That is, when they enter postcode they need to a put in a minimum 6 characters . Something like :

[code]if ( strlen($postcode) < 6){
$msg=$msg."Postcode must be more than 6 char length. Please go back and re-enter the p.c";} [/code]

I dont know what you would do for the existing 2 character data tho as the above will work when you modify your insert script for new records.

Swati

[ldoozer]

Thanks for the reply, have just put in validation for the new imputs, but i really need to account for people wanting to put in a general postal area.

Is there a way to search the first few chars of the postcode but still differenciate between S1 and S11 for example this would solve the problem I think.

[swatisonee]

but then barands code should work right because it diff. b/w s1 and s11 ? Is there anyway you can have users update their entries so you get the whole pc ?

[ldoozer]

Yes berands code checks against the first few chars as i understand it, but I think it relies on the fact that there is a full postcode to start with.

[swatisonee]

at thw top of my head, the only thing i can think of is to update the existing entries if they are not too many.

[Barand]

try

SELECT postcode FROM Activities
WHERE LENGTH(postcode)> 3 AND LEFT(postcode, LENGTH(postcode)-3) IN ('S1', 'S1 ')
UNION
SELECT postcode FROM Activities
WHERE LENGTH(postcode)<= 3 AND postcode = 'S1'

[ldoozer]

thanks  again berand for the advise - I can see now what the statement is saying from your last explanation but struggling with the syntax in php:
this is what iv got :
[code]$query = "SELECT * FROM publicActivities WHERE ActDesc LIKE '%$mysport%' AND LEFT(PostCode, LENGTH(PostCode ) - 3 )IN('$myArea') AND ActHide='1' LIMIT  $offset, $rowsPerPage";[/code]

which is working great as I said. How would i add the extra code you mentioned- UNION...

[Barand]

[code]$query = "SELECT * FROM publicActivities
        WHERE LENGTH(postcode)> 3
                AND LEFT(postcode, LENGTH(postcode)-3) IN ('$myArea')
                AND ActDesc LIKE '%$mysport%' 
        UNION
        SELECT * FROM publicActivities
        WHERE LENGTH(postcode)<= 3
            AND postcode = 'S1'
            AND ActDesc LIKE '%$mysport%' ";[/code]

[ldoozer]

I am getting this error:

You have an error in your SQL syntax near 'UNION SELECT * FROM publicActivities WHERE LENGTH(PostCode)<= 3 AND PostCode = '' at line 1

Heres the query im running.

[code]$query = "SELECT * FROM publicActivities WHERE LENGTH(PostCode)> 3 AND LEFT(PostCode, LENGTH(PostCode)-3) IN ('$myArea') AND ActDesc LIKE '%$mysport%' UNION SELECT * FROM publicActivities WHERE LENGTH(PostCode)<= 3 AND PostCode = '$myArea' AND ActDesc LIKE '%$mysport%' AND ActHide='1' LIMIT  $offset, $rowsPerPage";[/code]

[Barand]

I cannot see anything obvious, unless it's something in your $myarea and $mysport variables.

From a test table containing

postcode
----------
S1 2DB
S1 3RD
S118AA
S14DS
S1

this query
[code]
SELECT postcode FROM clients
        WHERE LENGTH(postcode)> 3
                AND LEFT(postcode, LENGTH(postcode)-3) IN ('S1', 'S1 ')
        UNION
        SELECT postcode FROM clients
        WHERE LENGTH(postcode)<= 3
            AND postcode = 'S1'
[/code]

gave -->

results
----------
S1 2DB
S1 3RD
S14DS
S1

[ldoozer]

im sure i will sort it - thanks for all your help through out this topic berand - much apreciated :)