MySQL Lookup problem

Report · May 23, 2012

I'm developing a form that asks the user to input a zipcode and I'd like to store the associated City and State in a hidden field in the form when the user clicks "Submit". This ought to be straightforward so I must be doing something wrong that is blindingly obvious and I'm way out of my depth. This is partly working but I can't get the MySQL Lookup to work - the page doesn't load when I enable the Lookup code.

I also need to allow for non-US zipcodes that aren't in the lookup table, in which case I'll just copy the zip field contents into the City/State field - I know it could be an invalid US zip code but I can live with that. I don't know how to get the lookup to return the zipcode if it doesn't find a match.

I'd really appreciate some direction on this. Below is what I have so far. Thanks,

I have a lookup table containing two columns, zipcode and cityState, I have a form containing among other things two fields, one called "zip" and the other hidden field called "location" - which should store the City and State obtained from the lookup table.

So, below is the code I have with additional comments:

document.form1.location.value = document.form1.zip.value;
// Now do the lookup - the page fails to load when this code is enabled
document.form1.location.value = "

<?php
// Make a MySQL Connection

$query = "SELECT zipcodes.citystate FROM zipcodes WHERE document.form1.zip.value = zipcodes.zipcode";

// I think I have to change the following line so if the lookup fails it returns the zip code as the result of the lookup

$result = mysql_query($query) or die(mysql_error());

// Print out result
while($row = mysql_fetch_array($result))

     {
       echo $row['citystate'];
     }
?>
"
}
</script>

Report · May 23, 2012

>$query = "SELECT zipcodes.citystate FROM zipcodes WHERE

>document.form1.zip.value = zipcodes.zipcode";

I'm really not well versed in PHP, but it doesn't seem to me that you can directly embed a DOM reference inside a php string. I'm not sure how you can reference it directly - you could copy it to a PHP variable, or simply concatenate the string.

What is the reason for loading the city/state into hidden form fields? What are you planning to do with this?

Report · May 23, 2012

Thanks for your help once again. I'll start digging around in that area and try using a variable.

The application is for an airshow (www.hollisterairshow.com )and we'll be asking visitors what zip code they came from when they buy their tickets and how they heard about the Airshow. Then I'll produce a summary on the web site - which works fine with my test data - showing how many adults/ children came from each city. Some cities have multiple zip codes and I expect the final report will be done using Excel and Pivot tables so it helps them to have City/ State associated with each ticket sale.

The other problem I have is that some visitors may come from Canada/ Mexico and so won't have a US Zip code, the code I have starts by copying the zip code into the City/State field, and what I'd like to do is overwrite the City/State field only if there's a valid US Zip code - in other words do nothing if there's no match for the zip code lookup. The Airshow is this weekend so if I don't get this fixed soon I'll have them separately record on paper non-US zipcodes and we'll manually enter them into the spreadsheet - it should be very few people anyway. Also if I don't get this to work I'll export the ticket data without City/ State to Excel and do the zipcode lookup in Excel - so at least I have a plan B but I'd much rather get this working on the web site !!!

Regards,

Tony

Report · May 24, 2012

>we'll be asking visitors what zip code they came from

So they are just entering zip code, and not city/state, right? You then look up the city/state based on zip code, correct? So then why do you need to populate the city/state in the form fields? If you are looking up city/state based on zip, then you should not be populating city/state in the database again.

To produce the report, you simply need to join the tables. Use an outer join so you can return results where there is no zip code match.