PDA

View Full Version : This is probably a strange question - I am looking for list of UK counties


nanny
07-26-2007, 07:47 AM
Hi I am wanting to have in my site countries: US, Candada, Australia, New Zealand and UK.
I am wanting to also have the states/provinces/counties for these.
I have all except when I search for UK I get a different result each time.
This is an example I am placing into my state table for New Zealand.

INSERT INTO inv_state (inv_state) VALUES ('New Zealand','26');
INSERT INTO inv_state (inv_state) VALUES ('NZ - Auckland','27');
INSERT INTO inv_state (inv_state) VALUES ('NZ - Christchurch','28');
INSERT INTO inv_state (inv_state) VALUES ('NZ - North','29');
INSERT INTO inv_state (inv_state) VALUES ('NZ - South','30');
INSERT INTO inv_state (inv_state) VALUES ('NZ - Other','31');

I am trying to cover everything but not too elaborate. Otherwise it gets too large.
Yes some inserts have a city.

Thanks...

davidj
07-26-2007, 07:58 AM
you should have 2 tables.

//table 1 - countries

+------+--------+
| ID |country |
+------+--------+


//table 2 - cities

+------+--------+
| ID |cities |
+------+--------+


your countries would be entered like this

10 | uk
11 | US
12 | Aus
13 | Germany
14 | India

then your cities would be related like this

10 | newcastle
10 | Gateshead
10 | Devon
10 | Portsmouth
10 | London

so when you select a country (in this example the uk) you would pass the id of 10 for the next query to return the city.


select * from city where id = $countryID

nanny
07-26-2007, 08:54 AM
Thanks I will giv it a go.

davidj
07-26-2007, 09:39 AM
http://www.dreamweaverclub.com/vtm/php-dynamic-dropdowns.php

Mark_W
07-26-2007, 01:23 PM
This page shows two lists of UK counties, one grouped into England, Scotland, Wales and N.Ireland and one listed the counties alphabetically.

Just copy the code into dreamweaver and search the <option> and </option> and delete them.

nanny
07-27-2007, 05:29 AM
Hi Mark
I didn't get the list, would you mind giving it again.
Thanks...

nanny
07-27-2007, 05:37 AM
Hi Davidj
Did the tutorial but isn't quite what I want. I am not using the id as an integer, this is an example of what I have done with the table. Maybe it won't work.

INSERT INTO country (inv_country, numbers) VALUES ('Australia', '1');
INSERT INTO country (inv_country, numbers) VALUES ('New Zealand', '2');
INSERT INTO country (inv_country, numbers) VALUES ('United Kingdom', '3');
INSERT INTO country (inv_country, numbers) VALUES ('United States - Canada', '4');


and then this:


INSERT INTO inv_state (inv_state, numbers) VALUES ('NZ - Auckland', '2');
INSERT INTO inv_state (inv_state, numbers) VALUES ('NZ - Christchurch', '2');
INSERT INTO inv_state (inv_state, numbers) VALUES ('NZ - North', '2');
INSERT INTO inv_state (inv_state, numbers) VALUES ('NZ - South', '2');
INSERT INTO inv_state (inv_state, numbers) VALUES ('NZ - Other', '2');


I used number as a foreign key and it is integer.
The reason I have used the name of things for some tables is I hav a lot of joins and the query will be much faster.

I did this to get the numbers in groups and alphabetical:

SELECT *
FROM inv_state RIGHT JOIN country ON inv_state.numbers = country.numbers


For the country table I did this to get the first selection box:

SELECT *
FROM country
WHERE country.inv_country = '$country'


My first selection box shows because of this:


<select name="inv_country" onChange="document.forms[0].submit()">
<option value="1" <?php if($country == 1) echo " selected"; ?>>Australia</option>
<option value="2" <?php if($country == 2) echo " selected"; ?>>New Zealand</option>
<option value="3" <?php if($country == 1) echo " selected"; ?>>United Kingdom</option>
<option value="4" <?php if($country == 2) echo " selected"; ?>>United States - Canada</option>
</select>


Any suggestions I think I am getting confused....
My next dropdown still shows all e.g. Australia, NZ etc. instead of corresponding to the number of the country.

davidj
07-27-2007, 06:56 AM
where you have ...


