Search code examples
phpmysqlsqldatabasepostal-code

First part of postcode

I want user to search by postcode, they could enter full postcode (eg: UB100PE / UB10 0PE) or first part of postcode (UB10)

I will use getUKPostcodeFirstPart("UB100PE") to get first part of postcode

Ref: getUKPostcodeFirstPart() by LazyOne

I want to display a list of record from first part of postcode, is this how it should be done?

Should I add stripped_postcode field in the table?

Something like::

SELECT records.company, records.full_postcode, area.* FROM records
  LEFT JOIN area on area.stripped_postcode = records.stripped_postcode AND records.id= area.record_id
WHERE records.stripped_postcode = "UB10"

It is linked with two tables, records and area

record table:

  • id (PK)

  • company

  • postcode

  • stripped_postcode

area table:

  • id (PK)
  • record_id (FK)
  • stripped_postcode
  • field1
  • field2
Solution

  • I'm a bit confused: seems like a lot of work for avoiding the use of a like in SQL.

    SELECT records.company, records.full_postcode, area.[insert your fields here]
FROM records
 LEFT JOIN area 
   ON area.stripped_postcode = records.stripped_postcode 
  AND records.id= area.record_id
WHERE records.postcode like strSearch+'%'