>Problem: How do I display all the tblCountriesOfOperation for one
>tblContacts record SO THAT MULTIPLE COUNTRIES CAN BE ADDED OR DELETED FOR
>THAT RECORD?
create table contacts(
contact_id int(11) unsigned auto_increment unique not null primary key,
name text
);
create table countries(
country_code char(2) unique not null primary key,
name text
);
create table contact_countries(
contact_id int(11) unsigned,
country_code char(2),
unique key contact_country_key(contact_id, country_code)
);
This last table is a "lookup" table, or cross-join table, or ...
Lots of names for it.
Basic idea is to just have a running list of contacts and countries "matched
up" by their IDs.
Sample Data:
insert into contacts(name) values('Rasmus Lerdorf');
insert into contacts(name) values('Ze\'ev Suraski');
insert into contacts(name) values('Andi Gutman);
insert into countries(country_code, name) values('US', 'United States');
insert into countries(country_code, name) values('CA', 'Canada');
insert into countries(country_code, name) values('IL', 'Israel');
insert into contact_countries(1, 'US'); # I think Rasmus is still in Silicon
Valley somewhere
insert into contact_countries(1, 'CA'); # But he used to work in Canada
insert into contact_countries(2, 'US'); # Zeev and Andi are mostly in
Israel, but
insert into contact_countries(2, 'IL'); # but come to the US for conferences
frequently.
insert into contact_countries(3, 'US');
insert into contact_countries(3, 'IL');
The above assumes that your table was empty, so Rasmus is 1, Ze'ev is 2, and
Andi is 3...
This "middle" table, contact_countries, lets you associate as many, or as
few, people with countries as you need.
The display code could be something like this:
<HTML><BODY><FORM ACTION=<?=$PHP_SELF?> METHOD=POST>
<?php
$contact_id = (int) $_REQUEST['contact_id']; # Don't really care if it's
_GET or _POST
# (int) is a crude but
effective anti-hack measure
if (isset($_POST['contact_countries'])){
# First, wipe out all the "old" contact/country for this person:
$query = "delete from contact_countries where contact_id = $contact_id";
mysql_query($query) or error_log(mysql_error());
# Then put in the "new" settings:
while (list($country_code) = each($contact_countries)){
$country_code = substr($country_code, 0, 2); # Crude but effective
anti-hack
$query = "insert into contact_countries(contact_id, country_code) ";
$query .= " values ($contact_id, $country_code)";
mysql_query($query) or error_log(mysql_error());
}
}
# Display contact name:
$query = "select name from contacts where contact_id = $contact_id";
$contact = mysql_query($query) or error_log(mysql_error());
list($name) = mysql_fetch_row($contact);
echo "Contact: <B>$name</B><BR>\n<BR>\n";
# Now to display current country selections:
# Crucial: ' order by name ' lets me run through $contact_countries and
$countries "in parallel"
$query = "select countries.country_code from contact_countries, countries
";
$query .= " where contact_id = $contact_id ";
$query .= " and countries.country_code = contact_countries.country_code
";
$query .= " order by name ";
$contact_countries = mysql_query($query) or error_log(mysql_error());
$query = "select country_code, name from countries order by name ";
$all_countries = mysql_query($query) or error_log(mysql_error());
$contact_country_row = 0;
while (list($code, $name) = mysql_fetch_row($all_countries)){
if ($code == mysql_result($contact_countries, 0, 0)){
$selected = 'SELECTED';
$contact_country_row++;
}
else{
$selected = '';
}
echo "<INPUT TYPE=CHECKBOX NAME=contact_countries[$code] $selected>
$name<BR>\n";
}
?>
<INPUT TYPE=SUBMIT VALUE='Update Countries'>
</FORM></BODY></HTML>
This code is untested, but should be okay other than typos...
--
Like Music? http://l-i-e.com/artists.htm
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
