SQL DB Zip Codes

[alfaowner]

The table name in my data base is zipData should it be zipdata?

Thanks

[Chuck S]

No zipData is correct.

[alfaowner]

excellent, I was just wondering as I know I asked you about this before, I added the UK postcodes as you said, but I get an error, do I ave to change anything to varchar in the sql?

[Chuck S]

that would all depend on what your trying to do.

It would depend on what your postcodes lengths are

The zipcode field is varchar 5 you might need to change it but it all depends on your specific error.

[alfaowner]

Originally Posted by Chuck S

that would all depend on what your trying to do.

It would depend on what your postcodes lengths are

The zipcode field is varchar 5 you might need to change it but it all depends on your specific error.

I see, well, they are maximum of 4 in length, but some people will have AL1 and others may have AL12 so a maximum of 4 and a minimum of 3 in length. So One assumes that varchar 4 will do the trick then.

By the way Chuck, the error is only occuring when I search from the postcode box on: search.php.
This is the error I receive:

"We're sorry, but the system experienced an unrecoverable error. Please try again later.

Script: showcat

(AL1,AL10,AL2,AL3,AL4,AL5,AL6,AL7,AL8,AL9,BN27,BN6,BN7,BN8,BR1,BR2,BR3,BR4,BR5,BR6,BR7,BR8,CB1,CB10,CB11 etc.... etc....

AND approved=1 ORDER BY zipcode

Result: Unknown column 'AL1' in 'where clause'

Database handle: Resource id #21

[Chuck S]

You would need to post the full error

[alfaowner]

post not required, was screwing layout up!

[Chuck S]

Well I beleive the present search mechanism is for use with numeric zipcodes thus the

zipcode IN ( BLAH BLAH )

That expects numeric results.

Your not going to be able to use the search feature with non numeric zipcodes as it stands

My suggestion would be to remove the distance and radius function from the search template if your going to use non numeric zipcodes.

[alfaowner]

That was already done, it is now varchar 4, with null and a deafault of NULL.

Any thing else it could be?

[Chuck S]

I beleive I posted what your issue is above that I see.

You can not use the new search feature since it is designed for our packaged zipcode feature which expects numeric results.

To make that work with non numeric zipcodes would require code modification.

[alfaowner]

Originally Posted by Chuck S

I beleive I posted what your issue is above that I see.

You can not use the new search feature since it is designed for our packaged zipcode feature which expects numeric results.

To make that work with non numeric zipcodes would require code modification.

where shall I post this then? Are you saying that photopost classifieds is only for use in The united States? Why limit the program. Are you willing to recode the search.php to allow people outside of the states to use this. At this time people need this feature when buying cars

what you reckon?

Thanks

AO

[Chuck S]

Well it is a very complex issue to say the least from an answer point of view as I dont like to leave anyone hanging.

We code our program and support our program as it is written. We packaged our program with a free zipcode database which happens to be in the US where we are based therefore we have never officially tested or attempted to use other zipcode databases with non numeric data. We have made allowances for other users using foreign data changing fields to VARCHAR from INT to allow non-numeric zipcodes as long as it does not provide a security hole in the program. The search distance function which is brand new and was written for the zipcode database we use with the program works flawlessly. We have not tested this function with non numeric data and it was not written for data other than the zipcode database we employ. I can say without fail it will not work with non numeric characters which is why I suggested you remove the search distance part from the search template.

Content visible to verified customers only.

Now I can help once we go into a beta for the product and move forward we can look at getting non-numeric zipcode databases if we can to test expanding this search distance feature. However as it stands now the feature in the program only accepts non numeric data and is how it was written.

[alfaowner]

Hi Chuck, thats a fair enough answer... but I only know this once Ive bought it, Just a suggestion, you should make this aware to potential users before sale, thats my only comment.

Put yourself in our position though, as we think it would be supported, we think you will have this mod available, as it is not, it kinda pee's people off a bit and makes people feel a little angry.

Not everyone that is paying thinks about how things work or expects that response from a supported product, so its only fair to tell before hand. Anyhow, I will not post the person who has given me the fix in case he is bombarded with requests but another fellow UK Photopost customer has fixed it for me, its a very easy fix that you can do and you may want to include this is forum, classifieds modification lounge???

Assuming all else is ok, on line 321 of showcat.php,

Change it to:

$searchcodes .= "'$searchzip',";
(notice apostrophies around $searchzip)

This will fix it, dont forget to change the Product sql table - zipcode from INT to VARCHAR with a value of 4 for UK postcodes using just the SS12 etc...

Good luck it works noww PHEW!!

[Chuck S]

LOL Actually I will test your way but I came up with another way that works also instead of placing it in an IN clause. I was thinking too hard on it instead of trying the easy fix. Just need to see what is the faster query