Hi

I have a view that is using the location proximity search. The postcode/zipcode field is available to the site user for searching, but some people have been adding too much text. The user gets a database error message and the log entry is as follows:

Data too long for column 'zip' at row 1: INSERT INTO {zipcodes} (latitude, longitude, country, zip) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3); Array ( [:db_insert_placeholder_0] => 51.7645582 [:db_insert_placeholder_1] => -0.4379009 [:db_insert_placeholder_2] => gb [:db_insert_placeholder_3] => hemel hemsted london ) in location_latlon_rough_default() (line 323 of \sites\all\modules\location\location.inc).

Open to suggestions on a work around? I am not sure if this is a bug, or an EBKAC

Thank you
ice70

CommentFileSizeAuthor
#1 location-1411246-shorten_zip_field.patch1.85 KBkrlucas

Comments

krlucas’s picture

krlucas commented
StatusFileSize
new location-1411246-shorten_zip_field.patch1.85 KB

This may not be the preferred approach but here's a patch which shortens zip to its length as defined by the schema api.

krlucas’s picture

krlucas commented
Status: Active » Needs review
ankur’s picture

ankur commented
Status: Needs review » Closed (fixed)

Instead of loading the schema information, I just modified the code in the views handlers to impose a maxlength of 16 on the postal code input textfield.

ice70’s picture

ice70 commented

Hi,

sorry for the delay in responding, I had not noticed the updates to this thread

thank you for the fix, prevents the error.

I had taken an alternative approach and increased the zipcodes.zip to varchar(60)
My reasoning behind this is that the view Location: Distance / Proximity search form had only the postcode form element and users were getting successful results from entering things like 'Sudbury hill middlesex' and 'Nottinghill gate, London'. The postcode of the area they wished to search was not always known. Limiting the postcode search field to just 16 characters will reduce the effectiveness of the views proximity search?

I am not sure if this is the correct use of the postcode search though so I may have not set this up correctly?

Thank you
ice70

Louis Bob’s picture

Louis Bob commented

Hi Ice70, I agree with your approach, so I would like to do the same.
How did you practically do it? From the database itself (using PHPMyAdmin for example), or modifying a drupal file in the location directory?