Postal, city and Province

br

brononius 15 Feb, 2016

Hey,

I've got a database table with following data:
PostalCode - City - Province - Country
1000 - Brussels - Brabant - Belgium
2000 - Antwerp - Antwerp - Belgium
9000 - Ghent - Oost-Vlaanderen - Belgium
9890 - Asper - Oost-Vlaanderen - Belgium
9890 - Baaigem - Oost-Vlaanderen - Belgium

I would like to create a contact form where people just need to typ (select?) the postal code, and the rest should be autofill.
So if I typ in 9000, this should be the result:
Postal: 9000
City: Ghent
Province: Oost-Vlaanderen
Country: Belgium

What's the best way to do this?

I was playing around with the db-read, and I've got a nicely dropdown list of all my postals.
And with the example of the demo-dynamic-dropdown, I'm able to match 1000 with Brussels, manually.
But i'm a bit stick to search the cities in the database...

Gr

GreyHead 15 Feb, 2016

Hi brononius,

I'm not sure what you are trying to do here? If you have the post code, do you need the other data completed? It looks to me as if the postcode is a unique identifier.

If you do need to complete them you could use JavaScript to parse (split up) the option text and set the values of the other boxes.

Bob

br

brononius 15 Feb, 2016

If somebody types (or select?) '9000' in the field postal, the other dropdown boxes (city, province...) should show the information of the tables with the necessary info.

Gr

GreyHead 15 Feb, 2016

Hi brononius,

Then I think it has to be a Custom Coded Ajax autocompleter.

Bob

br

brononius 15 Feb, 2016

ouch, sounds a bit complicated. :$
I'll have a look if I can figure it out...

br

brononius 16 Feb, 2016

I managed to build something with following code (based on the demo of the dynamic drop down):

<?php
$result = mysql_query(" SELECT postcode,gemeente FROM `oni_geo_gemeente` WHERE `postcode` LIKE '" . $form->data['postcode'] ."' ");

while ($row = mysql_fetch_array($result)) 
{
    $gemeente[$row['postcode']]['postcode'] = $row['gemeente'];
}

echo json_encode($gemeente[$form->data["postcode"]]);
?>

I repeat the same for a province, country...

Just have an issue now with the fact that some cities have the same postal code. So I should fine a solution (workaround) for this. :$

br

brononius 16 Feb, 2016

I've create 2 different DB-reads, one for the postal code, and one for the cities. This is the same table, but 2 DB-reads.
And now it seems that when I select a postal, I can select different cities (if different cities are under the same postal)?
Maybe a bit to much 'load' on the db, but it works...

Next step, showing proper value of the province/countries.
Since in the contactdb, I want to store a number (fe 3), and this number correspondence then with another table (fe countryID=3, country=Belgium)
Today, he reads the value out of the citydatabase (cityid=2, city=brussels, country=3...). And puts 3 nicely in the contactdatabase. But I would like that the dropdown button on the form shows 'Belgium'.
For the moment, he shows 3...

ps takes a while, but the result will be just perfect.😉

br

brononius 18 Feb, 2016

1 Likes

And yes, it works!!!
This is my final code for the 'event custom code':

<?php
$result = mysql_query(" SELECT * FROM `oni_geo_gemeente` WHERE `postcode` LIKE '" . $form->data['postcode'] ."' ");

while ($row = mysql_fetch_array($result)) 
   {
      $resultaat = mysql_query("SELECT `provincie` FROM `oni_geo_provincie` WHERE `provid` LIKE '" . $row['provincie'] . "' ");
      while ($provincie_leesbaar = mysql_fetch_array($resultaat)) {

       $provincie[$row['postcode']][$row['provincie']] = $provincie_leesbaar['provincie'];
   }
}
echo json_encode($provincie[$form->data["postcode"]]);
?>

I've repeated this several times. And when you now give a postal OR city, the other fields are filled in automaticlly. :p
Nice, nice nice! I love Chronoform!!!

Forums

Postal, city and Province