<select name="inv_country" onChange="document.forms[0].submit()">
<option value="1" <?php if($country == 1) echo " selected"; ?>>Australia</option>
<option value="2" <?php if($country == 2) echo " selected"; ?>>New Zealand</option>
<option value="3" <?php if($country == 1) echo " selected"; ?>>United Kingdom</option>
<option value="4" <?php if($country == 2) echo " selected"; ?>>United States - Canada</option>
</select>

check you $country equality check. should it not be 1,2,3,4
you have it set to 1,2,1,2

nanny
07-27-2007, 07:03 AM
Hi thanks I noticed that and changed it but it still won't work like yours.
Do you think I have to redo the tables......eeek.......

CREATE TABLE country
(
inv_country VARCHAR(30),
PRIMARY KEY (inv_country),
numbers INT(3)
);

CREATE TABLE inv_state
(
inv_state VARCHAR(80) NULL,
PRIMARY KEY (inv_state),
numbers INT(3)
);



I really wanted to keep the primary key as is but if I have to change I will...

davidj
07-27-2007, 07:16 AM
need to see your second dropdown

nanny
07-27-2007, 07:24 AM
Hi

<select name="inv_state">
<option value="">Select State</option>
<?php
do {
?>
<option value="<?php echo $row_invstate['inv_state']?>"><?php echo $row_invstate['inv_state']?></option>
<?php
} while ($row_invstate = mysql_fetch_assoc($invstate));
$rows = mysql_num_rows($invstate);
if($rows > 0) {
mysql_data_seek($invstate, 0);
$row_invstate = mysql_fetch_assoc($invstate);
}
?>

</select>


The same as I did for tutorial:

<select name="model">
<option value="">Select Model</option>
<?php
do {
?>
<option value="<?php echo $row_rscar['car_model_id']?>"><?php echo $row_rscar['car_model']?></option>
<?php
} while ($row_rscar = mysql_fetch_assoc($rscar));
$rows = mysql_num_rows($rscar);
if($rows > 0) {
mysql_data_seek($rscar, 0);
$row_rscar = mysql_fetch_assoc($rscar);
}
?>

</select>


Cheers...

davidj
07-27-2007, 07:52 AM
you need to follow the tut

i know this way works and its easier to put a square peg in a square hole than it is to take the peg to wood working shop an make it square by glueing square bits on to it

nanny
07-27-2007, 07:56 AM
Then I have the problem of my innerjoins for multiple tables for my search and results....... I will give it a try.....

davidj
07-27-2007, 08:25 AM
it will be your joins that will be incorrect causing the results to return incorrectly

nanny
07-27-2007, 09:44 AM
OK I changed it to having integer for the value key. I noticed too that I had to get rid of the primary key for the second table.

Now I need to to know how to show results for this:

SELECT *
FROM listingf
WHERE listingf.status = 'active' AND (listingf.classbusinessname LIKE '%SearchString%' OR listingf.inv_addr1 LIKE '%SearchString%' OR listingf.classcatid LIKE '%SearchString%' OR listingf.categorytype LIKE '%SearchString%' OR listingf.inv_state LIKE '%SearchString%' OR listingf.inv_addr2 LIKE '%SearchString%' OR listingf.tel LIKE '%SearchString%')
ORDER BY listingf.adscreation_date DESC


Where listingf.inv_state is now an integer instead of showing the name.
So on the results it will show a number - how do I change this.
It worked wonderfully before....
Thanks for your help.

davidj
07-27-2007, 10:10 AM
the name should still be in the table

the int is a reference which you use to id the record the name should be in the row or in a table with a relative reference

nanny
07-27-2007, 10:18 AM
The table refering to listingf is using the inv_state as a foreign key and as a dynamic menu/list.
So to query the listingf you only get the integer of the inv_state. So I need to know how to show the inv_state.invname
Do I need to do this?

SELECT *
FROM listingf, inv_state
WHERE listingf.status = 'active' AND (listingf.classbusinessname LIKE '%SearchString%' OR listingf.inv_addr1 LIKE '%SearchString%' OR listingf.classcatid LIKE '%SearchString%' OR listingf.categorytype LIKE '%SearchString%' OR listingf.inv_state LIKE '%SearchString%' OR inv_state.invname LIKE '%SearchString%' OR listingf.inv_addr2 LIKE '%SearchString%' OR listingf.tel LIKE '%SearchString%')
ORDER BY listingf.adscreation_date DESC

davidj
07-27-2007, 11:23 AM
im sorry about this. when your talking naming conventions i cant visualise the problem.

all i know is this....

if i had 2 tables...

table1...

+-----------+--------+-----------+
| id1_prime | name | address |
+-----------+--------+-----------+


table2...

+-----------+--------+-----------+----------+
| id2 prime | id1 | orders | dates |
+-----------+--------+-----------+----------+

i can access any field by using id1 prime and id1 in a join

select * from table1 left join table2 on id1 = id_prime where table1.name ='$var' and table2.dates='2007-07-01' .......

by joining them i in effect create 1 big query. As long as there is a relationship i can access the data

i cant see your problem as a cant evaluate it through this forum and would have to emulate your project before a can troubleshoot it.

You have taken a different path to what i normally do but i cant see where.

nanny
07-27-2007, 10:46 PM
Yes I would normally do that but as you see the table is more complicated than that - the reason it is the way to define the table has been set to active first before even showing in results.

I have put the country/state into my larger form but it won't work... can't see a reason why it works on its own but not within a large form.
Would I need to change the onchange="document.forms[0].submit()"
or maybe it doesn't work with my error checking with php?

back to the drawing board....

davidj
07-27-2007, 10:50 PM
you havnt got a form field with a duplicate name?

nanny
07-27-2007, 10:55 PM
No but I noticed it won't show the second drop down until the form is submitted and if you change the same happens again.
I liked the way it auto submitted before but somehow with a submit button things have changed.

I will have to go now my son is playing a game of football(soccer) and it is 8am in the morning here in Australia.
Thanks will be back later.

davidj
07-27-2007, 10:58 PM
is this to do with the logic around the second dropdown

nanny
07-27-2007, 10:59 PM
Jus before I go I noticed on re-submit the state doesn't stay sticky.
So for a user it is a very unfriendly way of filling out.
Thanks....bye

Mark_W
07-28-2007, 10:32 PM
Sorry about that nanny, must have forgot to paste the web page, and I couldn't reply yesterday or earlier today as I've been working at Ascot (Horse Racing).

There you go. http://www.ilovejackdaniels.com/resources/uk-counties-select-box/

davidj
07-28-2007, 11:02 PM
use strcmp() to make dropdown sticky

nanny
08-05-2007, 09:25 AM
Still no luck
I can't get my php to do error checking with it. Yes the country stays sticky but not the state.
I will give the test form:

$country = $_POST['inv_country'];
mysql_select_db($database_config, $config);
$query_invstate = "SELECT * FROM inv_state INNER JOIN country ON inv_state.inv_country = country.inv_country AND country.inv_country = '$country'";
$invstate = mysql_query($query_invstate, $config) or die(mysql_error());
$row_invstate = mysql_fetch_assoc($invstate);
$totalRows_invstate = mysql_num_rows($invstate);
mysql_select_db($database_config, $config);
$query_RScountry = "SELECT * FROM country";
$RScountry = mysql_query($query_RScountry, $config) or die(mysql_error());
$row_RScountry = mysql_fetch_assoc($RScountry);
$totalRows_RScountry = mysql_num_rows($RScountry);
if (array_key_exists('send', $_POST)) {
$classcatid = trim($_POST['classcatid']);
if (empty($classcatid)) {
$error['classcatid'] = 'Select Industry';
}
$categorytype = trim($_POST['categorytype']);
if (empty($categorytype)) {
$error['categorytype'] = 'Select Category';
}
}


<form id="option" name="option" method="post" action="test.php" >
<table border="0">
<tr>
<td><select name="inv_country" onChange="document.forms[0].submit()">
<option value="" <?php if (!(strcmp("", $_POST['inv_country']))) {echo "SELECTED";} ?>>Select Country</option>
<?php
do {
?>
<option value="<?php echo $row_RScountry['inv_country']?>"<?php if (!(strcmp($row_RScountry['inv_country'], $_POST['inv_country']))) {echo "SELECTED";} ?>><?php echo $row_RScountry['inv_country']?></option>
<?php
} while ($row_RScountry = mysql_fetch_assoc($RScountry));
$rows = mysql_num_rows($RScountry);
if($rows > 0) {
mysql_data_seek($RScountry, 0);
$row_RScountry = mysql_fetch_assoc($RScountry);
}
?>
</select>
</td>
<td><select name="inv_state">
<option value="" <?php if (!(strcmp("", $row_invstate['inv_state']))) {echo "SELECTED";} ?>>Select State</option>
<?php
do {
?>
<option value="<?php echo $row_invstate['inv_state']?>"<?php if (!(strcmp($row_invstate['inv_state'], $row_invstate['inv_state']))) {echo "SELECTED";} ?>><?php echo $row_invstate['inv_state']?></option>
<?php
} while ($row_invstate = mysql_fetch_assoc($invstate));
$rows = mysql_num_rows($invstate);
if($rows > 0) {
mysql_data_seek($invstate, 0);
$row_invstate = mysql_fetch_assoc($invstate);
}
?>
</select>
</td>
</tr>
</table>
<p>
<select name="classcatid" class="select">
<option value="" <?php if (!(strcmp("", $_POST['classcatid']))) {echo "SELECTED";} ?>>Select Industry</option>
<?php
do {
?>
<option value="<?php echo $row_indust['classcatid']?>"<?php if (!(strcmp($row_indust['classcatid'], $_POST['classcatid']))) {echo "SELECTED";} ?>><?php echo $row_indust['classcatid']?></option>
<?php
} while ($row_indust = mysql_fetch_assoc($indust));
$rows = mysql_num_rows($indust);
if($rows > 0) {
mysql_data_seek($indust, 0);
$row_indust = mysql_fetch_assoc($indust);
}
?>
</select>
<?php if (isset($error['classcatid'])) { ?>
<span class="errmsg1"><?php echo $error['classcatid']; ?></span>
<?php } ?> </p>
<p>
<select name="categorytype" id="categorytype">
<option value="" <?php if (!(strcmp("", $_POST['categorytype']))) {echo "SELECTED";} ?>>Select Category</option>
<?php
do {
?>
<option value="<?php echo $row_cattype['categorytype']?>"<?php if (!(strcmp($row_cattype['categorytype'], $_POST['categorytype']))) {echo "SELECTED";} ?>><?php echo $row_cattype['categorytype']?></option>
<?php
} while ($row_cattype = mysql_fetch_assoc($cattype));
$rows = mysql_num_rows($cattype);
if($rows > 0) {
mysql_data_seek($cattype, 0);
$row_cattype = mysql_fetch_assoc($cattype);
}
?>
</select>
<?php if (isset($error['categorytype'])) { ?>
<span class="errmsg1"><?php echo $error['categorytype']; ?></span>
<?php } ?></p>
<p>
<label>
<input name="send" type="submit" id="send" value="Submit" />
</label>
</p>
</form>



Just can't the inv_state drop down to stay sticky.
Any suggestions???
Thank you for your expertise help.

davidj
08-05-2007, 11:19 AM
you have to pass the first dropdown var through to the second dropdown

then catch the first in a GET and the second in the usual way

then just set your strcmp function to use both dropdowns

nanny
08-08-2007, 08:06 AM
But it won't work because the $country = $_POST['inv_country'] on the first drop down and that works within the form all except for the second dropdown staying sticky on resubmit.

If in any sequence for this I use $_GET the second dropdown will not appear.
So I can not see where this $_GET should be.

davidj
08-08-2007, 10:57 AM
ok

like this

the first dropdown uses a $_POST and its this you use in your strcmp()


$dd = $_POST['dropdown'];

if(! (strcmp($dd, 1 ))){echo "selected";} // 1 being the option value to match


so this makes dropdown1 sticky and displays dropdown2 options

so are you saying when you select another option from dropdown2 you loose dropdown1 stickyness

nanny
08-09-2007, 01:21 AM
Thanks davidj
guess what as soon as I removed the search form for keywords on top of the page - it worked.
I don't understand javascript but the onChange="document.forms[0].submit()" must have not liked the previous form. That is why when I did a test page and it worked - was because it was the only form on the page.
Interesting.

Now that I have that sorted I still want to be able to fix the keyword search problem.
But that is talked about elsewhere.

nanny
08-09-2007, 01:22 AM
Thanks Mark it was an awesome list.